Dealing with null filed values

[JoeS01]

The following code works Ok, except where there are Null values in the Field. I then get a runtime error 94 - 'Invalid use of null'

I have added the following line to the offending code, but get another error message 424 'Object required':

And rsTempData!FlashTemp Is Not Null Then

Can anyone please suggest a workaround?

all suggestions and pointeres gratefully received.

Public Function funcTemp(strTxNumber As String) As Single

    Dim rsTempData As ADODB.Recordset
    Set rsTempData = New ADODB.Recordset

    strSQL1 = "SELECT Temp FROM qryLatestTemp WHERE PlantNumber = " & Chr(34) & strTxNumber & Chr(34)

    rsTempData.Open strSQL1, ThisDB, adOpenStatic, adLockReadOnly

    If Not rsTempData.EOF And Not rsTempData.BOF    <<<< ERROR HERE
        funcTemp = rsTempData!Temp
    Else
        funcTemp = -999
    End If

Set rsTempData = Nothing
End Function

 

[JoeS01]

Corrections:

1. Title should be "Dealing with null Field values"

2. Line showing 'Error' should have "Then" added at the end:
If Not rsTempData.EOF And Not rsTempData.BOF Then <<<< ERROR HERE

 

[Andrew Fergus]

Hi Joe

If you want to know if a table/query has any records that meet your criteria, then rather than using ADODB etc, you can get a record count using this line :

DCount("[Temp]", "qryLatestTemp", "[PlantNumber] = '" & strTxNumber & "'")

I tested this using my own table but modified the details to your table and field names so hopefully I have spelt everything correctly.

Similarly, to return the matching record if the above count is great than 0 then you can use a DLookUp function instead of the ADODB lines.

HTH, Andrew

 

[JoeS01]

Many thanks, Andrew - will try this out

 

[JoeS01]

Found Nz( field value) gave zero if the field is Null - just what I needed

Thanks to all for suggestions

 

[MNpoker]

Hi Joe

If you want to know if a table/query has any records that meet your criteria, then rather than using ADODB etc, you can get a record count using this line :

DCount("[Temp]", "qryLatestTemp", "[PlantNumber] = '" & strTxNumber & "'")

I tested this using my own table but modified the details to your table and field names so hopefully I have spelt everything correctly.

Similarly, to return the matching record if the above count is great than 0 then you can use a DLookUp function instead of the ADODB lines.

HTH, Andrew

Thanks for this. If I have a SQL Statement:

VtSQL = ""
VtSQL = VtSQL & "SELECT Qry_SecondaryAfterFilter.Peril_Number, Qry_SecondaryAfterFilter.Secondary_Number, Qry_SecondaryAfterFilter.Secondary_Field, Qry_calcTIVByLoc.TIVVALUE, Qry_calcTIVByLoc.TIVVALUE" & _
"FROM Qry_SecondaryAfterFilter INNER JOIN (dbo_hudet INNER JOIN Qry_calcTIVByLoc ON dbo_hudet.LOCID = Qry_calcTIVByLoc.LOCID) ON Qry_SecondaryAfterFilter.Peril_Number = Qry_calcTIVByLoc.PERIL" & _
"WHERE (((Qry_SecondaryAfterFilter.Secondary_Number)=" & X & "));"

Now I need a count of the Records. Can I get that easily?

Thanks.

 

[Andrew Fergus]

Hi

Make another query that counts the records in your new query, using the previously suggested syntax - although you can omit the 3rd argument if you just want to return a count of all records.

Andrew