Date format change

[Dennis Villareal]

i am importing from a database that cannot be modified. on my query i am
pulling the date field and the format is "20081030" year month day. i would
like for the date to be displayed on my reports as month day year or "Oct 30
2008" or "10/30/2008" or "10 30 2008" anything better then the original. can
this be done within the criteria field of my querty?

 

[KARL DEWEY]

can this be done within the criteria field of my querty [sic]?
No. What type of field are you importing the date into? Is it text,
number, or datetime?
If it is a datetime field then you can use the Format function.
If it is a number or text field then it needs to be manipulated using left,
right, and mid functions.

 

[Dennis Villareal]

it is a number field. it is not recognized as a date

can this be done within the criteria field of my querty [sic]?

No. What type of field are you importing the date into? Is it text,
number, or datetime?
If it is a datetime field then you can use the Format function.
If it is a number or text field then it needs to be manipulated using left,
right, and mid functions.
--
KARL DEWEY
Build a little - Test a little

i am importing from a database that cannot be modified. on my query i am
pulling the date field and the format is "20081030" year month day. i would
like for the date to be displayed on my reports as month day year or "Oct 30
2008" or "10/30/2008" or "10 30 2008" anything better then the original. can
this be done within the criteria field of my querty?

 

[Dennis Villareal]

Sweet this worked thanks alot. saved me alot of brainstorming.

 

[John Spencer]

Assuming the field is a text field and always has a value, you can use this
expression to change the field from a string to a dateTime

DateValue(Format(SomeField,"@@@@-@@-@@"))

If you need to test then try
IIF(IsDate(Format(SomeField,"@@@@-@@-@@")),CDate(Format(SomeField,"@@@@-@@-@@")),Null)

Once you have that you can apply a format to it to display the date time any
way you wish.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

can this be done within the criteria field of my querty [sic]?

No. What type of field are you importing the date into? Is it text,
number, or datetime?
If it is a datetime field then you can use the Format function.
If it is a number or text field then it needs to be manipulated using left,
right, and mid functions.