Format Text as Date

[razorking]

I know this has been brought up many times in various incarnations - however, despite much time spent Googling this the things I have tried thus far are not working.

I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:

7/24/12
10/08/13

I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?

 

[David R]

What things have you tried? Do they include CDate()?

What is the source of the link table?

 

[razorking]

I have tried:
ODATE: Format([CYC-DATE],"Short Date")
OCDATE: Format([CYC-DATE],"mm/dd/yy")

If I try to use CDate I get - data type mismatch errors.

The source is a text file - link table. The source is what it is. I link to it and then I make a table from it, then I query the table that was built from a make table query - that links to the text file.

 

[razorking]

Pat and David,

Thanks for the replies - I understand the information you have provided. The CDate does work. The reason I was/am getting the data type mismatch is due to the fact that some records have no date - and currently they appear as 0/00/00. If I delete them the CDate query works. The problem is I need them to show. Is there any graceful way to include those records in the query and not get the data type mismatch errors?

 

[razorking]

I found this out there - and it seems to work - should be fine for the limited use this requires:

Expr1: IIf(IsDate([CYC-DATE]),CDate([CYC-DATE]),Null)