A query is returning different data if I supply a value in criteria?!?

[KHerm]

I have a query that is joining four tables. The first two tables join on a sale identifier (text), the second and third table join on a customer number (number) and the third and fourth table join on the customer number to get a zip code.

Some of the zips are null so I supplied this code to supply 99999 where the zip code is null-- ZIP: IIf([ZIPCODE] Is Null,'99999',[ZIPCODE])

A lot of 99999 entries were returned, when we should have only had a few.

While troubleshooting, I simply put a sale identifier, that had returned 99999 in the ZIP column, in the criteria box of the query, and low and behold the proper zip code was returned!

This query worked before the underlying database (SQLServer) was upgraded from 6.5 to 7.

Any thoughts?

This doesn't make any sense to me.

Ken

 

[HowieG]

I have no experience with SQLServer, so perhaps this wouldn't work, but in JetSQL I would use:

ZIP: nz([ZIPCODE],"99999")

to get the same effect. See if that works.

nz() is a great function to take the place of iif() logic when trying to eliminate NULLs. Alternatively you could use:

ZIP: iif(isnull([ZIPCODE]),"99999",[ZIPCODE])

 

[KHerm]

Well, I get the same results whether I use the IIf statement on the zipcode or not. In other words, if I don't put the sale identifier in the criteria box of the query, the query "thinks" the zipcode is null. If I put the sale identifier in the criteria box, I will only get the one row, but it returns the zipcode successfully. I don't understand how.

Ken

 

[tlbroadbent]

I assume you've linked the SQL tables to Access. Which Access version? Open your query in SQL View, copy to the clipboard and paste into a message in this thread. Allow us to analyze the query.

I recommend that in the future you post questions about Access queries in the "Microsoft: Access Queries and JET SQL" forum - forum701. The "Access Other topics" forum is for questions that don't fit in other forums. See thread181-231806 for descriptions of the forums.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.