Such an easy sum not working in Query

[CSP12345]

Oh Man, this is crazy.
I just need to add 2 results from expressions into another record in the query and if I do a straight [Field1]+[Field2] I get the result of field1,field2. I have tried the var command in front as this

Total cost per visit: Val([Labour Cost])+Val([BatteryCost])

and get an answer of $0.00 for all instead of the total.

I can not change the format of Labour Cost and BatteryCost as they are expressions "Dlookup" and "iif"

So what else could I try.

Many thanks for any help as I am new to access and so used to excel formulas.

 

[ranman256]

[Labour Cost] and [BatteryCost] should already be numeric, so you dont need the VAL(). If not, then the field in the table NEEDS to change to currency.
BUT
if you have a null in the field, it will add to null, so try NZ([Labour Cost])+NZ([BatteryCost])
(it converts nulls to zero) but this doesnt seem to be your problem. Norm, there are no nulls in currency.

ALSO, dont use DLOOKUPS in queries. The query can do everything needed.

 

[xenou]

Val() is a tricky function - it doesn't understand dollar signs:

?val("$1.00")
 0

Try instead CCur() which means "Cast to Currency" or "Convert to Currency":

?CCur("$1.00")
 1

However, CCur will crash if there is non-numeric value (either null, empty string, or any other text that does not represent a number)

 

[CSP12345]

Hi guys thank you so much for your help. I had formatted the result to currency on the 2 fields required but the CCurl in the sum expression worked a treat.

I really appreciate you guys taking the time to help a Newb like me out on Access.