Change Text to Date/Time field in a query

[akhlaq768]

I have created a table called - "Test"
The properties of the table is listed below

Table Name: Test
Field Name: ADMDAT2 (Text)
DISDAT2 (Text)
Operation Date (Date/Time)

I have written a query to populate a field where the Operation Date is between the ADMDAT2 and DISDAT2

Expr1: IIf([ADMDAT2] Is Null,"",IIf([Operation Date]>=[ADMDAT2] And [Operation Date]<=[DISDAT2],"Match"))

Unfornately it returns and ERROR message... I believe this may be because, the data type of the field, matching a Text with a Date/Time, I have rerun the query using a sample table where all the fields are Date/Time, and it work perfectly.

What i need help with how do i convert a text field into a Date/Time in a query?

So i can place that in the query before i populate the Test table. therefore it all should be date/time

thanks in advance

 

[raskew]

1. Lookup the DateValue() function in your help file. You can use this to convert a text field to date/time data format.

2. The question, however, is why you would create a new table that depicts dates in text format? As you've found, this makes your tasks unnecessarily difficult and robs you of Access's many date/time manipulation functions.

Bob