NZ #ERROR

D

DS

I'm using this Expression and I keep getting an #ERROR.

=NZ(DSum([Mod Price],"Mods Detail Query",[Order ID]=[Order ID]),0)

This is for an unbound field...
If I take out the DSum() and use regular Sum() I get the 0 when I have a
null, however; the Sum() doesn't give me what I want...any suggestions.
Thanks
DS
 
E

Ed Robichaud

Each parameter of a Domain function must be enclosed in quotes, so your
expression should be:

=NZ(DSum("[Mod Price]","[Mods Detail Query]","[Order ID]=[Order ID]"),0)

-Ed
 
V

Van T. Dinh

Try:

=NZ(DSum("[Mod Price]", "[Mods Detail Query]",
"[Order ID]= " & [Order ID]), 0)

(I am guessing you have a TextBox Control [Order ID] on the Form. If it
doesn't work, you have to explain where the value of [Order ID] comes from.)
 
D

DS

Van said:
Try:

=NZ(DSum("[Mod Price]", "[Mods Detail Query]",
"[Order ID]= " & [Order ID]), 0)

(I am guessing you have a TextBox Control [Order ID] on the Form. If it
doesn't work, you have to explain where the value of [Order ID] comes from.)
Click to expand...
I found the problem....but not the answer. The problem is...
On the first Subform I have records,
On the second Subform I have records that are associated with the first
Subform. On each, in the footer I have an unbound textbox that totals
the dollar amounts. It all works fine if the second Subform always has
a record in it. But if the second Subform doesn't have a record in it
the unbound textbox on the second form gives me nothing. I also found
that I needed an IIF statement instead of the NZ, so I'm using
this...instead.
=IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
ID]=[Order ID]))

Any suggestions would be most helpful.
Thank You
DS
 
D

DS

Ed said:
Each parameter of a Domain function must be enclosed in quotes, so your
expression should be:

=NZ(DSum("[Mod Price]","[Mods Detail Query]","[Order ID]=[Order ID]"),0)

-Ed


I'm using this Expression and I keep getting an #ERROR.

=NZ(DSum([Mod Price],"Mods Detail Query",[Order ID]=[Order ID]),0)

This is for an unbound field...
If I take out the DSum() and use regular Sum() I get the 0 when I have a
null, however; the Sum() doesn't give me what I want...any suggestions.
Thanks
DS
Click to expand...
Click to expand...
Thanks ED, see the previous post...my problem is unfortunately much
deeper than I thought.
DS
 
D

DebbieG

I have this function in a module:

Public Function nnz(TestValue As Variant) As Variant
If Not (IsNumeric(TestValue)) Then
nnz = 0
Else
nnz = TestValue
End If
End Function


=DSum("nnz([Mod Price])", "[Mods Detail Query]", "[Order ID]= " & [Order ID])

HTH,
Debbie


| Van T. Dinh wrote:
| > Try:
| >
| > =NZ(DSum("[Mod Price]", "[Mods Detail Query]",
| > "[Order ID]= " & [Order ID]), 0)
| >
| > (I am guessing you have a TextBox Control [Order ID] on the Form. If it
| > doesn't work, you have to explain where the value of [Order ID] comes from.)
| >
| I found the problem....but not the answer. The problem is...
| On the first Subform I have records,
| On the second Subform I have records that are associated with the first
| Subform. On each, in the footer I have an unbound textbox that totals
| the dollar amounts. It all works fine if the second Subform always has
| a record in it. But if the second Subform doesn't have a record in it
| the unbound textbox on the second form gives me nothing. I also found
| that I needed an IIF statement instead of the NZ, so I'm using
| this...instead.
| =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| ID]=[Order ID]))
|
| Any suggestions would be most helpful.
| Thank You
| DS
 
D

DS

DebbieG said:
I have this function in a module:

Public Function nnz(TestValue As Variant) As Variant
If Not (IsNumeric(TestValue)) Then
nnz = 0
Else
nnz = TestValue
End If
End Function


=DSum("nnz([Mod Price])", "[Mods Detail Query]", "[Order ID]= " & [Order ID])

HTH,
Debbie


| Van T. Dinh wrote:
| > Try:
| >
| > =NZ(DSum("[Mod Price]", "[Mods Detail Query]",
| > "[Order ID]= " & [Order ID]), 0)
| >
| > (I am guessing you have a TextBox Control [Order ID] on the Form. If it
| > doesn't work, you have to explain where the value of [Order ID] comes from.)
| >
| I found the problem....but not the answer. The problem is...
| On the first Subform I have records,
| On the second Subform I have records that are associated with the first
| Subform. On each, in the footer I have an unbound textbox that totals
| the dollar amounts. It all works fine if the second Subform always has
| a record in it. But if the second Subform doesn't have a record in it
| the unbound textbox on the second form gives me nothing. I also found
| that I needed an IIF statement instead of the NZ, so I'm using
| this...instead.
| =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| ID]=[Order ID]))
|
| Any suggestions would be most helpful.
| Thank You
| DS

No, didn't work. Is there anyway to have the calculated field just grab
Click to expand...
the records that are greater than 0, and not null?
Thanks
DS
 
D

DebbieG

One more shot ...

=nnz(DSum("[Mod Price]", "[Mods Detail Query]", "[Order ID]= " & [Order ID]))


| DebbieG wrote:
|
| > I have this function in a module:
| >
| > Public Function nnz(TestValue As Variant) As Variant
| > If Not (IsNumeric(TestValue)) Then
| > nnz = 0
| > Else
| > nnz = TestValue
| > End If
| > End Function
| >
| >
| > =DSum("nnz([Mod Price])", "[Mods Detail Query]", "[Order ID]= " & [Order
ID])
| >
| > HTH,
| > Debbie
| >
| >
| > | > | Van T. Dinh wrote:
| > | > Try:
| > | >
| > | > =NZ(DSum("[Mod Price]", "[Mods Detail Query]",
| > | > "[Order ID]= " & [Order ID]), 0)
| > | >
| > | > (I am guessing you have a TextBox Control [Order ID] on the Form. If it
| > | > doesn't work, you have to explain where the value of [Order ID] comes
from.)
| > | >
| > | I found the problem....but not the answer. The problem is...
| > | On the first Subform I have records,
| > | On the second Subform I have records that are associated with the first
| > | Subform. On each, in the footer I have an unbound textbox that totals
| > | the dollar amounts. It all works fine if the second Subform always has
| > | a record in it. But if the second Subform doesn't have a record in it
| > | the unbound textbox on the second form gives me nothing. I also found
| > | that I needed an IIF statement instead of the NZ, so I'm using
| > | this...instead.
| > | =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| > | ID]=[Order ID]))
| > |
| > | Any suggestions would be most helpful.
| > | Thank You
| > | DS
| >
| > No, didn't work. Is there anyway to have the calculated field just grab
| the records that are greater than 0, and not null?
| Thanks
| DS
 
D

DS

DebbieG said:
One more shot ...

=nnz(DSum("[Mod Price]", "[Mods Detail Query]", "[Order ID]= " & [Order ID]))


| DebbieG wrote:
|
| > I have this function in a module:
| >
| > Public Function nnz(TestValue As Variant) As Variant
| > If Not (IsNumeric(TestValue)) Then
| > nnz = 0
| > Else
| > nnz = TestValue
| > End If
| > End Function
| >
| >
| > =DSum("nnz([Mod Price])", "[Mods Detail Query]", "[Order ID]= " & [Order
ID])
| >
| > HTH,
| > Debbie
| >
| >
| > | > | Van T. Dinh wrote:
| > | > Try:
| > | >
| > | > =NZ(DSum("[Mod Price]", "[Mods Detail Query]",
| > | > "[Order ID]= " & [Order ID]), 0)
| > | >
| > | > (I am guessing you have a TextBox Control [Order ID] on the Form. If it
| > | > doesn't work, you have to explain where the value of [Order ID] comes
from.)
| > | >
| > | I found the problem....but not the answer. The problem is...
| > | On the first Subform I have records,
| > | On the second Subform I have records that are associated with the first
| > | Subform. On each, in the footer I have an unbound textbox that totals
| > | the dollar amounts. It all works fine if the second Subform always has
| > | a record in it. But if the second Subform doesn't have a record in it
| > | the unbound textbox on the second form gives me nothing. I also found
| > | that I needed an IIF statement instead of the NZ, so I'm using
| > | this...instead.
| > | =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| > | ID]=[Order ID]))
| > |
| > | Any suggestions would be most helpful.
| > | Thank You
| > | DS
| >
| > No, didn't work. Is there anyway to have the calculated field just grab
| the records that are greater than 0, and not null?
| Thanks
| DS
Click to expand...
Nope, not working..... I'm miffed again! There has got to be a way to
get the records!!!
Thanks for trying!
DS
 
D

DebbieG

Is =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
ID]=[Order ID])) giving you what you want?

If no record satisfies the criteria argument or if domain contains no records,
the DSum function returns a Null.

Is the second form based on "Mods Detail Query"?

Is [Order ID] and [Mod Price] on your second form?



| Van T. Dinh wrote:
| > Try:
| >
| > =NZ(DSum("[Mod Price]", "[Mods Detail Query]",
| > "[Order ID]= " & [Order ID]), 0)
| >
| > (I am guessing you have a TextBox Control [Order ID] on the Form. If it
| > doesn't work, you have to explain where the value of [Order ID] comes from.)
| >
| I found the problem....but not the answer. The problem is...
| On the first Subform I have records,
| On the second Subform I have records that are associated with the first
| Subform. On each, in the footer I have an unbound textbox that totals
| the dollar amounts. It all works fine if the second Subform always has
| a record in it. But if the second Subform doesn't have a record in it
| the unbound textbox on the second form gives me nothing. I also found
| that I needed an IIF statement instead of the NZ, so I'm using
| this...instead.
| =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| ID]=[Order ID]))
|
| Any suggestions would be most helpful.
| Thank You
| DS
 
K

Ken Snell [MVP]

Not sure that I'm understanding, but assuming that Mod Price is the textbox
on the second subform:

=IIf(IsError([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
ID]=[Order ID]))

--

Ken Snell
<MS ACCESS MVP>

DS said:
DebbieG said:
One more shot ...

=nnz(DSum("[Mod Price]", "[Mods Detail Query]", "[Order ID]= " & [Order
ID]))


| DebbieG wrote:
|
| > I have this function in a module:
| >
| > Public Function nnz(TestValue As Variant) As Variant
| > If Not (IsNumeric(TestValue)) Then
| > nnz = 0
| > Else
| > nnz = TestValue
| > End If
| > End Function
| >
| >
| > =DSum("nnz([Mod Price])", "[Mods Detail Query]", "[Order ID]= " &
[Order ID])
| >
| > HTH,
| > Debbie
| >
| >
| > | > | Van T. Dinh wrote:
| > | > Try:
| > | >
| > | > =NZ(DSum("[Mod Price]", "[Mods Detail Query]",
| > | > "[Order ID]= " & [Order ID]), 0)
| > | >
| > | > (I am guessing you have a TextBox Control [Order ID] on the Form.
If it
| > | > doesn't work, you have to explain where the value of [Order ID]
comes from.)
| > | >
| > | I found the problem....but not the answer. The problem is...
| > | On the first Subform I have records,
| > | On the second Subform I have records that are associated with the
first
| > | Subform. On each, in the footer I have an unbound textbox that
totals
| > | the dollar amounts. It all works fine if the second Subform always
has
| > | a record in it. But if the second Subform doesn't have a record in
it
| > | the unbound textbox on the second form gives me nothing. I also
found
| > | that I needed an IIF statement instead of the NZ, so I'm using
| > | this...instead.
| > | =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail
Query",[Order
| > | ID]=[Order ID]))
| > |
| > | Any suggestions would be most helpful.
| > | Thank You
| > | DS
| >
| > No, didn't work. Is there anyway to have the calculated field just
grab
| the records that are greater than 0, and not null?
| Thanks
| DS
Click to expand...
Nope, not working..... I'm miffed again! There has got to be a way to get
the records!!!
Thanks for trying!
DS
Click to expand...
 
V

Van T. Dinh

DS

I am not sure from your posts.

If you look in Access VB Help, all 3 arguments of DSUM are Strings (3rd
argument is optional). In the Dsum expression you posted, 2 out of 3 are
not even Strings.

Perhaps, you re-typed incorrectly on the post. It may be better to use Copy
& Paste. This way, we know exactly what you used in your database.

In the 3rd argument of your DSum expression (and assuming that we have
sorted out the String problem above), the 2 instances of "[Order ID]" refer
to 2 different things. The one on the left refers to the Field in your
Query "[Mods Detail Query]". The one on the right refers to a specific
value (in a Control?). Can you explain where this value comes from, plse?
 
D

DS

Van said:
DS

I am not sure from your posts.

If you look in Access VB Help, all 3 arguments of DSUM are Strings (3rd
argument is optional). In the Dsum expression you posted, 2 out of 3 are
not even Strings.

Perhaps, you re-typed incorrectly on the post. It may be better to use Copy
& Paste. This way, we know exactly what you used in your database.

In the 3rd argument of your DSum expression (and assuming that we have
sorted out the String problem above), the 2 instances of "[Order ID]" refer
to 2 different things. The one on the left refers to the Field in your
Query "[Mods Detail Query]". The one on the right refers to a specific
value (in a Control?). Can you explain where this value comes from, plse?
Click to expand...
Thanks for the Posts, wold of answered sooner but after 14 hours, I need
some sleep!

=IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
ID]=[Order ID]))

The Main Form "ORDERS" (Orders) Table
Order ID

First Subform "ORDER DETAILS" (Order Detail Query) Query
Order ID
Line ID
Product ID
Product Name
Retail Price

Second Subform "MODS DETAIL" (Mods Detail Query) Query
Order ID
Line ID
Mod ID
Mod Name
Mod Price

Above, is how its set-up.

If every record in Subform 1 has records in Subform 2, the textbox with
the DSum expression in it works fine.

If every record in Subform 1 has no records in Subform 2, the textbox
with the DSum expression in it works fine.

Its only when the records in Subform 1 has both records and no records,
that the textbox with the DSum expression doesn't work.

I hope this is clearer!
Thanks
DS
 
D

DebbieG

| Van T. Dinh wrote:
| > DS
| >
| > I am not sure from your posts.
| >
| > If you look in Access VB Help, all 3 arguments of DSUM are Strings (3rd
| > argument is optional). In the Dsum expression you posted, 2 out of 3 are
| > not even Strings.
| >
| > Perhaps, you re-typed incorrectly on the post. It may be better to use Copy
| > & Paste. This way, we know exactly what you used in your database.
| >
| > In the 3rd argument of your DSum expression (and assuming that we have
| > sorted out the String problem above), the 2 instances of "[Order ID]" refer
| > to 2 different things. The one on the left refers to the Field in your
| > Query "[Mods Detail Query]". The one on the right refers to a specific
| > value (in a Control?). Can you explain where this value comes from, plse?
| >
| Thanks for the Posts, wold of answered sooner but after 14 hours, I need
| some sleep!
|
| =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| ID]=[Order ID]))

I don't know how your DSum is working -- the syntax is incorrect. If you look
at DSum in Help, here is an example:

DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " _
& [ProductID])Yours should look like this:=IIf(IsNull([Mod
Price]),0,DSum("[Mod Price]","Mods Detail Query","[Order ID] = " & [Order ID]))|
| The Main Form "ORDERS" (Orders) Table| Order IDIs the Main Form displaying one
Order ID or multiple Order ID's?Are you totalling anything in the footer of this
form?| | First Subform "ORDER DETAILS" (Order Detail Query) Query| Order ID|
Line ID| Product ID| Product Name| Retail PriceIs it displaying one Order ID or
multiple Order ID's?In the footer of this subform are you totalling the Retail
Price?If it is displaying one Order ID, you should be able to just Sum the
Retail Price ... =Sum([Retail Price]) and make the Default Value = 0.| | Second
Subform "MODS DETAIL" (Mods Detail Query) Query| Order ID| Line ID| Mod ID| Mod
Name| Mod PriceIs this subform displaying one Order ID or multiple Order ID's?If
it is displaying one Order ID, you should be able to just Sum the Mod Price ...
=Sum([Mod Price]) and make the Default Value = 0.| | Above, is how its set-up.|
| If every record in Subform 1 has records in Subform 2, the textbox with | the
DSum expression in it works fine.| | If every record in Subform 1 has no records
in Subform 2, the textbox | with the DSum expression in it works fine.| | Its
only when the records in Subform 1 has both records and no records, | that the
textbox with the DSum expression doesn't work.How can Subform 1 have both
records and no records?| | I hope this is clearer!| Thanks| DS
 
D

DebbieG

My other message displayed very ugly ... I'll try again ...

I don't know how your DSum is working -- the syntax is incorrect. If you look
at DSum in Help, here is an example:

DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " _
& [ProductID])

Yours should look like this:
=IIf(IsNull([Mod Price]),0,DSum("[Mod Price]","Mods Detail Query","[Order ID] =
" & [Order ID]))

Is the Main Form displaying one Order ID or multiple Order ID's?
Are you totalling anything in the footer of this form?

Is Subform 1 displaying one Order ID or multiple Order ID's?
In the footer of this subform are you totalling the Retail Price?
If it is displaying one Order ID, you should be able to just Sum the Retail
Price ...
=Sum([Retail Price]) and make the Default Value = 0.

Is Subform 2 displaying one Order ID or multiple Order ID's?
If it is displaying one Order ID, you should be able to just Sum the Mod Price
....
=Sum([Mod Price]) and make the Default Value = 0.

How can Subform 1 have both records and no records?


| Van T. Dinh wrote:
| > DS
| >
| > I am not sure from your posts.
| >
| > If you look in Access VB Help, all 3 arguments of DSUM are Strings (3rd
| > argument is optional). In the Dsum expression you posted, 2 out of 3 are
| > not even Strings.
| >
| > Perhaps, you re-typed incorrectly on the post. It may be better to use Copy
| > & Paste. This way, we know exactly what you used in your database.
| >
| > In the 3rd argument of your DSum expression (and assuming that we have
| > sorted out the String problem above), the 2 instances of "[Order ID]" refer
| > to 2 different things. The one on the left refers to the Field in your
| > Query "[Mods Detail Query]". The one on the right refers to a specific
| > value (in a Control?). Can you explain where this value comes from, plse?
| >
| Thanks for the Posts, wold of answered sooner but after 14 hours, I need
| some sleep!
|
| =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| ID]=[Order ID]))
|
| The Main Form "ORDERS" (Orders) Table
| Order ID
|
| First Subform "ORDER DETAILS" (Order Detail Query) Query
| Order ID
| Line ID
| Product ID
| Product Name
| Retail Price
|
| Second Subform "MODS DETAIL" (Mods Detail Query) Query
| Order ID
| Line ID
| Mod ID
| Mod Name
| Mod Price
|
| Above, is how its set-up.
|
| If every record in Subform 1 has records in Subform 2, the textbox with
| the DSum expression in it works fine.
|
| If every record in Subform 1 has no records in Subform 2, the textbox
| with the DSum expression in it works fine.
|
| Its only when the records in Subform 1 has both records and no records,
| that the textbox with the DSum expression doesn't work.
|
| I hope this is clearer!
| Thanks
| DS
 
D

DS

DebbieG said:
My other message displayed very ugly ... I'll try again ...

I don't know how your DSum is working -- the syntax is incorrect. If you look
at DSum in Help, here is an example:

DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " _
& [ProductID])

Yours should look like this:
=IIf(IsNull([Mod Price]),0,DSum("[Mod Price]","Mods Detail Query","[Order ID] =
" & [Order ID]))

Is the Main Form displaying one Order ID or multiple Order ID's? Its showin one ID
Are you totalling anything in the footer of this form?
Click to expand...
Yes, the totalof the two Subforms
Is Subform 1 displaying one Order ID or multiple Order ID's? Multiple IDs
In the footer of this subform are you totalling the Retail Price? Yes
If it is displaying one Order ID, you should be able to just Sum the Retail
Price ...
=Sum([Retail Price]) and make the Default Value = 0. Yes, this works fine.

Is Subform 2 displaying one Order ID or multiple Order ID's?
Click to expand...
Multiple Order IDS, One for each record
If it is displaying one Order ID, you should be able to just Sum the Mod Price
...
=Sum([Mod Price]) and make the Default Value = 0.

How can Subform 1 have both records and no records?
Click to expand...
Subform 1 always has a record, Subform 2 sometimes has one
sometimes not.
Main Form is Order ID
Subform 1 is the Product....Burger
Subform 2 is the Modifier....Rare..etc.

I put he DSum code in that you wrote...its much cleaner.
Thanks...Ifor your help, I'm going crazy trying to figure this out!!!
DS
| Van T. Dinh wrote:
| > DS
| >
| > I am not sure from your posts.
| >
| > If you look in Access VB Help, all 3 arguments of DSUM are Strings (3rd
| > argument is optional). In the Dsum expression you posted, 2 out of 3 are
| > not even Strings.
| >
| > Perhaps, you re-typed incorrectly on the post. It may be better to use Copy
| > & Paste. This way, we know exactly what you used in your database.
| >
| > In the 3rd argument of your DSum expression (and assuming that we have
| > sorted out the String problem above), the 2 instances of "[Order ID]" refer
| > to 2 different things. The one on the left refers to the Field in your
| > Query "[Mods Detail Query]". The one on the right refers to a specific
| > value (in a Control?). Can you explain where this value comes from, plse?
| >
| Thanks for the Posts, wold of answered sooner but after 14 hours, I need
| some sleep!
|
| =IIf(IsNull([Mod Price]),0,DSum([Mod Price],"Mods Detail Query",[Order
| ID]=[Order ID]))
|
| The Main Form "ORDERS" (Orders) Table
| Order ID
|
| First Subform "ORDER DETAILS" (Order Detail Query) Query
| Order ID
| Line ID
| Product ID
| Product Name
| Retail Price
|
| Second Subform "MODS DETAIL" (Mods Detail Query) Query
| Order ID
| Line ID
| Mod ID
| Mod Name
| Mod Price
|
| Above, is how its set-up.
|
| If every record in Subform 1 has records in Subform 2, the textbox with
| the DSum expression in it works fine.
|
| If every record in Subform 1 has no records in Subform 2, the textbox
| with the DSum expression in it works fine.
|
| Its only when the records in Subform 1 has both records and no records,
| that the textbox with the DSum expression doesn't work.
|
| I hope this is clearer!
| Thanks
| DS
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NZ #ERROR Working 2
Access #Error & Excel Pivot 0
cannot use nz function 5
Nz function not working 9
Aggregate function error 5
Error on Empty Table 3
DLookup and Nz 0
DSUM Error 3

Top