Change field from Text to date? 1

[jazminecat]

Hi all -

I have a table that an enduser imports once a month from excel. It has a field that is a date in 01/01/07 format, but when it imports, it sets this as a text field. Each month the user imports the data, overwriting the existing table, and then runs a form, which runs a parameter query, and produces a report for her. It's all pretty simple. However, I'd like to find a quick and dirty way to programatically change that field to a date. Because the query doesn't return consistent results otherwise. Here's the query:

SELECT tblNextStepMain.ID, tblNextStepMain.YAAN8, tblNextStepMain.YAALPH, tblNextStepMain.YADR, tblNextStepMain.YANRDT, tblNextStepMain.YAFTE, tblNextStepMain.YAPGRD, tblNextStepMain.YAPSTP, tblNextStepMain.YMOSAL, tblNextStepMain.YAPHRT, tblNextStepMain.YASALY, tblNextStepMain.YAP005, tblNextStepMain.YAP006, tblNextStepMain.YAMAIL01, tblNextStepMain.YAJBCD01, tblNextStepMain.YAJBST01, tblNextStepMain.YAUN
FROM tblNextStepMain
WHERE (((tblNextStepMain.YADR)>=[Last step date on or after:] Or (tblNextStepMain.YADR) Is Null));

The date is tblNextStepMain.YADR.

But the results of the parameter query are inconsistent. With this as a sample of the data:

ID YAAN8 YAALPH YADR
61 224805 Name1 01/01/07
223 1725978 Name2 01/30/07
163 1367171 Name3 01/01/07

if I enter 01/01/07 in the parameter, it returns all of these. But if I enter 02/05/05 it returns nothing. 01/01/06 will return the above records, but not 02/01/06.

So I'm thinking it's because it's text and not a date.

Rather than have the user go into the table to change the data, how can I do this with code in the on open event of that form?

THanks!

 

[PHV]

You may try this WHERE clause:
WHERE CVDate(tblNextStepMain.YADR)>=CDate([Last step date on or after:]) Or tblNextStepMain.YADR Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

Once again, PHV to the rescue. Thanks for that quick and efficient fix! Have a star!