Date Issue - Query does not pull data for last day of the month

[philipscodes]

Greetings,

I have a query which ran successfully for months.
However, it does not pull the records for December 31st, 2017, while querying into Excel.

I checked the table and it clearly has the date field set to 'dd/mm/yyyy' format.
I even tried the built-in 'Short Date' format and updated all related components with no success.
The table clearly shows the date in 'dd/mm/yyyy' format.
However, the query based on this table shows the 'General Date' format (with time) for dates ranging from December 24th, 2017 through to December 31st, 2017.
The rest for the records for the month shows the 'Short Date' format.
The query in Access pulls all records correctly for the month of December.


However, when I pull data from Access on to Excel via 'Get External Data' query method, it does not pull in the records for December 31st, 2017 for some reason, which I guess has something to do with the date + time format for the these records.

I tried using the Format() function on the query for the dates but it still does not pull the last day records, as I believe this is only for display purpose.

Kindly can anyone advise how to go about this please.

Note: The table is appended every evening from our CRM report which is in an Excel file format - there is no time component in that file.

Warm Regards,
Philip

[John_G]

However, when I pull data from Access on to Excel via 'Get External Data' query method,

I'm confused by that - are you working in Access and exporting data to Excel, or are you working in Excel and importing from Access?

Note that the format setting and the Format() function affect only how you see the data - they do not affect how the data is stored in Access (unless you are not using a date/time type for the data).

If you are not getting the data for Dec. 31, either there is no data for it, or the query is incorrect. Can you post the SQL of the query?

[philipscodes]

Working in Excel and Importing Data from Access.

Also, I just managed to solve the issue.
I used DateValue() function on the Date field and it works fine now.

Thank you,
Warm Regards,
Philip

[ArviLaanemets]

How you set the upper limit for dates?

When you set the upper limit <= of December 31, you leave out all dates with non-zero time part on December 31.

Use < of January 1s of next year instead.