Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11

    Question Unanswered: Simple criterion not working

    I have several fields in a select query, two of which are "DueDate" and "CalDate". "CalDate" is calculated using an Iif statement involving several other fields in the table:

    CalDate: IIf(IsNull([Fu8]),IIf(IsNull([Fu7]),IIf(IsNull([Fu6]),IIf(IsNull([Fu5]),IIf(IsNull([Fu4]),IIf(IsNull([Fu3]),IIf(IsNull([Fu2]),IIf(IsNull([Fu1]),[Initial],[Fu1]),[Fu2]),[Fu3]),[Fu4]),[Fu5]),[Fu6]),[Fu7]),[Fu8])

    and "DueDate" is calculated using an Iif statement involving the another field and "CalDate":

    DueDate: IIf([Fatal]=True,DateAdd("d",3,[CalDate]),DateAdd("d",7,[CalDate]))

    In the criteria section of the query under the "DueDate" field, I have entered:

    Between Date() And DateAdd("d",5,Date())

    to return records that have a due date between today and 5 days from today.

    Problem: When I run the query I get no records returned, and I specifically set up two records that would meet the criterion. Can anyone see a problem in my syntax? When I run the query without the criterion, all the records are returned, with the correct DueDate, so I know the DueDate calculation is working. The criterion is such a simple statement, and I can't figure out what's wrong. Please help?

  2. #2
    Join Date
    May 2003
    Posts
    144
    Can you send me the query along with the table that contains the samples?

Posting Permissions

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