Force a value into an empty query

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?

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)
--
KARL DEWEY
Build a little - Test a little


"Nmidia" wrote:

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?

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 such query, 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 your query that has all the entries in
it.

John



KARL DEWEY wrote:
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)
Hi,

[quoted text clipped - 22 lines]
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?

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200803/1

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


On 19 Mar, 15:12, "J_Goddard via AccessMonster.com" u37558@uwe
wrote:
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

KARL DEWEY wrote:
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

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200803/1