DLookup interrogates for a Null value

[LOUISBUHAGIAR54]

I am using the following vba verse to see if there is a record with certain criteria.

Private Sub Command46_Click()
If IsNull(cltidcard) Then Exit Sub
Dim sodda As String
sodda = (DLookup("[RoomNo]", "Roomlog", "[IdcardNo] = " & Me.cltidcard & " And IsNull (ExitDate)"))
If Not IsNull(sodda) Then

MsgBox "We have already allocated bed" & sodda & "for this patient!", vbExclamation, sodda
Exit Sub
Else
End If
DoCmd.OpenForm "frmAdmission"
End Sub

When I try to run this code I get error message 3075, syntax error (missing operator) in query expression '[IdcardNo] = 34556m And IsNull (ExitDate)'.

Can I please get some help on how exactly to punctuate the DLookup statement?


LOUISB

 

[pbaldy]

The data type of the field must be text:

http://www.mvps.org/access/general/gen0018.htm

 

[LOUISBUHAGIAR54]

Many thanks. That is already of some help. However when I change the code to the following:

sodda = (DLookup("[RoomNo]", "Roomlog", "[IdcardNo] = '" & Me.cltidcard & "' And IsNull (ExitDate)"))

now I get runtime error 94 Invalid use of Null.

From a search online the line should be written like I put it. How do I correct the second part of the criteria?

Many thanks.


LouisB.

 

[pbaldy]

That would imply the DLookup is returning a Null. Does this avoid the error?

sodda = Nz(DLookup("[RoomNo]", "Roomlog", "[IdcardNo] = '" & Me.cltidcard & "' And IsNull(ExitDate)"),0)

If not, my guess is that Me.cltidcard is Null. Set a breakpoint and see what its value is at runtime.

 

[LOUISBUHAGIAR54]

Dear Paul,


With your valuable and efficient help the code is working now. For the benefit of others who may have the same difficulty the code is now as follows and is working like clockwork. I had to make some adjustment with regards to the variable type.

Private Sub Command46_Click()
If IsNull(cltidcard) Then Exit Sub
Dim sodda As Variant
sodda = Nz(DLookup("[RoomNo]", "Roomlog", "[IdcardNo] = '" & Me.cltidcard & "' And IsNull(ExitDate)"), 0)
If sodda <> 0 Then
MsgBox "We have already allocated bed " & sodda & " for this patient!", vbExclamation, sodda
Exit Sub
Else
End If
DoCmd.OpenForm "frmAdmission"
End Sub


Many thanks again. You're obviously an expert.


LOUISb

 

[pbaldy]

Happy to help. The Nz() function should have eliminated the need for the Variant type, unless a Null could come from the RoomNo field. In any case, nothing wrong with belt + suspenders.

 

[LOUISBUHAGIAR54]

I see what you mean. However I suppose that if no record exists with the criteria, RoomNo will be Null. In most cases this would be the expected outcome envisaged when the command button is fired.

Many thanks once again.

LouisB