Invalid use of NULL

[Susan Bricker]

Hi. I'm back again with another brain-buster (for me). I am getting
"Invalid use of Null" with an error number of 94 for the following ...

1. Form based on a table (tblPeople)
2. The people record contains name (firstname, lastname, middle initial,
name suffix) fields, address fields (addressline1, addressline2, city,
state, zipcode, country), and some other fields (email address, phone
number, various on/off switches to indicate category to which this
person belongs)
3. The tblPeople record has PK (long int) called peopleID
4. I added some rudimentary duplicate detection logic and that is what
is failing. I build a criteria phrase and move it to a variable call
strCriteria and use the criteria in a DLOOKUP call. If the all the
fields match then we have a duplicate record.

Here's the code ... it fails at the DLOOKUP when we have a field that is
NULL.

Anybody have a better solution?

Public Function DupDetect() As Integer
'*===========================*
'Returned data ... *
'1 = Duplicate Record *
'0 = NOT a Duplicate Record *
'*===========================*
On Error GoTo Err_DupDetect

Dim lngID As Long
Dim strCriteria As String

strCriteria = ""

'Setup the CRITERIA for the DLOOKUP
If Not IsNull(Me!fname) Then
strCriteria = strCriteria & "[fname] = '" & Me!fname & "' "
Else
strCriteria = strCriteria & "IsNull([fname]) "
End If
If Not IsNull(Me!midinit) Then
strCriteria = strCriteria & "AND [midinit] = '" & Me!midinit &
"' "
Else
strCriteria = strCriteria & "AND IsNull([midinit]) "
End If
If Not IsNull(Me!lname) Then
strCriteria = strCriteria & "AND [lname] = '" & Me!lname & "' "
Else
strCriteria = strCriteria & "AND IsNull([lname]) "
End If
If Not IsNull(Me!suffix) Then
strCriteria = strCriteria & "AND [suffix] = '" & Me!suffix & "'
"
Else
strCriteria = strCriteria & "AND IsNull([suffix]) "
End If
If Not IsNull(Me!addr1) Then
strCriteria = strCriteria & "AND [addr1] = '" & Me!addr1 & "' "
Else
strCriteria = strCriteria & "AND IsNull([addr1]) "
End If
If Not IsNull(Me!addr2) Then
strCriteria = strCriteria & "AND [addr2] = '" & Me!addr2 & "' "
Else
strCriteria = strCriteria & "AND IsNull([addr2]) "
End If
If Not IsNull(Me!city) Then
strCriteria = strCriteria & "AND [city] = '" & Me!city & "' "
Else
strCriteria = strCriteria & "AND IsNull([city]) "
End If
If Not IsNull(Me!state) Then
strCriteria = strCriteria & "AND [state] = '" & Me!state & "' "
Else
strCriteria = strCriteria & "AND IsNull([state]) "
End If
If Not IsNull(Me!zipcode) Then
strCriteria = strCriteria & "AND [zipcode] = '" & Me!zipcode &
"' "
Else
strCriteria = strCriteria & "AND IsNull([zipcode]) "
End If

'Remove leading and trailing spaces
strCriteria = Trim(strCriteria)

'Look for an identical record in the tblPeople table
lngID = DLookup("[peopleID]", _
"tblPeople", _
strCriteria)

'If lngID is a valid integer then we found a record ... DUPLICATE
'Otherwise ... NODUPLICATE
If Nz(lngID, 0) > 0 Then
DupDetect = YESDUP
Else
DupDetect = NODUP
End If

Exit_DupDetect:
Exit Function

Err_DupDetect:
MsgBox strCriteria
Call ShowError("frmPeople", "DupDetect", Err.Number,
Err.Description)
Resume Exit_DupDetect

End Function

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

One more piece of information ...
The error only occurs if I type in data for a record that I KNOW does
not exist. If I type in data for a record that already exists (and some
of the fields are NULL on the data base and I make sure that I don't
fill in data for those fields) .. the DLOOKUP works and finds a match.

Wow ... now I'm even more stumped.

HELLLLLPPPP.

Regards,
SueB

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Br]

Susan Bricker wrote:

Hi. I'm back again with another brain-buster (for me). I am getting
"Invalid use of Null" with an error number of 94 for the following ...

1. Form based on a table (tblPeople)
2. The people record contains name (firstname, lastname, middle
initial, name suffix) fields, address fields (addressline1,
addressline2, city, state, zipcode, country), and some other fields
(email address, phone number, various on/off switches to indicate
category to which this person belongs)
3. The tblPeople record has PK (long int) called peopleID
4. I added some rudimentary duplicate detection logic and that is what
is failing. I build a criteria phrase and move it to a variable call
strCriteria and use the criteria in a DLOOKUP call. If the all the
fields match then we have a duplicate record.

Here's the code ... it fails at the DLOOKUP when we have a field that
is NULL.

Anybody have a better solution?

Public Function DupDetect() As Integer
'*===========================*
'Returned data ... *
'1 = Duplicate Record *
'0 = NOT a Duplicate Record *
'*===========================*
On Error GoTo Err_DupDetect

Dim lngID As Long
Dim strCriteria As String

strCriteria = ""

'Setup the CRITERIA for the DLOOKUP
If Not IsNull(Me!fname) Then
strCriteria = strCriteria & "[fname] = '" & Me!fname & "' "
Else
strCriteria = strCriteria & "IsNull([fname]) "
End If
If Not IsNull(Me!midinit) Then
strCriteria = strCriteria & "AND [midinit] = '" & Me!midinit &
"' "
Else
strCriteria = strCriteria & "AND IsNull([midinit]) "
End If
If Not IsNull(Me!lname) Then
strCriteria = strCriteria & "AND [lname] = '" & Me!lname & "' "
Else
strCriteria = strCriteria & "AND IsNull([lname]) "
End If
If Not IsNull(Me!suffix) Then
strCriteria = strCriteria & "AND [suffix] = '" & Me!suffix & "'
"
Else
strCriteria = strCriteria & "AND IsNull([suffix]) "
End If
If Not IsNull(Me!addr1) Then
strCriteria = strCriteria & "AND [addr1] = '" & Me!addr1 & "' "
Else
strCriteria = strCriteria & "AND IsNull([addr1]) "
End If
If Not IsNull(Me!addr2) Then
strCriteria = strCriteria & "AND [addr2] = '" & Me!addr2 & "' "
Else
strCriteria = strCriteria & "AND IsNull([addr2]) "
End If
If Not IsNull(Me!city) Then
strCriteria = strCriteria & "AND [city] = '" & Me!city & "' "
Else
strCriteria = strCriteria & "AND IsNull([city]) "
End If
If Not IsNull(Me!state) Then
strCriteria = strCriteria & "AND [state] = '" & Me!state & "' "
Else
strCriteria = strCriteria & "AND IsNull([state]) "
End If
If Not IsNull(Me!zipcode) Then
strCriteria = strCriteria & "AND [zipcode] = '" & Me!zipcode &
"' "
Else
strCriteria = strCriteria & "AND IsNull([zipcode]) "
End If

'Remove leading and trailing spaces
strCriteria = Trim(strCriteria)

'Look for an identical record in the tblPeople table
lngID = DLookup("[peopleID]", _
"tblPeople", _
strCriteria)

'If lngID is a valid integer then we found a record ... DUPLICATE
'Otherwise ... NODUPLICATE
If Nz(lngID, 0) > 0 Then
DupDetect = YESDUP
Else
DupDetect = NODUP
End If

Exit_DupDetect:
Exit Function

Err_DupDetect:
MsgBox strCriteria
Call ShowError("frmPeople", "DupDetect", Err.Number,
Err.Description)
Resume Exit_DupDetect

End Function

Regards,
SueB

Change ....

Dim lngID As Long

to

Dim varID as Variant

You can't set a long to NULL.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Bob Quintal]

Susan Bricker <sl*****@verizon.net> wrote in news:aSx5f.38$8Q2.510
@news.uswest.net:

'Look for an identical record in the tblPeople table
lngID = DLookup("[peopleID]", _
"tblPeople", _
strCriteria)

lngID = NZ(Dlookup("[PeopleID]","[tblPeople]",strcriteria),0)

--
Bob Quintal

PA is y I've altered my email address.

[Darryl Kerkeslager]

1. s works just as good for a variable name as strCriteria. Also makes
your code more readable.
2. You could eliminate all your "Not"s by flipping the conditional:

If Not IsNull(Me!fname) Then
s = s & "[fname] = '" & Me!fname & "' "
Else
s = s & "IsNull([fname]) "
End If

becomes:

If IsNull(Me!fname) Then
s = s & "IsNull([fname]) "
Else
s = s & "[fname] = '" & Me!fname & "' "
End If

Which is unnoticably faster but DOES make your code more readable.

3. IsNull() is a function. What you are actually doing is creating the
WHERE clause of an SQL query. IsNull has no meaning in SQL - so you could
just change the IsNull to Is Null.

4. Is there really a reason to do the lookup on a null middle initial, or
address, or first name? I mean, if all you're trying to do is find a match,
testing if it matches a null won't get you much. So, for instance, if the
first name Is Null, why not just leave it out of the search criteria:

If Not IsNull(Me!fname) Then
s = s & "[fname] = '" & Me!fname & "' "
End If

So now you have something like this:

Dim s As String
s = vbNullString
If Not IsNull(Me!fname) Then s = s & " AND [fname] = '" & Me!fname & "'"
If Not IsNull(Me!midinit) Then s = s & " AND [midinit] = '" & Me!midinit &
"'"
If Not IsNull(Me!lname) Then s = s & " AND [lname] = '" & Me!lname & "'"
If Not IsNull(Me!suffix) Then s = s & " AND [suffix] = '" & Me!suffix & "'"

Note that I've added " AND" to the first string, since using this structure,
any clause may be left out of the final string, including the first one.
After all the statements, you then add:

If s <> vbNullString Then
s = Mid(s, 6) ' trims the very first " AND "
.... stuff ....
Else
' No criteria entered
Endif

5. Just before the DLookup is run, while testing, place a MsgBox s line to
examine the string carefully.

As a final note, after you get it working with DLookup, consider using ADO
or DAO.

--
Darryl Kerkeslager

[Darryl Kerkeslager]

And of course, missed the trees through the forest!

--
Darryl Kerkeslager