Left join on two queries and want zeros to populate any data that is blank

[newbee]

I am combining two queries one has a list of stores, the other has a list of attributes for that store. What I need to happen is if the attribute does not appear for a store then the result would be zero instead of blanks. Is there a way of doing this?

[help_me_with_access]

YES. substitute the literal value of "0" or 0, depending on the data format, into the field and check for NULL suing the IIF() statement.

example:

SELECT t1.f1, t1.f2, t2.f1, IIF(t2.f2 IS NULL, "0", t2.f2) as t2.f2 FROM t1 LEFT JOIN t2 on t1.f1 = t2.f1 AND t1.f2 = t2.f2 

WHERE t2.f1 IS NULL

or, of course you don't need the criteria. the LEFT JOIN will take care of things itself. Unless I'm mistaken, I believe that will work.

[JoeM]

Alternatively, you may be able to use the NZ function also, which you tell it what to return in place of a NULL value, i.e.
NZ([Field1],0)

[newbee]

Thanks, I used the first with some changes and it worked. I am going to try the second method on something similar.

[help_me_with_access]

Alternatively, you may be able to use the NZ function also, which you tell it what to return in place of a NULL value, i.e.
NZ([Field1],0)

this is not a catch all, but I've had unpredictable results with NZ(). I never use it because of this reason. It seems as though NZ() is a function that becomes unpredictable from Access version to Access version.

I've had issues, but does not mean that anyone else will...

[JoeM]

this is not a catch all, but I've had unpredictable results with NZ(). I never use it because of this reason. It seems as though NZ() is a function that becomes unpredictable from Access version to Access version.

I've had issues, but does not mean that anyone else will...I have

That's odd. I have been using it for years in Access 2000, 2003, and 2007, and cannot ever recall having an issue with it.
Do you remember the circumstances/situations in which you were experiencing problems?
If there are situations like you describe, I would like to file those away in my memory banks!

[help_me_with_access]

That's odd. I have been using it for years in Access 2000, 2003, and 2007, and cannot ever recall having an issue with it.
Do you remember the circumstances/situations in which you were experiencing problems?
If there are situations like you describe, I would like to file those away in my memory banks!

no I don't remember the scenario. but that's not the first time I've heard that either. no idea what's going on with it...

[JoeM]

no I don't remember the scenario. but that's not the first time I've heard that either. no idea what's going on with it...

Hmmm... Not sure what to do with that. It is hard to defend a method if you don't know what the charges are!

Like I said, I have been using it for years across three different versions without issues. Not saying that there aren't some obscure circumstances in which it might not work as expected, I just haven't come across any, though. Of course, we can't discount user error. Anything can have issues if it is not used correctly too!