Querying Dates (Date/Time and Text Format)

[sstump]

I have table1/field1 with a date that is in the Date/Time format. Now I'm querying two different tables, one of these tables is set with Date/Time format (table2/field1), the other is setup with only a Text format (table3/field1).

Querying table2/field1 is easy, however how do I change the format when it queries table3/field1 to query as a Text format. Below is what I have so far just in the Query Criteria

I'm querying with a Between and DateAdd Statement as below

Not Between DateAdd("d",[table2]![field1],-5) And DateAdd("d",[table2]![field1],5)

OR

Not Between DateAdd("d",[table3]![field1],-10) And DateAdd("d",[table3]![field1],10)

Any assistance would be appreciated.

Thanks!

 

[SteveR77]

If the Date field that is text contains properly formatted dates you can use the CDate function.

CDate([textDate]) then set the criteria as you normally would for a Date/Time field.

 

[ahmun]

If you are writing in VB, be careful of the syntax for DateAdd (looked under the "F1" help in Access):
DateAdd(interval, number, date)

Not Between DateAdd("d",-5,[table2]![field1]) 
        And DateAdd("d",5,[table2]![field1])
OR 
Not Between DateAdd("d",-10,

CDate([table3]![field1])

) 
        And DateAdd("d",10,

CDate([table3]![field1])

)

Earnie Eng