You will find dates easier to work with if you leave them as date data types
and just format them as needed when you need something different.
Where is this value coming from? You state that it is "brought into the
query". If it is an Access table, is the data type of the field set to
Date/Time?
Regardless of the above, eventually you'll need to create 3 calculated
fields for the query to do what you're asking. The calculation is what will
vary, depending on what the value really is (a number as a text string or a
date data type).
To create a calculated field, open the query in design view. In the field
box, type the name you would like for the calculated field (don't use a
reserved word such as Date, Month, Year, etc). Follow this by a Colon and
then the calculation.
Example:
MonthFromDate:Format([DateField], "mm")
or
MonthFromDate:Month([DateField])
The second one will drop the leading zero if you're always wanting 2 digits.
The examples are based on the value being a Date/Time data type. If it is
just a string of numbers, you'll have to break the string apart. If it is
always 8 characters, this is fairly simple.
Example:
MonthFromDate:Mid([DateField], 5, 2)
DayFromDate:Right([DateField],2)
YearFromDate:Left([DateField],4)
--
Wayne Morgan
MS Access MVP
Tim Whitley said:
Is there a way that I can modify the data as it is brought into the query
to
create 3 fields with the YYYY MM and DD vs 1 field with YYYYMMDD?
