|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
February 19th, 2005, 12:49 AM
|
|||
|
|||
NZ #ERROR
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 
|
|
#2
February 19th, 2005, 01:02 AM
|
|||
|
|||
|
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 "DS" wrote in message ... 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
|
|
#3
February 19th, 2005, 01:33 AM
|
|||
|
|||
|
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.) -- HTH Van T. Dinh MVP (Access) "DS" wrote in message ... 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
|
|
#4
February 19th, 2005, 02:04 AM
|
|||
|
|||
|
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
|
|
#5
February 19th, 2005, 02:05 AM
|
|||
|
|||
|
Ed Robichaud wrote:
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 "DS" wrote in message ... 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 Thanks ED, see the previous post...my problem is unfortunately much deeper than I thought. DS
|
|
#6
February 19th, 2005, 02:39 AM
|
|||
|
|||
|
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 "DS" wrote in message ... | 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
|
|
#7
February 19th, 2005, 02:58 AM
|
|||
|
|||
|
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 "DS" wrote in message ... | 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
|
|
#8
February 19th, 2005, 03:11 AM
|
|||
|
|||
|
One more shot ...
=nnz(DSum("[Mod Price]", "[Mods Detail Query]", "[Order ID]= " & [Order ID])) "DS" wrote in message ... | 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 | | | "DS" wrote in message | ... | | 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
|
|
#9
February 19th, 2005, 03:14 AM
|
|||
|
|||
|
DebbieG wrote:
One more shot ... =nnz(DSum("[Mod Price]", "[Mods Detail Query]", "[Order ID]= " & [Order ID])) "DS" wrote in message ... | 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 | | | "DS" wrote in message | ... | | 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 Nope, not working..... I'm miffed again! There has got to be a way to get the records!!! Thanks for trying! DS
|
|
#10
February 19th, 2005, 03:43 AM
|
|||
|
|||
|
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? "DS" wrote in message ... | 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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Backing Database | GOL | General Discussion | 4 | January 28th, 2005 03:32 PM |
| Error message different in MDB and MDE | david epsom dot com dot au | General Discussion | 1 | September 21st, 2004 12:47 AM |
| Help, i'm gettiing error: Access has encountered a problem and needs to close... | betsy | General Discussion | 0 | September 14th, 2004 08:20 PM |
| Expression - calculating running total | Kathy | Running & Setting Up Queries | 26 | June 22nd, 2004 10:14 PM |
| Custom Error Messages | DMc2004 | Database Design | 4 | June 11th, 2004 11:16 PM |
Linear Mode
