Undefined Function Mid in Expression

[paul29berks]

I have written a statement within a select query that modified the format of a date value:

Date of Birth: IIf([Date_Of_Birth]="00000000","",DateValue(Mid([Date_Of_Birth],7,2) & "/" & Mid([Date_Of_Birth],5,2) & "/" & Mid([Date_Of_Birth],1,4)))

But receive the error:

Undefined Function Mid in Expression

Can anyone help?

thanks

 

[pritcham]

Hi

What version of Access are you using? I've got 2000 here and just checked your formula and it works fine. From the look of your code, I assume that your DOBs are currently stored in yyyymmdd format?

Martin

 

[Norie]

paul

That sounds to me as though you might have a corrupt database.

Does that expression work if you create a new database and import the tables/queries from the original?

 

[SydneyGeek]

If you have upgraded the database from 97 to 2000 or higher, this often happens.

Go into any module and check Tools | References. It is likely that one of them will be marked as Missing. Note the name of the library, deselect the missing reference, scroll down the list and select the version that you have. Click OK and try the query again

Denis