Such an easy sum not working in Query

CSP12345

Board Regular
Joined
Oct 1, 2014
Messages
52
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,971
[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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
Val() is a tricky function - it doesn't understand dollar signs:
Code:
?val("$1.00")
 0

Try instead CCur() which means "Cast to Currency" or "Convert to Currency":
Code:
?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

Board Regular
Joined
Oct 1, 2014
Messages
52
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,947
Members
416,144
Latest member
JohnMacDonald

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top