[RESOLVED] Search function - Invalid use of Null

[Hownice]

Hi there,

In my VB6 programm I use the following code in order to search in the Access DB. It looks like it's working fine, until...
This time I want to search for the V-number (V9). In the DB are present the V9 and the V90(!) and here starts the problem. When I search for the V9 I receive a invalid use of Null and when I search for V90 I receive the right anwser.

The code I use is:

Private Sub cmdZoekVnummer_Click()

Dim strSQL As String
strSQL = "SELECT * FROM tbl_Reparaties"

If txtZoekVnummer.Text <> "" Then
        strSQL = strSQL & " WHERE fldVnummer LIKE '" & Trim(Replace((txtZoekVnummer.Text), "'", "''")) & "%'"
End If

rs1.Close

rs1.Open strSQL, cn1, adOpenKeyset, adLockPessimistic, adCmdText
    
If Not (rs1.BOF = True Or rs1.EOF = True) Then
       txtZoekVnummer.Text = ""
    End If

FillFields

End Sub

The strange thing is that at debug the yellow line gets at the Fillfield

Public Sub FillFields()

    If Not (rs1.BOF = True Or rs1.EOF = True) Then

        txtDatum.Text = rs1.Fields("fldDatum")
        txtVnummer.Text = rs1.Fields("fldVnummer")
        txtMerk.Text = rs1.Fields("fldMerk")
        txtSerienummer.Text = rs1.Fields("fldSerienummer")
        txtType.Text = rs1.Fields("fldType")
        txtSoort.Text = rs1.Fields("fldSoort")
        txtKlacht.Text = rs1.Fields("fldKlacht")
        txtAccessoires.Text = rs1.Fields("fldAccessoires")
        txtStatus.Text = rs1.Fields("fldStatus")

'this line below is yellow        
        txtBedrijfsnaam.Text = rs1.Fields("fldBedrijfsnaam")

        txtProject.Text = rs1.Fields("fldProject")
        txtLocatie.Text = rs1.Fields("fldLocatie")
        txtContactpersoonVoornaam.Text = rs1.Fields("fldContactpersoonVoornaam")
        txtContactpersoonTussenvoegsel.Text = rs1.Fields("fldContactpersoonTussenvoegsel")
        txtContactpersoonAchternaam.Text = rs1.Fields("fldContactpersoonAchternaam")
        txtContactpersoonTelefoon.Text = rs1.Fields("fldContactpersoonTelefoon")
        txtContactpersoonEmail.Text = rs1.Fields("fldContactpersoonEmail")
        
    End If

End Sub

I looked extra at fields from fldBedrijfsnaam and the following (order as in the code), but could not find any difference with the first like fldVnummer and fldMerk etc.

Looking to many threads didn't gave me the anwser for this problem, so I hope someone is able to help me with this.

Thanks

[akhileshbc]

It is because, in one of your records, the fldBedrijfsnaam field is having a Null value (empty). That's what I think...

[dpaulson]

You can change a null to a string by appending a string to it.

txtBedrijfsnaam.Text = rs1.Fields("fldBedrijfsnaam") & ""

[RhinoBull]

...
The code I use is:

...

'this line below is yellow        
txtBedrijfsnaam.Text = rs1.Fields("fldBedrijfsnaam")

...

The fiollowing should be applied to all other lines where you assign recordset fields values to tyour textboxes:

'you can do this (preferable)
If Not IsNull(rs1.Fields("fldBedrijfsnaam")) Then
    txtBedrijfsnaam.Text = rs1.Fields("fldBedrijfsnaam")
Else
    txtBedrijfsnaam.Text = "N/A" '<<< this isn't necessary - just showing you what can be done...
End If

'or this:
txtBedrijfsnaam.Text = "" & rs1.Fields("fldBedrijfsnaam")

[akhileshbc]

Here's a solution to it: http://www.vbforums.com/showthread.php?t=401834 ...

Edit:
Rhino beat me to it

[Hownice]

The fiollowing should be applied to all other lines where you assign recordset fields values to tyour textboxes:

'or this:
txtBedrijfsnaam.Text = "" & rs1.Fields("fldBedrijfsnaam")

Hi Rhinobull and others,

This line dit the trick, now my code works perfect on this part!

Many thanks