Multiple criteria in dlookup?

  • Thread starter Thread starter clueless
  • Start date Start date
C

clueless

I've read all there is on dlookup but can't find any examples where
dlookup has multiple expressions. Example : my db wants to check 1) If
two dates(arrive and depart) don't clash for 2) a room in the db,no
double bookingd. Worse even all the fields are on one form, so I'm not
looking for a field on a different table, that prt of dlookup i
understand.
I've already coded to ensure that data is inserted on these 3 fields
and tested the dates (depart>arrive = then error mgsbox).
Now I'm stuck on looking through the records for a clash and display
error message.

I've tried using RecordsetClone coding but it saves records even if
there are clashes.No error displays.

I've tried
Dim myLookup As Long
myLookup = dlookup("[roomId]","Bookings","[BookId]=" _
&dlookup("[arrive]<=[depart]","Bookings","[BookId]=")
This also saves the record and looks for nothing.

I am officially confused. Can somebody please help explain what I'm
doing wrong. Or should I be using an If statement instead of lookup.

Thank You.
 
2 events clash if both:
A starts before B ends, and also
B starts before A ends.

In your case, you need 2 more conditions, to ensure it is the same room, and
that the booking does not clash with itself. (I have assumed a primary key
field named ID in the table to check the booking does not clash with
itself.)

The goal is to set up the Criteria string so that it looks like the WHERE
clause in a query.

Untested example to give you the idea:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String 'MsgBox message.
Dim strWhere As String 'Criteria for DLookup()
Dim varResult As Variant 'Result of DLookup().
Const conJetDate = "\#mm\/dd\/yyyy\#" 'Format for literal dates in
JET.

If (Me.arrive = Me.arrive.OldValue) And _
(Me.depart = Me.depart.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue) Then
'do nothing.
Else
If IsNull(Me.arrive) Or IsNull(Me.depart) Or IsNull(Me.RoomID) Then
Cancel = True
strMsg = strMsg & "Arrive and Depart dates and Room required." &
vbCrLf
ElseIf Me.depart < Me.arrive Then
strMsg = strMsg & "Depart cannot be before Arrive." & vbCrLf
Else
strWhere = "([arrive] < " & Format(Me.depart, conJetDate) & _
") AND (" & Format(Me.arrive, conJetDate) & _
" < [depart]) AND ([roomID] = " & Me.RoomID & _
") AND ([id] <> " & me.id & ")"
'Debug.Print strWhere
varResult = DLookup("id", "Bookings", strWhere)
If Not IsNull(varResult) Then
Cancel = True
strMsg = strMsg & "Clashes with booking ID " & varResult &
vbCrLf
End If
End If
End If

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> twice
to undo."
MsgBox strMsg, vbExclamation, "Invalid entry"
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

clueless said:
I've read all there is on dlookup but can't find any examples where
dlookup has multiple expressions. Example : my db wants to check 1) If
two dates(arrive and depart) don't clash for 2) a room in the db,no
double bookingd. Worse even all the fields are on one form, so I'm not
looking for a field on a different table, that prt of dlookup i
understand.
I've already coded to ensure that data is inserted on these 3 fields
and tested the dates (depart>arrive = then error mgsbox).
Now I'm stuck on looking through the records for a clash and display
error message.

I've tried using RecordsetClone coding but it saves records even if
there are clashes.No error displays.

I've tried
Dim myLookup As Long
myLookup = dlookup("[roomId]","Bookings","[BookId]=" _
&dlookup("[arrive]<=[depart]","Bookings","[BookId]=")
This also saves the record and looks for nothing.

I am officially confused. Can somebody please help explain what I'm
doing wrong. Or should I be using an If statement instead of lookup.
Click to expand...
 
Back
Top