Force a value into an empty query

[Nmidia]

Hi,

I have a need to group a set of 4 queries into a single query. They
are all very simple, short queries, being combined into a single
select query. Each of the 4 returns a value against a dataset name.

Dataset Value1
a 15

Dataset Value2
a 20

Dataset Value3
a 7

Dataset Value 4
a 8

So, when I combine the queries, I get the following:

Dataset Value1 Value2 Value3 Value4
a 15 20 7 8

Thats great, unless one of the first 4 queries returns nothing. If
thats the case, I don't get any values returned. Are there any neat
tricks to force a "0" (zero) into a query where it returns nothing?

 

[KARL DEWEY]

I would need to see you query but my guess is that the NZ function will do
it.
Use it in this fashion --
NZ([YourField], 0)

 

[Nmidia]

Thanks John, that worked a treat! Now thumping myself for not
thinking of that!

I also tried the NZ function, but that didn't seem to work in this
instance, although thanks for the suggestion

Adam

You have a problem here - you won't be able to do this unless you are sure
there is at least one of the queries that will have an entry for each dataset.

If you do have suchquery, then link the other three to that first one (on
dataset name) using outer join relationships.  Right click the relationship
line, select Join Properties, the use option 2: Include all records from
[Query1], where Query1 is the name of yourquerythat has all the entries in
it.

John

I would need to see youquerybut my guess is that the NZ function will do
it.  
Use it in this fashion --
    NZ([YourField], 0)

Hi,

[quoted text clipped - 22 lines]

thats the case, I don't get any values returned.  Are there any neat
tricks toforcea "0" (zero) into aquerywhere it returns nothing?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

http://www.accessmonster.com/Uwe/Forums.aspx/access/200803/1