Avatar of keschuster

asked on 

Error when setting null value to variable

I get invalid use of null error on this line  t = rs.Fields(ast).Value

The field can be null or contain text.  any idea how to get around this.  
Public Sub ClearOne()
Dim a As String
Dim s As String
Dim t As String
Dim asn As String
Dim ans As String
Dim ast As Variant
asn = "AssetNumber"
ans = "AssetNumberSuffix"
ast = "AssetTagName"
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT CPT_Assets.AssetNumber, CPT_Assets.AssetId, CPT_Assets.AssetNumberSuffix, CPT_Assets.AssetTagName FROM CPT_Assets WHERE (((CPT_Assets.AssetNumber) In (SELECT [AssetNumber] FROM [CPT_Assets] As Tmp GROUP BY [AssetNumber] HAVING Count(*)>1 ))) ORDER BY CPT_Assets.AssetNumber, CPT_Assets.AssetNumberSuffix DESC, CPT_Assets.AssetTagName DESC;")
If rs.RecordCount > 0 Then
'Goto First Record
a = rs.Fields(asn).Value
s = rs.Fields(ans).Value
t = rs.Fields(ast).Value
Do While Not rs.EOF
    If a = rs.Fields(AssetNumber).Value And t = "Error" And test <> "Error" Then
        'MsgBox "gets here"
        rs.Fields("AssetNumberSuffix").Value = ""
    End If
Set rs = Nothing
Set db = Nothing
MsgBox "All Done"
End If
End Sub

Open in new window

Microsoft AccessMicrosoft Applications

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon