search for dates in a text field

[MICKI0220]

We have a field in a table that's data type is Text....The users of this field put a date in it until the unit is loaded on a truck and switch the date to "LOAD"...This apparantly is why it is a text field and not a date field....I have a user who needs a report based on when and what dates are in that field....How can I do a search on Dates in a text field....I need a query like between this date and end date. I have no clue...the dates are in yyyy/mm/dd format.

Thanks in advance for your help.

 

[dhookom]

How about providing table and field names and some sample records? Are we supposed to understand the only possible values in the un-named text field are:
1) blank
2) "LOAD"
3) a legitimate date value


Duane
Hook'D on Access
MS Access MVP

 

[genomon]

You could loop through a recordset and use the IsDate() function to identify which records contain valid dates.

Beir bua agus beannacht!

 

[MICKI0220]

dhookam. Yes, the only data in the text fields are either, null, LOAD or a legitimate date such as 2012/05/21

 

[MICKI0220]

genomon.

Can you give me an example?

 

[PHV]

need a query like between this date and end date
SELECT ...
FROM ...
WHERE IsDate([yourField]) AND CVDate([yourField]) Between ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

Thanks alot PHV. I will try that.