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.

 

[Linq Adams via AccessMonster.com]

The problem is that "20081030" is not considered to be a Date by Access. In
your query you can use a calculated expression, rather than Criteria, to
change this to around, assuming (and this is very important) that the data
***always*** is 8 characters long!

In a new field in the Query Grid enter this:

NewDate:Mid([ImportedDateField],5,2) & "/" & Right([ImportedDateField] ,2)&
"/" & Left([ImportedDateField] ,4)

where [ImportedDateField] is the field holding the "date" that was imported.
In forms, reports, etc, you now use the calculated field, NewDate, whenever
needed.

If the imported dates don't always follow the 8 character formatting, however,
you're really sunk! The problem, of course, is that if it only has 7
characters, you have no way of knowing whether it was the month or the day
that has been entered with a one-character designation. If it only has 6
characters, it could be that the "date" has been entered using a one-
character designation for both the month AND the day, or it could be that the
month and day were entered using a two-character designation, but the year
was entered as 08 instead of 2008. You see the problem! Unless the "date" is
8 characters long, there's no sure way for Access to figure out which part of
it is which date component.

 

[Linq Adams via AccessMonster.com]

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.

Actually, I don't think Access will let you import that into a Date/Time
field, becuase it doesn't consider "20081030" to be a valid date. If you set
up a table with a text field, enter "dates" like this, then change the
datatype to DateTime, Access will actually delete the invalid data.

If you try running Format against the string "20081030" it'll bomb out with
an Overflow error. But if you parse the string into what looks like a date,
10/30/2008, with the hack I posted, Access will almost always accept it as a
date. If you find an instance where it doesn't (and I never have) you can
wrap it in the CDate() function.

 

[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.