Date Change

[Mike]

I am trying to convert a date field (CYYMMDD) to the format MMDDYYYY
using an update query on Access 2000. The formula I am using is:

Format(DateSerial(Left([Invoice Date],3),Mid([Invoice
Date],4,2),Right([Invoice Date],2)))

This is not working, it will not detect the records currently formatted
as: CYYMMDD.

Any suggestions on how to fix this?

Mike

 

[John Vinson]

I am trying to convert a date field (CYYMMDD) to the format MMDDYYYY
using an update query on Access 2000. The formula I am using is:

Format(DateSerial(Left([Invoice Date],3),Mid([Invoice
Date],4,2),Right([Invoice Date],2)))

This is not working, it will not detect the records currently formatted
as: CYYMMDD.

Any suggestions on how to fix this?

Mike

The first argument of the DateSerial function must be a complete year
- e.g. 2005, 1994, or the like. I'm not sure what's in your "C"
subfield but a three-byte substring will not return a four-digit year!

Also, you don't need the Format() function. DateSerial will return a
valid Date/Time value which your computer should display using its
current Windows date/time short date setting; if you really want to
store the reformatted date back into the text field, which I do NOT
recommend, you'll need to *specify* a format.

GUESSING here that C=9 means 20th century, C=0 21st, then try

Format(DateSerial(Switch(Left([Invoice Date], 1) = "9", "19",
Left([Invoice Date], 1]) = "0", "20") & Mid([Invoice Date], 2, 2),
Mid([InvoiceDate], 4, 2), Right([Invoice Date], 2)), "mmddyyyy")

Again, this will not store a Date/Time value but rather a String.
Consider instead adding a date/time value and updating it to the
DateSerial() expression; you can then format it for display any way
you like, even several different ways in a single report or form.

John W. Vinson[MVP]

 

[Al Camp]

Mike,
I tried this out, but had no luck with DateSerial when there's only 2
digits for the year (YY)
If your data was
C19901010
=DateSerial(Mid([DOC],2,4),Mid([DOC],6,2),Right([DOC],2))
would yield 10/10/1990.

I don't think Access can determine a year from 2 digits. Think about
it... "45" by itself could be 2045 or 1845... etc.

IF your dates are all 1900's and on up to 2010...

An Update query that looks at the 4th and 5th digits should be able to
convert the string to a proper DateSerial format.
Create a new field to receive the new String (ex. ConvDateStr), so you
don't screw up your original values.
You appear to know String functions well...
First pass... (against C901010)
If the Val of digits 4 and 5 > 10 AND <= 99... then you could insert "19"
into 2nd and 3rd position yielding 10/10/1990.
Second Pass... (against C041010)
If the Val of digits 4 and 5 >= 0 AND <10... then insert a "20" into
position 2 and 3, yielding 10/10/2004.

Perhaps someone ahs a more sophisticated answer, but this is at least a
workable possibility...
Good luck!
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Mike]

Thank you thank you thank you. With a little "tweaking", this worked
great. I appreciate the help.