I have an ODBC link set up between a DB2 table and MS Access. A date column from the DB2 table is coming through as a text column in Access. I can't change the datatype in design view in Access. Is there a way to convert the text into a date format in a query?

This is an example of the text formatted date. I want to convert to something as simple as "2001-12-14". want to be able to run some easier queries for date ranges but this text format is giving me trouble.

2001-12-14-02.20.07.676990

Thanks,
JG

Type this in a new column in the field box

NewDate: DateValue(Left(DateFieldName,10))

This is presuming that the date aspect will always be 10 characters long ie 1st Jan is 2002-01-01-........

HTH

If you use DB2Connect, you can select the patch that will allow Access to view this column as a proper Date/Time data type. Unfortunately, I cannot be more specific. My current client does not use DB2Connect so I can't look it up for you. If you don't have DB2Connect available to you, the DB2 DBA may be able to resolve the problem at the gateway.

Thanks to both of you. I am using the Date Value workaround for now and will see if the DBA can add that patch. I found which one it is. Thanks again.

Hi -

You might try playing around with this (from the debug window):

'********************************************
x = "20110315"
y = datevalue(mid(x,5,2)& "/" & mid(x, 7,2) & "/" & left(x,4))
z = format(y, "mmmm yyyy")
? z
March 2011

'********************************************

Best wishes -- Bob

Hi Bob, I'm a bit puzzled as this looks like you are replying to Blackops on this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=207369) rather than this ancient thread.

Brian

Thanks Brian -

I apparently suffered a serious brain cramp.

Sorry if I threw unintended confusion into the pot.

Best wishes - Bob