Date format change

  • Thread starter Thread starter Dennis Villareal
  • Start date Start date
D

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?
 
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.
 
it is a number field. it is not recognized as a date

KARL DEWEY said:
can this be done within the criteria field of my querty [sic]?
Click to expand...
Click to expand...
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


Dennis Villareal said:
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?
Click to expand...
Click to expand...
 
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

KARL said:
can this be done within the criteria field of my querty [sic]?
Click to expand...
Click to expand...
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.
Click to expand...
 
Back
Top