Truncate a date/time stamp

[scorpiorc]

Hi, I'm trying to write a query that will only return records from a table
where the date is equal to today's date. I'm using Date(). The date field
in the table that I'm querying has a date and time stamp, ex. 9/14/05
1:23:45PM. Of course when I run the query, it returns no records because
Date() will never find a match in the table's date field. Is there a way to
write the query so that it will only look to match the date and not the time?
I thought I could use Trunc, but that does not work. I also cannot modify
or update the table.

 

[Graham Mandeno]

Use the DateValue function to remove the time part:
... WHERE DateValue([DateField]) = Date()

 

[Crazyhorse]

Plus you could right a Expr in the qry- expr: Left([date],8) this just shows
you the first 8 positions in the field on the Left side

Use the DateValue function to remove the time part:
... WHERE DateValue([DateField]) = Date()
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I'm trying to write a query that will only return records from a table
where the date is equal to today's date. I'm using Date(). The date
field
in the table that I'm querying has a date and time stamp, ex. 9/14/05
1:23:45PM. Of course when I run the query, it returns no records because
Date() will never find a match in the table's date field. Is there a way
to
write the query so that it will only look to match the date and not the
time?
I thought I could use Trunc, but that does not work. I also cannot modify
or update the table.

 

[Ken Snell [MVP]]

No, not unless the poster is storing the date/time value as a "date string".
ACCESS stores date/time values as floating point numbers, not as text
strings.

--

Ken Snell
<MS ACCESS MVP>

Plus you could right a Expr in the qry- expr: Left([date],8) this just
shows
you the first 8 positions in the field on the Left side

Use the DateValue function to remove the time part:
... WHERE DateValue([DateField]) = Date()
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I'm trying to write a query that will only return records from a
table
where the date is equal to today's date. I'm using Date(). The date
field
in the table that I'm querying has a date and time stamp, ex. 9/14/05
1:23:45PM. Of course when I run the query, it returns no records
because
Date() will never find a match in the table's date field. Is there a
way
to
write the query so that it will only look to match the date and not the
time?
I thought I could use Trunc, but that does not work. I also cannot
modify
or update the table.

 

[John Vinson]

Hi, I'm trying to write a query that will only return records from a table
where the date is equal to today's date. I'm using Date(). The date field
in the table that I'm querying has a date and time stamp, ex. 9/14/05
1:23:45PM. Of course when I run the query, it returns no records because
Date() will never find a match in the table's date field. Is there a way to
write the query so that it will only look to match the date and not the time?
I thought I could use Trunc, but that does not work. I also cannot modify
or update the table.

Using Datevalue() as suggested is certainly a good way; another -
useful if the table is large and the date field is indexed - is to use
a criterion

= Date() AND < Date() + 1

to get all date/time values between midnight at the start of today and
11:59:59.999999999... pm tonight.

John W. Vinson[MVP]