Concatenating Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an calculated field that is supposed to be adding three fields
together, however, they are being concatenated instead.

The fields looks something like this:

Room_Rental: IIf(nz([ACTRENTAL])=0,NZ([RENTAL]),NZ([ACTRENTAL]))

and the calculated field is like this:

Total_Revenue:
(nz([Food])+nz([Beverage])+nz([Other])+nz([Res])+nz([Room_Rental]))

The query result for a particular record would look like this:

60005000100

where Food=6000, Beverage=500, Other=0, and Res=100.

I think I am having problems with my data types but don't know how to
resolve them.
 
Try:

(nz([Food], 0)+nz([Beverage], 0)+nz([Other], 0)+nz([Res],
0)+nz([Room_Rental], 0))

Check Access Help on the Nz() function.
 
This suggests that one, several, or all, of the 5 fields [Food],
[Beverage], [Other], [Res], and [Room_Rental], are of a Text data type.
If so, the whole expression will be coerced into a Text expression, not
a Numeric one, and the "+"s will effectively work like string append
operators (&), giving the result that you see.

If you expect all those 5 fields to add like numbers, you need to
ensure that they are indeed, Numeric type fields. If any are text type
fields, you could certainly use the Val() function on those fields, to
convert them to numbers. But if they are truly numbers, then, they
should be in Numeric fields to begin with!

HTH,
TC [MVP Access]
 
Thanks. I thought I had tried that already, but I guess something went wrong.

Van T. Dinh said:
Try:

(nz([Food], 0)+nz([Beverage], 0)+nz([Other], 0)+nz([Res],
0)+nz([Room_Rental], 0))

Check Access Help on the Nz() function.

--
HTH
Van T. Dinh
MVP (Access)



Steven Cheng said:
I have an calculated field that is supposed to be adding three fields
together, however, they are being concatenated instead.

The fields looks something like this:

Room_Rental: IIf(nz([ACTRENTAL])=0,NZ([RENTAL]),NZ([ACTRENTAL]))

and the calculated field is like this:

Total_Revenue:
(nz([Food])+nz([Beverage])+nz([Other])+nz([Res])+nz([Room_Rental]))

The query result for a particular record would look like this:

60005000100

where Food=6000, Beverage=500, Other=0, and Res=100.

I think I am having problems with my data types but don't know how to
resolve them.
Click to expand...
Click to expand...
 
Nz() will not help you, IMHO. Your problem is the type of the fields,
not the fact that they can be Null.

HTH,
TC [MVP Access]
 
Back
Top