Date() function not working in query

[riggsdp]

I have several queries that use the Date() and DateNow() functions and they were working a couple of weeks ago but this week, they are not. All other date functions are working, such as DatePart.

I've checked for missing references and none are missing.



I've done searches for this issue and nothing has been successful.

There were some security updates made for Windows on 1/20/15, but none of them seem to be items that would affect using the function.

Any thoughts on what else could be going wrong here?

[orange]

Please post the code/SQL that is NOT working.

[riggsdp]

SELECT tblDOTOtoCDRL.Contract_Number, tblDOTOtoCDRL.Delivery_Task_Order, tblDOTOtoCDRL.CDRL_Number, tblDeliveredItems.Date_Due, tblDeliveredItems.Date_Received, tblDeliveriesReceived.Document_NumberFROM (tblDOTOtoCDRL INNER JOIN tblDeliveriesReceived ON tblDOTOtoCDRL.[Relate_ID] = tblDeliveriesReceived.[Relate_ID]) INNER JOIN tblDeliveredItems ON (tblDOTOtoCDRL.Relate_ID = tblDeliveredItems.Relate_ID) AND (tblDeliveriesReceived.Deliverable_ID = tblDeliveredItems.Relate_ID)
WHERE (((tblDeliveredItems.Date_Due)=Date()) AND ((tblDeliveredItems.Delivery_Is)<>"Resubmittals"));

SELECT tblDOTOtoCDRL.Contract_Number, tblDOTOtoCDRL.Delivery_Task_Order, tblDOTOtoCDRL.CDRL_Number, tblDeliveredItems.Date_Due, tblDeliveredItems.Date_Received, tblDeliveriesReceived.Document_Number
FROM (tblDOTOtoCDRL INNER JOIN tblDeliveriesReceived ON tblDOTOtoCDRL.[Relate_ID] = tblDeliveriesReceived.[Relate_ID]) INNER JOIN tblDeliveredItems ON (tblDeliveriesReceived.Deliverable_ID = tblDeliveredItems.Relate_ID) AND (tblDOTOtoCDRL.Relate_ID = tblDeliveredItems.Relate_ID)
WHERE (((tblDeliveredItems.Date_Due)=Date()+1) AND ((tblDeliveredItems.Delivery_Is)<>"Resubmittals"));

SELECT tblDOTOtoCDRL.Contract_Number, tblDOTOtoCDRL.Delivery_Task_Order, tblDOTOtoCDRL.CDRL_Number, tblDeliveredItems.Date_Due, tblDeliveredItems.Date_Received, tblDeliveriesReceived.Document_Number
FROM (tblDOTOtoCDRL INNER JOIN tblDeliveriesReceived ON tblDOTOtoCDRL.[Relate_ID] = tblDeliveriesReceived.[Relate_ID]) INNER JOIN tblDeliveredItems ON (tblDOTOtoCDRL.Relate_ID = tblDeliveredItems.Relate_ID) AND (tblDeliveriesReceived.Deliverable_ID = tblDeliveredItems.Relate_ID)
WHERE (((tblDeliveredItems.Date_Due)=Date()-1) AND ((tblDeliveredItems.Delivery_Is)<>"Resubmittals"));

[orange]

In first sample I see a syntax error on the FROM. But what is the error with the Date function?
What is the value of tblDeliveredItems.Date_Due?

SELECT tblDOTOtoCDRL.Contract_Number
, tblDOTOtoCDRL.Delivery_Task_Order
, tblDOTOtoCDRL.CDRL_Number
, tblDeliveredItems.Date_Due
, tblDeliveredItems.Date_Received
, tblDeliveriesReceived.Document_NumberFROM 
(tblDOTOtoCDRL INNER JOIN tblDeliveriesReceived ON
 tblDOTOtoCDRL.[Relate_ID] = tblDeliveriesReceived.[Relate_ID]) 
INNER JOIN tblDeliveredItems ON (tblDOTOtoCDRL.Relate_ID = tblDeliveredItems.Relate_ID)
 AND (tblDeliveriesReceived.Deliverable_ID = tblDeliveredItems.Relate_ID)
WHERE (((tblDeliveredItems.Date_Due)=Date()) AND
 ((tblDeliveredItems.Delivery_Is)<>"Resubmittals"));

Were you looking for something like

.... where   tblDeliveredItems.Date_Due BETWEEN  Date()-1 AND Date() +1 ......

[riggsdp]

I think the FROM error is from the copy/paste, it's not like that in the actual query.

The Date_Due can be any day of the year.

The error is that nothing is found as a result of the query even though there are items that should show up. If I run the query to show all items due this month or last week, I get results, but the queries for items due today, tomorrow, and yesterday don't work.

These are all for separate reports so I can't ask for items due between but even if I did, the "BETWEEN Date()-1 AND Date() +1" probably wouldn't work if they won't work in a query by themselves.

This is one that works. It gets all items due next week:

SELECT tblDOTOtoCDRL.Contract_Number, tblDOTOtoCDRL.Delivery_Task_Order, tblDOTOtoCDRL.CDRL_Number, tblDeliveredItems.Date_Due, tblDeliveredItems.Date_Received, tblDeliveredItems.Document_Number, tblDeliveredItems.Document_Revision FROM ((tblDeliverableDetails INNER JOIN tblDOTOtoCDRL ON tblDeliverableDetails.[CDRL_ID] = tblDOTOtoCDRL.[CDRL_Number]) INNER JOIN tblDeliveriesReceived ON tblDOTOtoCDRL.[Relate_ID] = tblDeliveriesReceived.[Relate_ID]) INNER JOIN tblDeliveredItems ON tblDeliveriesReceived.[Deliverable_ID] = tblDeliveredItems.[Relate_ID] 
WHERE (((Year([tblDeliveredItems].[Date_Due])*53+DatePart("ww",[tblDeliveredItems].[Date_Due]))=Year(Date())*53+DatePart("ww",Date())+1) AND ((tblDeliveredItems.Delivery_Is)<>"Resubmission"));

[orange]

You might try looking at a specific record or 2 in your table. Look in the datasheet view and see exactly what value is in
tblDeliveredItems.Date_Due

Then create a query in the query grid looking specifically for that date, and
try again with Date().
It could simply be a formatting issue. For example, if
tblDeliveredItems.Date_Due has a time component and Date() doesn't they probably won't match.

Try a little debugging to be sure what the values are.
Good luck.

[June7]

Where are you? Is international date involved? http://allenbrowne.com/ser-36.html

[riggsdp]

Date_Due is a Date/Time data type, with format set to Short Date. There are no visible times showing in the field. I tried changing the format to General Date to see if any times were displayed and there weren't (not sure if that's a valid test though).

I did try a query searching for a particular date - WHERE (((tblDeliveredItems.Date_Due)=#9/16/2014#) - but it found nothing, even though there four items with that due date.

And, no, international dates are not involved. My regional settings are set to English (US).

On a whim, I changed the selection criteria for Date_Due to Is Not Null and it should have displayed 538 records, instead, it displayed only one record with a date of 12/15/14, There are actually nine records with a Date_Due of 12/15/14 and I could not see any difference in them.

I then went to create a new query in the Query Design and re-created the query with the same result. I'd tried this before but hadn't had any luck then either.

Finally, I went to the Query Wizard and used it to re-create the query minus the Date() and Resubmittal criteria and I got all the items with due dates. I then added the Date() and Resubmittal criteria and got the correct items. Next, I copied the SQL code and compared it to the code generated from re-creating the query using the Query Design - and found that it had added an extra inner join that the Query Wizard had not, even though I used exactly the same tables and fields. I've highlighted the additional join in the Query Design result.

From Query Design

SELECT tblDOTOtoCDRL.Contract_Number, tblDOTOtoCDRL.Delivery_Task_Order, tblDOTOtoCDRL.CDRL_Number, tblDeliveriesReceived.Document_Number, tblDeliveredItems.Date_Due, tblDeliveredItems.Date_Received, tblDeliveredItems.Delivery_Is
FROM (tblDOTOtoCDRL INNER JOIN tblDeliveriesReceived ON tblDOTOtoCDRL.Relate_ID = tblDeliveriesReceived.Relate_ID) INNER JOIN tblDeliveredItems ON  (tblDOTOtoCDRL.Relate_ID = tblDeliveredItems.Relate_ID) AND (tblDeliveriesReceived.Deliverable_ID = tblDeliveredItems.Relate_ID);

From Query Wizard

SELECT tblDOTOtoCDRL.Contract_Number, tblDOTOtoCDRL.Delivery_Task_Order, tblDOTOtoCDRL.CDRL_Number, tblDeliveriesReceived.Document_Number, tblDeliveredItems.Date_Due, tblDeliveredItems.Date_Received, tblDeliveredItems.Delivery_Is
FROM (tblDOTOtoCDRL INNER JOIN tblDeliveriesReceived ON tblDOTOtoCDRL.[Relate_ID] = tblDeliveriesReceived.[Relate_ID]) INNER JOIN tblDeliveredItems ON tblDeliveriesReceived.[Deliverable_ID] = tblDeliveredItems.[Relate_ID];

So, I looked at the tables displayed in Query Design and noticed that in the one I created in the design tool, there was a third relationship line that doesn't display in the one built using the wizard. The relationship is there (and has to be) but the wizard ignored it since the field in the second table wasn't selected in the tool. The design tool added it to the query because it was there, even though the field in the second table wasn't in the query. I'd noticed the difference before but it didn't click that it was actually adding it to the query.

I'm now going back through the queries and removing the join that is causing the issue.

[orange]

Glad you have it sorted. Thanks for posting your activity and results.