expression

[patricespencer]

I have a form with the following expression:

NRD: IIf(Left(DateAdd("yyyy",65,[E DOB]),2)="12" And Mid(DateAdd("yyyy",65,[E DOB]),4,2)>16," 01/01/" & Right(DateAdd("yyyy",65,[E DOB]),4)+1,IIf(Mid(DateAdd("yyyy",65,[E DOB]),4,2)<=16,Left(DateAdd("yyyy",65,[E DOB]),2) & "/01/" & Right(DateAdd("yyyy",65,[E DOB]),4),Left(DateAdd("yyyy",65,[E DOB]),2)+1 & "/01/" & Right(DateAdd("yyyy",65,[E DOB]),4)))

My problem is that this formula works in some releases and not in others. I have a system with 9.0.2720 and it works but on my other system that is MS Office 2000 premium that same formula does not work, it give me an #error in the field.

Does anyone have any idea why that would be.

Thanks

 

[Jack Cowley]

From any Access code page on a machine that is giving you the #Error message select the Tools menu. Then select references. See if any items are checked that say "Missing". If so, uncheck those items and try again. Look at the References on a machine that is working, note which references are checked, and be sure the same ones are checked on the other machine.

 

[patricespencer]

Thanks for the quick answer. I did go into Modules|New then did the Tools|Reference. I did see a difference in the setup and went back to my system and changed the settings but I am still having the same problem.

I did discover that if the DOB month was 10, 11 or 12 the formula in the form worked just fine, but if the date was a single digit or started with a 0 (ex. 02) then the formula doesn't work on my system.

Do you have other ideas of where I can look.

Thanks for all your help.

 

[D-Fresh]

It is most likely in the formatting of your dates. Check the regional settings in your Control Panel and see what format your date is in. It is really tough to use the Mid function with dates unless you use it on a formatted date, i.e. mid(format(Date,"mm/dd/yyyy"),4,2). Without the formatting, you might have a date like 6/25/01. When using your function to get the month, it will return "5/" which is where your #error comes in. If you're looking for the month day or year of a date, your best bet is to use those functions, Month, Day, and Year. This will extract an integer value of the date part that you are looking for. Hope this explains it for you.

Doug

 

[patricespencer]

Doug,

Thanks so much. I changed the regional settings and it worked. I guess I just assumed that having set the formatting in Access would make the formula work.

Once again, thanks for all your help.

Patrice