Query returning "error#" instead of zero

[noname]

I have a query that subtracts this months mileage from last months, then
divides that mileage value by the fuel used by the vehicle to figure this
months miles per gallon.
Whenever one of the values is 0, it returns "error#".
Do I use Nz function to fix this?
Here is the query

SELECT [Monthly mileage log 2004].AssetID, [JanMiles]-[Monthly mileage log
2004]![YearBeginingMileage] AS JanMilesDriven, [JanMilesDriven]/[Monthly
mileage log 2004]![JanFuel] AS JanMpg, [Monthly mileage log
2004]![FebMiles]-[Monthly mileage log 2004]![JanMiles] AS FebMilesDriven,
[FebMilesDriven]/[Monthly mileage log 2004]![FebFuel] AS FebMpg, [Monthly
mileage log 2004]![MarMiles]-[Monthly mileage log 2004]![FebMiles] AS
MarMilesDriven, [MarMilesDriven]/[MarFuel] AS MarMpg, [AprMiles]-[MarMiles]
AS AprMilesDriven, [AprMilesDriven]/[AprFuel] AS AprMpg, [Monthly mileage
log 2004]![MayMiles]-[Monthly mileage log 2004]![AprMiles] AS
MayMilesDriven, [MayMilesDriven]/[Monthly mileage log 2004]![MayFuel] AS
MayMpg, [Monthly mileage log 2004]![JunMiles]-[Monthly mileage log
2004]![MayMiles] AS JunMilesDriven, [JunMilesDriven]/[Monthly mileage log
2004]![JunFuel] AS JunMpg, [Monthly mileage log 2004]![JulMiles]-[Monthly
mileage log 2004]![JunMiles] AS JulMilesDriven, [JulMilesDriven]/[Monthly
mileage log 2004]![JulFuel] AS JulMpg, [Monthly mileage log
2004]![AugMiles]-[Monthly mileage log 2004]![JulMiles] AS AugMilesDriven,
[AugMilesDriven]/[Monthly mileage log 2004]![AugFuel] AS AugMpg, [Monthly
mileage log 2004]![SepMiles]-[Monthly mileage log 2004]![AugMiles] AS
SepMilesDriven, [SepMilesDriven]/[Monthly mileage log 2004]![SepFuel] AS
SepMpg, [Monthly mileage log 2004]![OctMiles]-[Monthly mileage log
2004]![SepMiles] AS OctMilesDriven, [OctMilesDriven]/[Monthly mileage log
2004]![OctFuel] AS OctMpg, [Monthly mileage log 2004]![NovMiles]-[Monthly
mileage log 2004]![OctMiles] AS NovMilesDriven, [NovMilesDriven]/[Monthly
mileage log 2004]![NovFuel] AS NovMpg, [Monthly mileage log
2004]![DecMiles]-[Monthly mileage log 2004]![NovMiles] AS DecMilesDriven,
[DecMilesDriven]/[Monthly mileage log 2004]![DecFuel] AS DecMpg,
([JanMpg]+[FebMpg]+[MarMpg]+[AprMpg]+[MayMpg]+[JunMpg]+[JulMpg]+[AugMpg]+[Se
pMpg]+[OctMpg]+[NovMpg]+[DecMpg])/12 AS MpgYearAvg
FROM [Monthly mileage log 2004];

[Tim Marshall]

noname wrote:

I have a query that subtracts this months mileage from last months, then
divides that mileage value by the fuel used by the vehicle to figure this
months miles per gallon.
Whenever one of the values is 0, it returns "error#".

Well, do you have any zero values in the denominators, such as the fuel
values?

Your structure is very, very poor, you might as well just be using a
spreadsheet. Much better to have one table (it looks as if you have a
tble per year - bad, bad design), and not worry about months:

TBL_VEHICLE_LOG (or something)

VL_PK autonumber
VL_TRANS_DATE date 'transaction date
VL_MILES numeric 'make sure default value is 0
VL_FUEL numeric 'make sure default value is 0

You can then easily get monthly, yearly figures using month(), year()
and if you have a fiscal year system that is other than 1 Jan you can
write functions to get what you want.

Sorry if I sound like I'm being mean, but if someone came to me with a
data structure like that (and I run maintenance systems for vehicles) I
would throw him/her out of my office angrily.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Tim Marshall wrote:

TBL_VEHICLE_LOG (or something)

VL_PK autonumber
VL_TRANS_DATE date 'transaction date
VL_MILES numeric 'make sure default value is 0
VL_FUEL numeric 'make sure default value is 0

And of course, you'd want a link to a SEPARATE vehicle table.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me