DLookup and Date Comparison

[MightyRoo]

Hello all,

I'm having an issue with obtaining some data from a table that falls between a date range. The code is as follows:

CustDiscount = DLookup("[CustDisc]", "TblDiscounts", "[CustID]='" & CustomerID & "AND [EffectiveDate]< " & OrderDate & " AND [ExpirationDate]> '" & OrderDate & "'")

Essentially I would like to be able to obtain a number by looking up from a table by comparing a date range and an identifier and use that number as a variable.

Any feedback or thoughts on how to obtain the data out of the table would be greatly appreciated.

Thanks

 

[jedraw]

You could try this version which has not been tested.
Access wants # to enclose Date datatype fields. You are missing a ' after CustomerId before the AND

CustDiscount = DLookup("[CustDisc]", "TblDiscounts", "[CustID]='" & CustomerID & "' AND [EffectiveDate]< #" & OrderDate & "# AND [ExpirationDate]> #" & OrderDate & "#")

 

[Axworthy]

Whenever I need to use dates in a SQL statement or a DLookup, I use a simple function:

Function SQLDate(datDate As Date) As String
    SQLDate = "#" & Format(datDate, "mm/dd/yyyy") & "#"
End Function

Then you can change your statement to this:

CustDiscount = DLookup("[CustDisc]", "TblDiscounts", "[CustID]='" & CustomerID & "AND [EffectiveDate]< " & SQLDate(OrderDate) & " AND [ExpirationDate]> " & SQLDate(OrderDate))

 

[MightyRoo]

Thank you both for your reply. Axworthy, your code did what I wanted. Thank you for that little trick.