How to add values from different queries with nullvalues 1

[marlun]

Hi!!!
I have a problem with a query. I have three select-queries which gives me three different values I want to add (using a fourth query). That works fine but when one of the three select-queries doesn't "select" a value The fourth query doesn't select anything. How can I prevent this from happening. If one query selects "null" I want the other two to be added.

Hope someone understands

Thanks
marlun

 

[jebry]

Hi!

I am assuming that in the fourth query you have a field like this:

query1.field + query2.field + query3.field

If that is the case then instead:

Nz(query1.field, 0) + Nz(query2.field, 0) + Nz(query3.field, 0)

hth


Jeff Bridgham
bridgham@purdue.edu

 

[marlun]

That's correct. But where do I put the [ ]??

[Nz(query1.field, 0)] - [Nz(query2.field, 0)] + [Nz(query3.field, 0)]

in my case there is subtraction too.
When I wrote like I have done above I get a small window with "Enter value" or something like that.

 

[jebry]

Hi!

Like this:

Nz([query1].[field], 0) + Nz([query2].[field], 0) + Nz([query3].[field], 0)

Of course you need to substitute your actual query and field names which I am assuming you are doing.



Jeff Bridgham
bridgham@purdue.edu

 

[marlun]

Hi again!! It doesn't seem to work.
this is how my line looks like:

Summa Förste: Nz([Ek_rap_siste_totalt].[SummaförSumma Siste],0) - Nz([Ek_rap_in_import_total].[In_total],0) + Nz([Ek_rap_ut_total].[SumOfSumma Ut],0)

Can you see if there is something wrong with it???

 

[jebry]

Hi!

What sort of error are you gettin? It is possible that Nz is not a part of Jet. You can try it like this:

Summa Förste: IIf(IsNull([Ek_rap_siste_totalt].[SummaförSumma Siste]) = True, 0, [Ek_rap_siste_totalt].[SummaförSumma Siste]) - etc.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[marlun]

thanks for your help, worked perfectly on my computer with access 2000, but didn't work with office xp.
I don't know why, maybe you do.
Appreciated your help

/marlun

 

[jebry]

Hi!

Sorry, I haven't worked with XP except on a very limited basis. I would be surprised if MS removes this sort of functionality from their SQL. One last thing you can do is to design a Public function is module that will return the correct number when you pass the field to it in then query.

Public Function CheckNulls(TableValue As Variant) As Integer or Double whatever you need

If IsNull(TableValue) = True Then
CheckNulls = 0
Else
CheckNulls = TableValue
End If

End Function

Then in the query:

Summa Förste: CheckNulls([Ek_rap_siste_totalt].[SummaförSumma Siste]) - etc

hth


Jeff Bridgham
bridgham@purdue.edu