0

I've been poking around with the below code and cannot get it to work.

Dim db As Database
Dim RecRLs As Recordset
Dim sTripCode, sVanNum As Integer
Dim sDepDate, sArrivalDate As Date
Dim sRoomRate As Currency
Set RecRLs = db.OpenRecordset("qryRLRoomListRates", dbOpenSnapshot)

sTripCode = DLookup("[TourCodeID]", "tblTripCodes", "[TourCode]=[Forms]![frmRMSBuildRLs]![tboxTourCode]")
sDepDate = [Forms]![frmRMSBuildRLs]![tboxDepartureDate]
sVanNum = [Forms]![frmRMSBuildRLs]![tboxVanNumber]
sArrivalDate = RecRLs!ArrivalDate


sRoomRate = DLookup("[RateTwinHosCab]", "tblRLRatesByTrip", "[TourCode] = " 
& sTripCode & " AND [DepartureDate] = " & sDepDate & " AND [VanNumber] = " & 
sVanNum & " AND [ArrivalDate] = " & sArrivalDate)

The issue is that sRoomRate returns null. I've MsgBox'd each of the variables: sTripCode, sDepDate, sVanNum, and sArrivalDate. They each return the correct result.

Any ideas why sRoomRate would return null? Thank you so much!

arbitel
  • 321
  • 6
  • 22
  • Does `sDepDate` contain a string such as `"#10/24/2017#"` or does it just contain a string such as `"10/24/2017"` or does it contain a date such as `10/24/2017`? – YowE3K Oct 23 '17 at 23:35
  • sDepDate contains the format: 10/24/2017 . no quotes or hashtags. thank you! – arbitel Oct 23 '17 at 23:45
  • I should add all of the variables match. I.E. [TourCode] is a number field and sTripCode returns a number, etc. Thanks! – arbitel Oct 23 '17 at 23:46
  • Ah you led me down the right path. Solution posted below. – arbitel Oct 23 '17 at 23:53

1 Answers1

3

The solution was to add the #s around the dates below:

sRoomRate = DLookup("[RateTwinHosCab]", "tblRLRatesByTrip", "[TourCode] = " & 
sTripCode & " AND [DepartureDate] = #" & sDepDate & "# AND [VanNumber] = " & 
sVanNum & " AND [ArrivalDate] = #" & sArrivalDate & "#")
arbitel
  • 321
  • 6
  • 22
  • You should probably explicitly format your dates using `= #" & Format(sDepDate, "mm/dd/yyyy") & "# AND ...`. Otherwise VBA will format them with whatever default date settings are in place, which might be a `"dd/mm/yyyy"` format for instance, and that could cause issues if anyone uses the code other than yourself. – YowE3K Oct 24 '17 at 00:18
  • That is only half of the solution.You miss the format as described by @YowE3K. Or use my `CSql` function [here](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error/43591778#43591778) for concatenating your values. – Gustav Oct 24 '17 at 08:55