changing date format in a query

A

Allen Browne

Go to the Windows Control Panel | Regional Options | Date.

Your date/time fields in Access will use the Short Date format you specified
there, unless you set the Format property of the text boxes differently.
 
G

Guest

Hi Fred. Thanks for your help.
In my query, I want to search between two date fields that
are formatted dd/mm/yyyy using the date field that is
formatted yyyymm.
I know that I have to tell Access to use the month and
year from yyyymm and add in 01 for the day but I don't
know how. Can you enlighten me?


-----Original Message-----
How do I change a date yyyymm to mm/dd/yyyy?
Click to expand...
Is the [Date] field a Date/Time datatype?
In a query?
ColumnName:Format([DateField],"mm/dd/yyyy")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
J

John Vinson

Hi Fred. Thanks for your help.
In my query, I want to search between two date fields that
are formatted dd/mm/yyyy using the date field that is
formatted yyyymm.
Click to expand...

The Format merely controls how the data is *displayed* - regardless of
the format, a Date/Time value is stored as a Double Float number, a
count of days and fractions of a day (times) since midnight, December
30, 1899. In other words - a date *is not a string*, and adding 01
won't work!
I know that I have to tell Access to use the month and
year from yyyymm and add in 01 for the day but I don't
know how. Can you enlighten me?
Click to expand...

Try

BETWEEN DateSerial(CInt(Left([Enter date yyyymm:], 4)),
CInt(Right([Enter date yyyymm:], 2)), 1) AND
DateSerial(CInt(Left([Enter date yyyymm:], 4)), CInt(Right([Enter date
yyyymm:], 2)) + 1, 0)
 
Top