---

MS Access 2003: Examples of converting Excel formulas to Access

This MSAccess tutorial explains how to convert Excel formulas to Access with an example in Access 2003 (with step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I'm trying to build an expression that will do the same as the following Excel formula with the result formatted as "dd/mm/yyyy".

=VALUE(DATE(VALUE(MID(A1,4,1))+2000,1,1))+VALUE(MID(A1,5,3))-1

How can I do this?

Answer: In an Access query, the equivalent formula would be:

Format(DateSerial(Val(Mid([Field1],4,1))+2000,1,1)+Val(Mid([Field1],5,3))-1,"dd/mm/yyyy")

This formula uses the Format function, DateSerial function, Val function, and Mid function in Access. We've also replaced the reference to cell A1 with Field1.