Changes Date Format from Date/Time to just Date

[Guest]

I hope someone can help. I have received data which appears as 01/01/2007
12:25:45. I need to change the details to just read Date and not the time as
when I use this field in a query look up no data returns as the system is
looking for an exact match. I want to produce another field if required that
just holds the date. Can anyone help

 

[Rick Brandt]

I hope someone can help. I have received data which appears as 01/01/2007
12:25:45. I need to change the details to just read Date and not the time as
when I use this field in a query look up no data returns as the system is
looking for an exact match. I want to produce another field if required that
just holds the date. Can anyone help

You can strip the time (actually set it to midnight) by using an update query
that uses the DateValue() function.

UPDATE TableName
SET FieldName = DateValue(FieldName)

But you can also just fix your query to work with the data you have now. For
example to get all records for yesterday's date...

SELECT *
FROM TableName
WHERE FieldName >= #2007-03-26#
AND FieldName < #2007-03-27#

 

[Guest]

I have tried this but "Formate" is not recognised. Do you have any other
ideas or have I copied it down incorrectly """Date:
(formate([Date/time],"short date"))"""

in a query:

[field2]: (formate([field1], "short date"))

replacing FIELD1 and FIELD2 with your chosen field names.

I hope someone can help. I have received data which appears as 01/01/2007
12:25:45. I need to change the details to just read Date and not the time as
when I use this field in a query look up no data returns as the system is
looking for an exact match. I want to produce another field if required that
just holds the date. Can anyone help

 

[Guest]

in a query:

[field2]: (formate([field1], "short date"))

replacing FIELD1 and FIELD2 with your chosen field names.

 

[Guest]

Don't use reserved words for fields.

It can upset VBA and JET for running code and SQL.

I have tried this but "Formate" is not recognised. Do you have any other
ideas or have I copied it down incorrectly """Date:
(formate([Date/time],"short date"))"""

in a query:

[field2]: (formate([field1], "short date"))

replacing FIELD1 and FIELD2 with your chosen field names.

I hope someone can help. I have received data which appears as 01/01/2007
12:25:45. I need to change the details to just read Date and not the time as
when I use this field in a query look up no data returns as the system is
looking for an exact match. I want to produce another field if required that
just holds the date. Can anyone help

 

[Rick Brandt]

I have tried this but "Formate" is not recognised. Do you have any
other ideas or have I copied it down incorrectly """Date:
(formate([Date/time],"short date"))"""

Its Format() not Formate().

Note that the output of Format() will no longer be a date. It will be a
string that just happens to look like a date. It will not sort like a date
and you will not be able to manipulate it as a date without explicitly
converting it back again. The DateValue() function mentioned in my previous
response is a better choice if you want to retain the DateTime Data Type.