Convert Excel formula to Access

ID Number breakdown:

75 = year
02 = month
22 = day
5 = gender

All of those functions work in Access except maybe 'Today()'. Exchange that for 'Date()' and remove the '=' at the beginning and it should work. Of course replace the cell references with field references.

It would probably make more sense to store the result of the function in another field in a table vs. calculating it on the fly each time you need it.

Thank you for the reply.

I fiddled with it per your suggestion and found that this works:

=Left([IdNumber],2)+IIf(Left([IdNumber],2)<Right(Year(Date()),2),2000,1900) & "-" & Mid([IdNumber],3,2) & "-" & Mid([IdNumber],5,2)

Original:
=DATE(LEFT(B1,2)+IF(LEFT(B1,2)<RIGHT(YEAR(TODAY()),2),2000,1900),MID(B1,3,2),MID(B1,5,2))


The red was not necessary and i had to concatenate the red commas if that makes sence.

For those who are lost, this formula is to get the Date of Birth (1982-12-25) from the id number 8212250058088 (South African Id Numbers). It is a formula for Access inside a query.

Thanx maw230

Glad you got it working!