Help I Cant Fix It!

[cocoonfx]

Hello I am pretty new to Access and i have grasped the basics of SQL and making complicated queries. However i have a query which looks at the main table of data. I have a rule which certain fields must have data which must be within 2 days of todays date, and i want to find out in some other fields which don't have dates.

I am using the OR part of the query so my coding would look like

WHERE (((QryMasterReport.[Planned Date)])<=Now()-"2") AND ((QryMasterReport.[Delivery Failed]) Is Null) AND ((QryMasterReport.Delivered Castleford]) Is Null)) OR (((QryMasterReport.[Planned Date 2])<=Now()-"2") AND ((QryMasterReport.[Rebooked Planned Delivery Date 2]) Is Null) AND ((QryMasterReport.[Delivered 2]) Is Null));

Now there are about 20 Fields i have applied this to and some of the fields where IS NULL it still show dates?

HELP?

 

[FoFa]

WHERE (QryMasterReport.[Planned Date)]<=DATEADD("d",-2,Now()) AND
QryMasterReport.[Delivery Failed] Is Null AND
QryMasterReport.Delivered Castleford] Is Null)
OR (QryMasterReport.[Planned Date 2]<=DATEADD("d",-2,Now()) AND
QryMasterReport.[Rebooked Planned Delivery Date 2] Is Null AND
QryMasterReport.[Delivered 2] Is Null);
remove all the uneeded parens first, and rearrange the code to make sense, then paste it back into the SQL portion (don't worry, access will add bacl those parens).
try it again
But your code does not give data within two days of todays date. If I understand then this is better. Of course using NOW() instead of DATE() brings the time portion into play (may or maynot matter).
WHERE (QryMasterReport.[Planned Date)] between DATEADD("d",-2,Now()) AND now() AND
QryMasterReport.[Delivery Failed] Is Null AND
QryMasterReport.Delivered Castleford] Is Null)
OR (QryMasterReport.[Planned Date 2] between DATEADD("d",-2,Now()) AND now() AND
QryMasterReport.[Rebooked Planned Delivery Date 2] Is Null AND
QryMasterReport.[Delivered 2] Is Null);

 

[cocoonfx]

Thank You

Yep it works thank you!

 

[cocoonfx]

More Help???

Thank you for your help! and it worked but now we have to find out orders which our 90 days or older. I have adapted the code from -2 to -90 but it shows.

i.e

>=now()-"90" this shows dates a couple of days ago but we only want to see orders now 90 days or older?

Have i done this part of the code wrong?

 

[neileg]

As Pat says, you should be using Date(). Also, you should use DateAdd() to do the date maths, so you can work explicitly with days, or hours.