Return a date from a datetime

[Guest]

Hello, I'm using a MySQL backend with MSAccess for the forms and using the OBDC connector. I've got a table with a datetime field. When I run a query for a specific date the only records that are returned are those that match the date but without the time. (It is possible to enter just a date into this field and not a time.) I suppose, now that I write this, I could jsut tell the query to look for a matching date and a date with a time range (00:00:00 - 23:59:59).

There is a question that is still on topic. The datetime that is in this table is generated from a different table and cooresponding form. The generating form captures a datetime, in the target (and here topic) table I'm really only interested in the date. Can I copy over a datetime and only paste a date? I've tried where the date is put into the target form
Forms![material_in_stock]![MATERIAL_CREATION_DATE] = outDat
Forms![material_in_stock]![MATERIAL_CREATION_DATE].Format = (mm / dd / yy
and get an error

Any thoughts? Thanks in advance

-Jon

 

[Douglas J. Steele]

The DateValue function will strip off the time and simply return the date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I'm using a MySQL backend with MSAccess for the forms and using the

OBDC connector. I've got a table with a datetime field. When I run a query
for a specific date the only records that are returned are those that match
the date but without the time. (It is possible to enter just a date into
this field and not a time.) I suppose, now that I write this, I could jsut
tell the query to look for a matching date and a date with a time range
(00:00:00 - 23:59:59).

There is a question that is still on topic. The datetime that is in this

table is generated from a different table and cooresponding form. The
generating form captures a datetime, in the target (and here topic) table
I'm really only interested in the date. Can I copy over a datetime and only
paste a date? I've tried where the date is put into the target form:

Forms![material_in_stock]![MATERIAL_CREATION_DATE] = outDate
Forms![material_in_stock]![MATERIAL_CREATION_DATE].Format = (mm / dd / yy)
and get an error.

Any thoughts? Thanks in advance.

-Jon

 

[John Vinson]

Hello, I'm using a MySQL backend with MSAccess for the forms and using the OBDC connector. I've got a table with a datetime field. When I run a query for a specific date the only records that are returned are those that match the date but without the time. (It is possible to enter just a date into this field and not a time.) I suppose, now that I write this, I could jsut tell the query to look for a matching date and a date with a time range (00:00:00 - 23:59:59).

That's actually usually a good idea: it takes advantage of any Index
on the field. A suitable criterion would be

= [Enter date:] AND < DateAdd("d", 1, [Enter date:]

There is a question that is still on topic. The datetime that is in this table is generated from a different table and cooresponding form. The generating form captures a datetime, in the target (and here topic) table I'm really only interested in the date. Can I copy over a datetime and only paste a date? I've tried where the date is put into the target form:
Forms![material_in_stock]![MATERIAL_CREATION_DATE] = outDate
Forms![material_in_stock]![MATERIAL_CREATION_DATE].Format = (mm / dd / yy)
and get an error.

Use an Append query (or Update query) updating the target field to
Datevalue([datetimefield]). The Datevalue function trims off the
fractional portion of the number (i.e. the time).