Linked Tables

[s.booth]

I have a linked table that is in comma delimitted format, the problem I am having is that the date fields in the linked table have date fields that are empty which are shown as --/--/----.
To make the linked table work I have the properties of the date field set as text, but this causes a problem when querring the dates.

Is there a away of doing some sort of transformation on data on linked tables. ie convert --/--/---- to a default date, say 01/01/000 to allow the tables to be querried using dates?

 

[dcx693]

If you don't want to touch (alter) the data in the linked tables, you can use a calculated query field to change instances of --/--/---- to 01/01/000.

In your query place this formula into the field line, assuming the date field is called Date:
RealDate: CDate(IIf([Date]="--/--/----","01/01/000",[Date]))

This will create a new field called RealDate, insert January 1, 2000 as the default date if the date in the linked table says "--/--/----", and will convert the dates from text fields into actual dates so you can run queries based on the dates.