SQL Statement Not Working (Repost of Criteria Not working but in SQL view)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following is a where clause in my query. I am trying to pull up all records where the clock date is null, or where revision recieved is false and todays date is 55 or more days past the date in NOD Issue

WHERE ((([RMP Status].[RMP clock Date]) Is Null) AND (([RMP Status]![RMP Number])=[Latest RMP, Audit,SP,UI]![MaxOfRMP Number])) OR ((([RMP Status].[Revision Recieved])=False) AND (([RMP Status]![RMP Number])=[Latest RMP, Audit,SP,UI]![MaxOfRMP Number]) AND ((Date())>=[RMP Status]![NOD Issued]+55))
ORDER BY [SS Information].[Lead Reviewer]

however when my results come up I am getting information where while the date is 55 or more days after NOD Issued the response recieved values are true. DId I make a mistake somewhere
Thank Yo
Nicole
 
Nicole said:
The following is a where clause in my query. I am trying to pull up all records where the clock date is null, or where revision recieved is false and todays date is 55 or more days past the date in NOD Issued


WHERE ((([RMP Status].[RMP clock Date]) Is Null) AND (([RMP Status]![RMP Number])=[Latest RMP, Audit,SP,UI]![MaxOfRMP Number])) OR ((([RMP Status].[Revision Recieved])=False) AND (([RMP Status]![RMP Number])=[Latest RMP, Audit,SP,UI]![MaxOfRMP Number]) AND ((Date())>=[RMP Status]![NOD Issued]+55))
ORDER BY [SS Information].[Lead Reviewer];

however when my results come up I am getting information where while the date is 55 or more days after NOD Issued the response recieved values are true. DId I make a mistake somewhere?
Thank You
Nicole
Click to expand...


Your first line is very different than the Where clause.

The where clause for what you wrote would look like:

(Case 1)

Where

[RMP Status].[RMP clock Date]) Is Null OR

([RMP Status].[Revision Recieved])=False AND
[RMP Status]![NOD Issued] <= DateAdd("d",-55,Date()))


The Where clause from the SQL broken out looks like:

(Case 2)

WHERE
[RMP Status].[RMP clock Date] Is Null AND

([RMP Status]![RMP Number]=[Latest RMP, Audit,SP,UI]![MaxOfRMP Number] OR [RMP
Status].[Revision Recieved]=False) AND

[RMP Status]![RMP Number]=[Latest RMP, Audit,SP,UI]![MaxOfRMP Number] AND

[RMP Status]![NOD Issued] <= DateAdd("d",-55,Date())


Note the where the ANDs , the OR & the parentheses are located.


In Case 1, every record where the [RMP clock Date] is null will be returned. In
addition every record where [Revision Recieved] is False AND [NOD Issued] is
more than 55 days ago (both conditions are True) will be returned.


In Case 2, even if [RMP clock Date] Is Null, the record will be returned ONLY if
the other conditions are True.


The two cases will return very different record sets.

Also note a better way to write

Date())>=[RMP Status]![NOD Issued]+55

is placing the field on the left and using the DateAdd function:

[RMP Status]![NOD Issued] <= DateAdd("d",-55,Date())


Hope this makes sense......
 
Back
Top