Dlookup Issue with multiple criteria

[foxhound]

Hello,

I am using Access 2000 and have searched everywhere and cannot find the answer to this! I want to lookup [hours] (a number field) in a table where [group] can be a dynamic group name and, [low_months] and [high_months] are number fields. If my value, yrssrv is between [low_months] and [high_months] and my value, grp matches [group] then I need to know the number of [hours] in [tbl_Vacation Schedule (New Prior Year)].

...something like this but using between.

numhours = DLookup("[Hours]", "[tbl_Vacation Schedule (New Prior Year)]", _
"[group]= '" & grp & "'" & _
" And [Low_Months] >= " & yrssrv & _
" And [High_Months] <= '" & yrssrv & "'")

 

[foxhound]

Thanks for the suggestion Norie I tried almost every combination I could to use "between" and couldn't get it to work. Here is my latest attempt that still doesn't work:

rs_Snapshot!VacAwardHours = DLookup("[Hours]", "[tbl_Vacation Schedule (New Prior Year)]", _
"[group]= '" & VGF & "'" And " & yrssrv & "" between LOW_" & CalcMethod & " and HIGH_" & CalcMethod)

"VGF" is a text field
"yrssrv" is a number field
"CalcMethod" is a text variable that I use to switch between "months" and "years"

 

[foxhound]

Here is sql code that works when returning the record in a recordset but I need to use dlookup() instead

"Select [HOURS] from " & VacSchedule & " where [GROUP] = '" & VGF & "'" _
& " and (" & srvyrs & " BETWEEN LOW_" & CalcMethod & " and HIGH_" & CalcMethod & ")"

 

[Norie]

When you run that SQL code are you prompted for parameters?

 

[foxhound]

I had the parameters defined already so I didn't get an error on the sql. Guess what? I finally tinkered with the code enough and got this to work!


rs_Snapshot!VacAwardHours = DLookup("[Hours]", VacSchedule, _
"[group]= '" & VGF & "'" & _
" And (" & int_Years & " between [Low_" _
& CalcMethod & "] And [High_" & CalcMethod & "])")


Norie, thanks for your assistance. Now I just need to get the other post resolved