Date conversion

[moben]

I have a append query, where a field is an expression consisting of some fields which return a date of format 29SEP06.

I actually want a value of 290906 to be saved to a field of type Date within a table.

I have tried the following to no avail i.e. nothing saved:

format(cdate("29SEP"&"06"),"ddmmyy")

The 29SEP and 06 are seperate fields that have to be concatanated together.

Any help appreciated.

Mo.

 

[Golom]

format(cdate("29-SEP-"&"06"),"ddmmyy")

 

[raskew]

Hi -

Regardless of formatting, Access stores dates in a date/time field as doubles representing the number of days since 30-Dec-1899. You can test this from the debug (immediate) window:

Today is 28-Sep-2006

? cdbl(date())
38988

Attempting to store 290906 in a data/time data type field will return 20-Jun-2696.

--
The DateValue() function will convert a string example to a true date/time data type and that's what needs to be stored.

x = datevalue("28-SEP-"&"06")
? cdbl(x)
38988

Then, for display purposes, you format that field as necessary.
? format(x,"ddmmyy")
280906
--
This MSKB articles provides information on how dates are stored. http://support.microsoft.com/kb/q130514/

HTH - Bob

 

[moben]

Thanks for responses.

I actually gave you incorrect info.

I want the end concatanated date i.e. 29SEP06 as 29-09-06 stored in a date field.

As I mentioned, I want to be able to perform the conversion within a Access 2000 append query.

Golom I tried format(cdate("29SEP06"),"dd-mm-yy"), but this does not seem to work.

Regards,

Mo.

 

[moben]

Got it working, as per original code (as Golom mentioned). Clumsy error on my part, receiving field was Text of char 2 ! I should have checked the field size and properties !

Thanks again.

 

[raskew]

receiving field was Text of char 2 !

What exactly does that mean?

Bob