Condition to handle a NULL value in a recordset

[heprox]

I have this simple application that checks a pair of tables in a Sybase database for price, SKU, and UPC. There are occassions when an SKU has a price and SKU record in one table (plu) but there is no corresponding UPC record (in plu_cross_ref). I have the application still returning the records regardless of whether or not there is a UPC by just having an outer join in the SQL statement to the DB. The problem is that I created a little routine to change the "strInfo" variable whenever the field "xrefnum" is empty (Null) however it still isn't working? I've tried:

If rs("xrefnum") = Null Then
strInfo = "Missing UPC"
End If

...and:

If rs("xrefnum") = "Null" Then
strInfo = "Missing UPC"
End If

...and:

If rs("xrefnum") = "" Then
strInfo = "Missing UPC"
End If

...what am I missing? Here is the code:

Private Sub cmdEnter_Click()
Dim strsql As String
Dim strstatus As String
Dim strInfo As String
Dim strItem As String
Set rs = New ADODB.Recordset
Select Case Len(txtData.Text)
Case 12
' where condition uses UPC
strsql = "SELECT PLU_Cross_Ref.plunum, xrefnum, retailprice FROM PLU_Cross_Ref, plu WHERE PLU_Cross_Ref.plunum=plu.plunum AND xrefnum='" & (txtData.Text) & "'"
Case 11
' where condition uses SKU
strsql = "SELECT a.plunum, b.xrefnum, a.retailprice FROM plu a left outer join plu_cross_ref b ON a.plunum = b.plunum WHERE a.plunum='" & (txtData.Text) & "'"
Case Else
lblPrice.Caption = ""
lblSign.Caption = ""
lblStatus.Caption = ("Invalid UPC/SKU!")
lblInfo = ""
txtData.Text = ""
txtData.SetFocus
Exit Sub
End Select
rs.Open strsql, cn, adOpenKeyset, adLockReadOnly
If Not rs.EOF() Then
Select Case Len(txtData.Text)
Case 12
' where condition uses UPC
strInfo = "SKU: " & rs("plunum")
Case 11
' where condition uses SKU
strInfo = "UPC: " & rs("xrefnum")
End Select
'Establish the reference of the active.txt DB and designate the first 9-digits as the item_cd
strItem = Left(rs("plunum"), 9)
Set rsText = New ADODB.Recordset
On Error GoTo errhandler2
rsText.Open "Select * from active.txt where item_cd = " & strItem, cnText, adOpenKeyset, adLockReadOnly
If Not rsText.EOF Then
strstatus = "Active"
Else
strstatus = "99 (Inactive)"
End If
rsText.Close
If rs("xrefnum") = Null Then
strInfo = "Missing UPC"
End If
lblPrice.Caption = rs("retailprice")
lblSign.Caption = "$"
lblStatus.Caption = strstatus
lblInfo.Caption = strInfo
txtData.Text = ""
txtData.SetFocus
Else
'MsgBox "Invalid UPC Number!", vbCritical
lblPrice.Caption = ""
lblSign.Caption = ""
lblStatus.Caption = ("Invalid UPC/SKU!")
lblInfo = ""
txtData.Text = ""
txtData.SetFocus
End If
rs.Close
Exit Sub
errhandler2:
MsgBox "No status file present!", vbCritical
If rs("xrefnum") = Null Then
strInfo = "Missing UPC"
End If
lblPrice.Caption = rs("retailprice")
lblSign.Caption = "$"
lblStatus.Caption = strstatus
lblInfo.Caption = strInfo
txtData.Text = ""
txtData.SetFocus
rs.Close
Exit Sub
End Sub

 

[moki]

You could try:

If rs("xrefnum") = Empty Then
strInfo = "Missing UPC"
End If

' OR

If rs("xrefnum") & "" = Empty Then
strInfo = "Missing UPC"
End If

'OR

If len(rs("xrefnum") & "") = 0 Then
strInfo = "Missing UPC"
End If

 

[heprox]

Yep, that fixed it:

If rs("xrefnum") & "" = Empty Then
strInfo = "Missing UPC"
End If

...thanks.

 

[ArtieChoke]

You should also be able to do:

If isnull(rs("xrefnum")) then ...


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[tsdragon]

Or:

If rs("xrefnum") & "" = "" Then


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.