Handling Null Values

[illy2k]

I setup a quesry which totals a certain type of sales. Though sometimes this query will be null, meaning in the specific date range specified, that there are no values. When this happens, the report errors out, in which all boxes contain #Error. Is there anyway to specify that if the quesry isNull that I can give it some value?

 

[maxmangion]

actually a null value is usually considered as data 'not known' at the time of you data input. therefore this means that such fields are not considered as zero. however, if you would like to make such fields appear as 0 (when they are null) considering that the datatype is numeric, you can make the following in the after update event:

If IsNull(Me.Fieldname) Then
Me.Fieldname = 0
end if

 

[Mile-O]

The best function for use within a query when dealing with Null Values is the Nz() function - its name is made from Null to Zero.

It doesn't just convert Null values to 0 but you can change a Null value to anything you want.

MyField: Nz([Cost])

MyField: Nz([Surname], "Unknown")

 

[Rich]

Why not just use the NoData event for the Report?