forcing null values to act as a zero

[rookiedev]

I HAVE A CROSS TAB QUERY THAT SHOWS ME THE NUMBER OF CALLS THAT CAME IN FOR EACH AREA

CompanyName Total 6100 6200 6301
Galaxy 71 2 0 3
GOQ 111 5 0 26
Mobil Home Park 4 0 0 4


I FORCED THE 0 TO DISPLAY USING @;"0" HOWEVER I THEN TRIED TO USE THE VALUES IN A REPORT AS A VALUE IN A CALCULATED FIELD AND IT WOULD NOT SEE THE VALUE AS A ZERO THEREFORE THE CALCULATION GAVE ME AN ERROR MESSAGE. FURTHER INVESTIGATION MADE ME REALIZE THAT I WAS USING A TEXT FORMAT AND THAT THE ACTUAL VALUE OF ZERO WAS NOT BEING STORED. CAN ANYONE ENLIGHTEN ME AS TO HOW I CAN MAKE A ZERO VALUE DISPLAY IN MY REPORT SO I CAN CALCULTE USING THAT VALUE?

ROOKIEDEV

 

[Uriel365]

Try looking at the Nz funtion in Access Help

 

[NewfieGolfer]

ROOKIEDEV,

maybe you could use the following for the criteria in the query design.

iif(isnull([fieldname]), 0,[fieldname])

fieldname is the name of the specific field criteria you are placing this code into.

Hope this helps,
NG

ps. i'm not too familiar with Cross Tab Queries, this works with select queries though.

 

[rookiedev]

Thanks- I'll give both suggestions a try and let you know how I fare. Right now what I am trying to do is simply multipy this value by $5493.235 and then for each of the subtotals I will have to add them for a total contractor cost. I wrote that string as nz[6100Subtotal]+nz[6200Subtotal]+..........of course I'm not sure if it is going to work properly until I try your suggestions.

Rookiedev

 

[rookiedev]

I tried to put the iff statement in the criteria field in the crosstab query and got an error message that says"You can't specify criteria on the same field for which you entered "Value" in the crosstab row. The nz function in the report does not seem to work out either. Any suggestions as to what I am doing wrong?

Rookiedev

 

[SeeThru]

Cross tabs are a pain because of stuff like that. You could try basing another query on the cross-tab, or basing the crosstab on a query that uses NZ(0) but I havent really tried either.