Date() Criteria

[Guest]

Hello,

I have a basic query that is automated to export data into an Excel
spreadsheet. This query runs using the Date() function in the criteria field
to pull relevant records from that date. Now, I want to move this Date()
function to another field that is actually a DateTime field. This no longer
works.

Basically, how can I use something similar to the Date() function for a
DateTime field? Thanks.

 

[Guest]

Show us the SQL for what does and doesn't work. Open the query in design
view. Next go to View, SQL View and copy and past it here. Information on
primary keys and relationships would be a nice touch too.

 

[Michel Walsh]

Hi,


also, your new date_time field MAY have a time portion. A comparison will
check and the date, and the time, to say there is an equality. Date() having
no explicit time, it is taken as 00:00:00 (midnight) for this purpose. So,
unless your field has a record with the same date, also at midnight, that
will be considered DIFFERENT. You can try:

.... WHERE DateValue(YourDateTimeField) = Date()


with DateValue removing the time part, if any is present. On the other
hand, that may be quite slow, if you have many records. Maybe preferable to
use:


.... WHERE YourDateTimeField BETWEEN Date() AND Date() + 1




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Date()+1