changing date format in a query

  • Thread starter Thread starter Elisa
  • Start date Start date
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.
 
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...
 
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)
 
Back
Top