Error 2766

[Alex Motilal]

RoomTariffs.zipHello everybody,

I am developing a Hotel Room Booking Database. I created a function to pull the Room Tariff applicable on the booking date. However, when the query is run it shows error # 2766. I have attached the file for analysis. Please tell what I have done wrong.

Alex

[pbaldy]

The text of the error should give you a hint. You didn't concatenate the RoomType variable into the string.

[Alex Motilal]

Thanks for the reply pbaldy.
Still I could not solve the problem. The RoomNature field as you can see is an Integer field. Can you please solve and upload the database for me?
Alex

[pbaldy]

I'm on the road for another hour. You have to concatenate that variable into the string the same way you did the other. No delimiters since it's numeric.

[Alex Motilal]

I changed the function and Query SQL as follows:

Function:

Public Function RoomRateChargeable(BookedDt As Date, RoomNbr As String) As Double

RoomRateChargeable = DLookup("[RoomTariff]", "[QryTariffs]", "[DtFrom]<= #" & BookedDt & "# And Nz([DtTo],#12/31/9999#)>= #" & BookedDt & "# And [RoomNumber]='" & [RoomNbr] & "'")

End Function

Created New Qry: QryTariffs - SQL:

SELECT tblRoomNo.RoomNumber, tblTariffs.RoomTariff, tblTariffs.DtFrom, tblTariffs.DtTo
FROM (tblRmNature INNER JOIN tblRoomNo ON tblRmNature.RmNatureID = tblRoomNo.RoomNature) INNER JOIN tblTariffs ON tblRmNature.RmNatureID = tblTariffs.RoomNature;

Query1 SQL:

SELECT tblBooking.GuestName, tblRoomNo.RoomNumber, tblRmNature.RmNatureID, tblBooking.CheckInDt, tblBooking.CheckOutDt, RoomRateChargeable([CheckInDt],[RoomNumber]) AS RmTariff
FROM tblRmNature INNER JOIN (tblRoomNo INNER JOIN tblBooking ON tblRoomNo.RoomNumber = tblBooking.RoomBooked) ON tblRmNature.RmNatureID = tblRoomNo.RoomNature;

This solved my problem.

Alex

[pbaldy]

Glad you got it sorted out.