Query Iff statement works in Access 2000 but not in Access 97 2

[paulfla]

I built a query that works in Access 2000 (A2k), but not in Access 97(A97).

In A2K I used the Convert Database, Prior Access Database Version. So I could use the DB in A97 on another PC.

Here is the code in the query field:


zipformat: IIf(Len([ZipCode])>5,Left([ZipCode],5) & "-" & Right([ZipCode],4),Left([ZipCode],5))

What the Iff statement is saying is:
If the length of the zipcode field is greater than 5, then place the "-" between the 5th and 6th digit of the zipcode field,

else
just display the first five digits of the zipcode.

end if

This works fine in Access 2000. In Access 97 the code passes the vaildation (ie. I can save the query with out an error msg), but when I display the results in Datasheet view I get the #ERROR in the zipformat field.

I sure would appreciate anyones help.

 

[petersdaniel]

Well, it isn't an issue with the intrinsic operation of the iif function. "Iif" has been around for some time and this function should work.

Have you tried backing this down in steps to see if it is a specific portion of the statement that is causing the issue?

Try:

IIf(Len([ZipCode])>5,"Yes", "No&quot. if that passes then add your true false responses back in. A friend once told me that learning to break the issue down and "baby step" the problem gets you results quicker in the long run.


petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[paulfla]

Peter, Thanks for your response. Prior to my initial post I did do something like that. I entered the code below and still received the #ERROR:

zipformat: Left([ZipCode],5)

I was thinking there was some feature that was not selected during the install of Access 97.

 

[petersdaniel]

That is a possibility. Unfortunately, I no longer have '97 on my platform due to low demand for it. In most cases, I am converting up to 2K or XP (2002) petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[VogonPoet]

It sounds like you may be missing a reference to one or more files. Try checking the references in VBA a look for missing references.

Michael

 

[paulfla]

I'm not sure where to look for the references, but I will look through the help files and other resources to figure out where to look for this.

On important piece of information: I went to the PC that had the original DB in Access 97. As you know from above the Access 2K DB didn't work when I did a save for previous version on my PC and tried to load on the original PC which uses Access 97.

What I ended up doing is take the exact query code that I used in the Access 2K DB and pasted into the original Access 97 DB and the query worked fine.

Does anybody know why this is the case?