Convert text date to date value

[unslog]

Hi

i have a linked table from ODBC , and trying to run a query.

One of the columns in the query is outputting date in this format 20100914

it should be outputting in this format dd/mm/yyyy

in the access design view, I have tried to select the field in question and prefix with cDate([datefield]) datefield being the field from the table containing the serial date. this doesn't work , the system changes to Expr1: CDate([datefield])

In the table this field has been as TEXT, and I cannot change to date.

Please can someone help as I need to add criteria to this field



e.g. >=Now()+"100"

[boblarson]

You can use this:

MyDate:Format(DateSerial(Left([datefield],4),Mid([datefield], 5,2),Right([datefield],2)),"dd/mm/yyyy")

And the criteria should be able to be

>=DateAdd("d", 100, Date())

(don't use NOW unless you need time included)

[RayMilhon]

You need to change it to a date Use CDate(mid(columnname,5,2) & "/" & right(columnname,2) & "/" & left(columnname,4))

This will give you a date in the format mm/dd/yyyy Then you can display it in any manner you want using format([converteddate],"dd/mm/yyyy")

[RayMilhon]

Bob beat me. plus I like his solution better. Mine takes 2 steps where his is done in 1.

[unslog]

Thanks guys in particular BOB, worked a treat.

I want to filter on all dates between today and today + 100days on this new Date column (MyDate)

I have tried >=DateAdd("d", 100, Date()) , but it returning dates in 2009

Also when I sort on this column , it doesn't appear to be sorting dates correctly e.g.

11/10/2011
11/10/2011
11/11/2010
11/11/2011


Can you help?

[boblarson]

I was afraid that might happen because FORMAT changes things to a string.

So, you should just create a separate field (doesn't have to be displayed) for the criteria. Use the same code I gave you but without the Format part. And then the criteria I gave you should work. Also sort on that field instead.

[unslog]

Hi Bob
the sort function is now working.

here are the settings on this column;
Field: Datefield
Table:PUBLIC_HEADER_TABLE
Sort: Descending
Criteria: >=DateAdd("d",100,Date())
Show: Unticked

Now, the column MyDate is visiable with no criteria or sort, however the dates on query run still appear to be outside filter e.g. 26/02/2010 , 03/01/2011,25/09/2010. No other criteria is being defined on this query


The PC date is set to 02/02/2012

Not sure why it is not why MyDates column is not displaying all dates between today and today + 100days ?

[boblarson]

You show the criteria on the datefield but it should be:

Field: DateSerial(Left([datefield],4),Mid([datefield], 5,2),Right(datefield],2))
Table:
Sort: Descending
Criteria: >=DateAdd("d",100,Date())
Show: Unticked

[unslog]

I think this has worked. Need to look at it with fresh eyes in the morning.
Thank you very much