Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Posts
    175

    Unanswered: Querying for Expression Data

    I have attached a pic of my query as it stands. I'm looking for a way to filter out only the expression results that return a value of "DUE". As you can see I've used the ="DUE" on different rows indicating an "or" rule. However, it's not returning only values of "due". I want to query only for information that is due.
    Anyone have any thoughts on this?

    thanks in advance.
    Attached Thumbnails Attached Thumbnails query.png  

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Like "*Due*"

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Switch from Design View to SQL View and post the query text please
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2010
    Posts
    175
    I Tried Like "*DUE*", but it just returned a bunch of duplicate data. Here is my SQL View:

    SELECT [TABLE TO PULL FROM].LastName, [TABLE TO PULL FROM].FirstName, [TABLE TO PULL FROM].Address, [TABLE TO PULL FROM].City, [TABLE TO PULL FROM].State, [TABLE TO PULL FROM].Zip, [TABLE TO PULL FROM].[INSPECTION EXPIRATION], IIf([INSPECTION EXPIRATION]<Now()+30,"DUE","Not Due") AS Expr1, [TABLE TO PULL FROM].[REGISTRATION EXPIRATION], IIf([Registration Expiration Date]<Now()+30,"DUE","Not Due") AS Expr2, [TABLE TO PULL FROM].[INSURANCE EXPIRATION], IIf([Insurance Expiration]<Now()+30,"DUE","Not Due") AS Expr3, [TABLE TO PULL FROM].[DRIVER LICENSE EXPIRATION], IIf([DL Expiration Date]<Now()+30,"DUE","Not Due") AS Expr4
    FROM [TABLE TO PULL FROM] INNER JOIN ([PROGRAM TABLE beginning 10-11] INNER JOIN [Transaction Table] ON [PROGRAM TABLE beginning 10-11].[PROGRAM ID] = [Transaction Table].[Program ID]) ON ([TABLE TO PULL FROM].[Individual ID] = [Transaction Table].[Individual ID]) AND ([TABLE TO PULL FROM].[Individual ID] = [Transaction Table].[Individual ID])
    WHERE ((([Transaction Table].[Program ID])=171)) OR (((IIf([INSPECTION EXPIRATION]<Now()+30,"DUE","Not Due")) Like "*DUE*")) OR (((IIf([Registration Expiration Date]<Now()+30,"DUE","Not Due")) Like "*DUE*")) OR (((IIf([Insurance Expiration]<Now()+30,"DUE","Not Due")) Like "*DUE*")) OR (((IIf([DL Expiration Date]<Now()+30,"DUE","Not Due")) Like "*DUE*"))
    ORDER BY [TABLE TO PULL FROM].LastName;

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    I don't really understand what you are saying
    do you want rows where ALL are due
    where any are due

    specifying like "*DUE*" will return rows including DUE and Not Due, you could try = "DUE". however frankly thats just plain dumb repeating the IIF in your where clause

    Code:
    WHERE ((([Transaction Table].[Program ID])=171))
    OR [INSPECTION EXPIRATION] <= Now()+30
    OR [Registration Expiration Date] <=Now()+30
    OR [Insurance Expiration] <= Now()+30
    OR [DL Expiration Date] <= Now()+30
    ORDER BY [TABLE TO PULL FROM].LastName;
    incidentally using spaces in a table and or column name is non standard, other DB's may not understand the [] convention. there is no need to use spaces use either CamelCase or underlining to differentiate words in place of spaces.

    seeing tables called [PROGRAM TABLE beginning 10-11] suggests to me that your data design is not normalised. if nothing else this query will have to be re-edited each time your source data changes, as oppose to embedding the date inside the data AND using that embedded date if required.

    also some db's have problems with very long column names (some truncate after so many characters so I'd strongly recommend that you use or develop your own naiming convention
    eg Inpsection Expiration could become InspExpiry, Registration Expiration Date could become RegExpiry

    if you need long winded descriptions for your labels in reports and forms then consider setting the caption property

    do you get your duplicate data if you run the query without any of the where clause, if so thats down to your query and almost certainly the way your JOIN clause is phrased
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2010
    Posts
    175
    I don't really understand what you're saying. lol. But here's a breakdown:
    I have columns that pull expiration dates into the query. Each record can have more than one thing "due" at a time. I want the query to show only those items that are "due". Please see the attachment.
    Attached Thumbnails Attached Thumbnails screen shot.jpg  

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your attachment doesn't advance thing further. I get that bit, I also suggested a better way of defining the where clauses

    rows are duplicated because of your JOINs. if you don't understand your JOIN's then read up on them.

    looking at your code you want to display rows that have expired or about to expire within 30 days of today, incidentally be aware that NOW() refers to todays date AND a specific date and time date() refers to today only

    without knowing your table design or seeing the relationships its tricky to comment on the join design but it looks hooky to me
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2010
    Posts
    175
    In other words, each row is a different person who I've defined as a "driver" joined by a separate program table. Each person who our agency id's as a driver, must be monitored. When his or her driving information becomes or is about to become expired, I have set up expressions in this query to identify that. I want the query to pick out only those who have one or more "due" items. Does that make any further sense?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •