calculating currency formatted fields

[apex77]

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?

 

[Jerry Whittle]

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.

 

[John Spencer]

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]

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")

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.

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?

 

[John Spencer]

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

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

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?

 

[apex77]

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'

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

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?

 

[apex77]

BRILLIANT!! John Spencer, Once again you have done it!

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

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

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?

 

[Jerry Whittle]

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.

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")

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.

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?