IsNull Function Proper Use in CrossTab Query 1

[samn265]

I have the following function (see below)that runs a cross tab query which produces 1 record. However, sometimes, the query has no records to produce. At that time I get run-time Error.

I tried to use the IsNull function, to solve this problem, but I guess I am using it wrong. Would anyone tell me the correct format of using IsNull in the following function:

Public Function TotalUnexcusedIncidents() As Integer
TotalUnexcusedIncidents = DLookup("[TotalUnExcused]", "qryCrosstab30DayIntervalTest1&quot
MsgBox "TOTAL UNEXCUSED: " & [TotalUnexcusedIncidents]
End Function


Thanks….. Sam

 

[KyleS]

Hi Sam,
Try this:

Public Function TotalUnexcusedIncidents() As Integer
IF IsNull(DLookup("[TotalUnExcused]", "qryCrosstab30DayIntervalTest1&quot Then
TotalUnexcusedIncidents = 0
Else
TotalUnexcusedIncidents = DLookup("[TotalUnExcused]", "qryCrosstab30DayIntervalTest1&quot
End If
MsgBox "TOTAL UNEXCUSED: " & [TotalUnexcusedIncidents]
End Function Kyle :

 

[samn265]

Kyle,
I am still getting the same error that I got before using similar code as you have suggested. The error is as follow: Run Time error: The Microsoft Jet database does not recognize '[TotalUnExcused]' as valid field name or expression.

I get this error only when the cross tab query produces no record.
Any other suggestions?!

 

[KyleS]

Wow, brain fart over here, sorry about that. The column heading don't show up if there aren't any recods since there aren't any fields to the records.

So you could do two things, you could change it to this:

Public Function TotalUnexcusedIncidents() As Integer
IF IsError(DLookup("[TotalUnExcused]", "qryCrosstab30DayIntervalTest1&quot Then
TotalUnexcusedIncidents = 0
Else
TotalUnexcusedIncidents = DLookup("[TotalUnExcused]", "qryCrosstab30DayIntervalTest1&quot
End If
MsgBox "TOTAL UNEXCUSED: " & [TotalUnexcusedIncidents]
End Function

Which will get the error and then go about it's merry way, or you could create a recordset off the query and check the recordcount which would be 0 (or check for .EOF). It may be a bit unorthadox, but I would use the code above as it's alot smaller and easier to change if you need to.

Let me know if you want to do it the long way... Kyle :

 

[samn265]

Kyle,
I am sorry but I am still getting the same error even when I use the IsError.
Any suggestions?

 

[KyleS]

Wow, OK,
Well then onto the slightly longer way I suppose...

Public Function TotalUnexcusedIncidents() As Integer
Dim rstError as DAO.Recordset
Set rstError = CurrentDB.OpenRecordset("qryCrosstab30DayIntervalTest1&quot

If rstError.EOF Then
TotalUnexcusedIncidents = 0
Else
TotalUnexcusedIncidents = rstError!TotalUnExcused
End If

rstError.Close
Set rstError = Nothing
End Function

OK, sorry for the confusion... Kyle :

 

[samn265]

Kyle,
It worked. You are a great help. Thank you very much. I don't know what all these lines mean but it worked. Thanks again. I spent a lot of times trying to get rid of the error that I mentioned to you early but with no success.
I owe you one.

Thanks,
Sam

 

[KyleS]

Glad to help,
Here, let's go through this line by line and I'll explain what this is doing..


Public Function TotalUnexcusedIncidents() As Integer
This below is Dimensioning a Recordset, which is very similar to an array of sorts. It's an electronic copy of a table or query that the database uses but you can't see
Dim rstError as DAO.Recordset
Here I'm just telling the DB to make the Recordset I dimensioned above = to your query, so now we have a copy of your query that we can sort through
Set rstError = CurrentDB.OpenRecordset("qryCrosstab30DayIntervalTest1&quot
.EOF means "End Of File", so if the Recordset starts out at the End Of File then there are no records there
If rstError.EOF Then
TotalUnexcusedIncidents = 0
Else
In the case that there is a record, [RECORDSETNAME]![FIELDNAME] will give you the value in that specific field for the record you are currently on, and since you're only getting one field in the query, we don't need to move or anything since recordsets automatically start at the first record
TotalUnexcusedIncidents = rstError!TotalUnExcused
End If
You need to be sure to close the recordset and set the variable = nothing so it doesn't take up any memory anymore
rstError.Close
Set rstError = Nothing
End Function

I hope my descriptions helped... Kyle :

 

[samn265]

Thanks again for the explaination. You are a very helpful person.
Sam