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
|
|||
|
|||
calculating currency formatted fields
I have a query in which I am trying to total up 3 currency formatted fields.
The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#2
|
|||
|
|||
calculating currency formatted fields
Are they truely currency field or text fields with something that looks like
currency in them? Debug.Print "$449.01" + "$525.00" + "$250.50" = $449.01$525.00$250.50 Whereas : Debug.Print 449.01 + 525.00 + 250.50 1224.51 Even if they are text fields, getting rid of the $ symbol could help: Debug.Print Val("449.01") + Val("525.00") + Val("250.50") = 1224.51 Try dumping the $ sign. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apex77" wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#3
|
|||
|
|||
calculating currency formatted fields
You are adding strings. Try
Val(Lead) + Val(Advance) + Val(Close) If any of those three are null then you will need to add the NZ function into the calculation Val(Nz(Lead,0)) + Val(Nz(Advance)) + Val(Nz(Close)) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County apex77 wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#4
|
|||
|
|||
calculating currency formatted fields
Jerry,
Thanks for the fast reply. Actually the the 3 fields are as follows: Lead: Format(nz([qryTotalLeadMTD].[Total Lead],0),"Currency") Advance: Format(nz([qryTotalAdvanceMTD].[Total Advance],0),"Currency") Close: Format(nz([qryTotalCloseMTD].[Total Close],0),"Currency") "Jerry Whittle" wrote: Are they truely currency field or text fields with something that looks like currency in them? Debug.Print "$449.01" + "$525.00" + "$250.50" = $449.01$525.00$250.50 Whereas : Debug.Print 449.01 + 525.00 + 250.50 1224.51 Even if they are text fields, getting rid of the $ symbol could help: Debug.Print Val("449.01") + Val("525.00") + Val("250.50") = 1224.51 Try dumping the $ sign. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apex77" wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#5
|
|||
|
|||
calculating currency formatted fields
Whoops!
Val("$124.35") returns 0 So try: IIF(IsNumeric(Lead),CCur(Lead),0) + IIF(IsNumeric(Advance),CCur(Advance),0) + IIF(IsNumeric(Close),CCur(Close),0) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John Spencer wrote: You are adding strings. Try Val(Lead) + Val(Advance) + Val(Close) If any of those three are null then you will need to add the NZ function into the calculation Val(Nz(Lead,0)) + Val(Nz(Advance)) + Val(Nz(Close)) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County apex77 wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#6
|
|||
|
|||
calculating currency formatted fields
Total: Val([Lead])+Val([Advance])+Val([Close]) returns only '0' when one of
the 3 fields contains '0' Val(Nz(Lead,0)) + Val(Nz(Advance)) + Val(Nz(Close)) also returns only '0' when one of the 3 fields contains '0' "John Spencer" wrote: You are adding strings. Try Val(Lead) + Val(Advance) + Val(Close) If any of those three are null then you will need to add the NZ function into the calculation Val(Nz(Lead,0)) + Val(Nz(Advance)) + Val(Nz(Close)) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County apex77 wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#7
|
|||
|
|||
calculating currency formatted fields
BRILLIANT!! John Spencer, Once again you have done it!
"John Spencer" wrote: Whoops! Val("$124.35") returns 0 So try: IIF(IsNumeric(Lead),CCur(Lead),0) + IIF(IsNumeric(Advance),CCur(Advance),0) + IIF(IsNumeric(Close),CCur(Close),0) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John Spencer wrote: You are adding strings. Try Val(Lead) + Val(Advance) + Val(Close) If any of those three are null then you will need to add the NZ function into the calculation Val(Nz(Lead,0)) + Val(Nz(Advance)) + Val(Nz(Close)) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County apex77 wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
#8
|
|||
|
|||
calculating currency formatted fields
The Format function returns a string. Therefore you aren't adding numbers.
You're adding ABC's. Try dumping the format function. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apex77" wrote: Jerry, Thanks for the fast reply. Actually the the 3 fields are as follows: Lead: Format(nz([qryTotalLeadMTD].[Total Lead],0),"Currency") Advance: Format(nz([qryTotalAdvanceMTD].[Total Advance],0),"Currency") Close: Format(nz([qryTotalCloseMTD].[Total Close],0),"Currency") "Jerry Whittle" wrote: Are they truely currency field or text fields with something that looks like currency in them? Debug.Print "$449.01" + "$525.00" + "$250.50" = $449.01$525.00$250.50 Whereas : Debug.Print 449.01 + 525.00 + 250.50 1224.51 Even if they are text fields, getting rid of the $ symbol could help: Debug.Print Val("449.01") + Val("525.00") + Val("250.50") = 1224.51 Try dumping the $ sign. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apex77" wrote: I have a query in which I am trying to total up 3 currency formatted fields. The 3 fields are Lead, Advance and Close. The fourth column with the calculation is written as: Total: [Lead]+[Advance]+[Close] Instead of returning a total of the three fields it is returning the 3 values pressed together like the following: $449.01$525.00$250.50 Any ideas? |
Thread Tools | |
Display Modes | |
|
|