Need help on DlookUp creteria

[kamulegs]

Hello

I am new to Access World Forum

My access programming skills are intermediate

I have been trying to create a dlookup to check for overlapping bookings but over 3 hrs, i couldnt get it correct.

I get a message that is there is a overlapping booking even where there is none!( i have few testing records)

Here is the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varNum As Variant '
varNum = Me.BookingDetailsID
If IsNull(varNum) Then varNum = 0
If Not IsNull(DLookup("BookingDetailsID", "tblBookingDetails", _
"(RoomID=" & _
Me.RoomID & ") And (CheckInDate<#" & Me.CheckOutDate & _
"#) And (CheckOutDate> #" & Me.CheckInDate & "#) And (BookingDetailsID<>" & _
varNum & ")")) Then
If MsgBox(" This entry creates an overlaping booking, do you want to proceed?", vbYesNo) = vbNo Then
Cancel = True
Exit Sub
End If
End If
End Sub

Thanks in advance

God bless

 

[spikepl]

Put the WHERE condition in a string, debug.print the string and check if it is as expected. Otherwise you are navigating blind.

 

[kamulegs]

Put the WHERE condition in a string, debug.print the string and check if it is as expected. Otherwise you are navigating blind.

Thanks spikepl

I have tried your suggestion

But put the strSql in afterupdate event to see the results

Here is the code

Private Sub Form_AfterUpdate()
Dim varNum As Variant
Dim strSql As String
varNum = Me.BookingDetailsID
If IsNull(varNum) Then varNum = 0
strSql = DLookup("BookingDetailsID", "tblBookingDetails", _
"(RoomID=" & _
Me.RoomID & ") And (CheckInDate<#" & Me.CheckOutDate & _
"#) And (CheckOutDate> #" & Me.CheckInDate & "#) And (BookingDetailsID<>" & _
varNum & ")")
Debug.Print strSql
Debug.Print CheckInDate
Debug.Print CheckOutDate
Debug.Print RoomID
End Sub

In the immediate window for the strSql i see 1 printed
The dates are printed correctly

Any ideas?

Ronald.

 

[spikepl]

Difficult to see, since your dlookup looks OK.

You can create a query in the query designer to list all the records from tblBookingDetails. Change to SQL view in the query designer, and add
"WHERE " and the contents of strSQL from the Immediate Window. Run the query and see if it makes sense.

 

[PeterF]

The Dlookup shouldn't be in the strSQL, what is the output of strSQL?
What do you want to do with the dlookup, I expected to see someting like:

strSql = "(RoomID=" & Me.RoomID & ") And (CheckInDate<#" ...rest of the string

me.someControl = DLookup("BookingDetailsID", "tblBookingDetails", strSQL)

 

[spikepl]

#5 good point , I missed that OP did NOT put the criteria into the strSQL

 

[kamulegs]

I have given up on DlookUp
Tried the Dao code bellow which i am still testing but so far it has been good

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varNum As Variant
varNum = Me.BookingDetailsID

If IsNull(varNum) Then varNum = 0

'Create a dao recorset
Dim rs As DAO.Recordset
'set it up to check the booking details
Set rs = CurrentDb.OpenRecordset("SELECT BookingDetailsID " & _
"FROM tblBookingDetails " & _
"WHERE RoomID = " & Me.RoomID & _
" AND CheckInDate < #" & Me.CheckOutDate & "#" & _
" AND CheckOutDate > #" & Me.CheckInDate & "#" & _
" AND BookingDetailsID <> " & varNum & "", dbOpenSnapshot)

'if it doesn't reach the end then there are records
If Not rs.EOF Then
If MsgBox(" This entry creates an overlaping booking, do you want to proceed?", vbYesNo) = vbNo Then
Cancel = True
Exit Sub

End If
End If

 

[PeterF]

Glad you solved it.
Please use the code tags next time in stead of the quote tags as it makes code easier to read.