Writing an Expression in a Query

[ssworthi]

I have a detail query of all transactions for our company vehicles fuel usage. I then wrote a total query to give me totals by card which lists totals for gas and totals for diesel (separate fields). I am trying to write a sum to combine both gas & diesel for report purposes and am not doing something right; I am getting no data. I am hoping if the diesel field is empty the expression would result in what the gas amount was. Finally I tried this Expr1: Sum([Gas $ Amount]+[Diesel $ Amount]) and still no result. Should the fields be separated by ()? Thanks for any help.

 

[Brianwarnock]

If fields can be empty you are going to need to use NZ

sum(nz(gas,0)+nz(diesel,0))

 

[ssworthi]

Thank you so much, To confirm:
sum(nz([SumofGas $Amount],0)+nz(SumofDiesel $Amount],0))

Is there an "=" in front of sum? Thanks so much!

 

[ssworthi]

Worked like a charm! Wonderful; Thanks so much for your help!

 

[Brianwarnock]

Glad to be of assistance

Brian