Multiple criteria in DLookup in Access.

[snowy1]

I have a table called tblBoxRsum with fields LeagueID,BoxID,LowerRsum and UpperRsum.
In a query I have the fields EntryID,LeagueID and Rank
I want a Dlookup to find which BoxID the entry is in dependant on the LeagueID & Rank. All fields are numeric.
I have the following

BoxID: Nz(DLookUp("BoxID","tblBoxRsum","LeagueID="&[LeagueID] And "" & [Rank] & " Between LowerRSum And UpperRSum"),"")

This does not work. If I omit the LeagueID criteria it allocates correctly according to rank but obviously takes no notice of league duplicating boxIDs for entries.
Any help appreciated. Many thanks

[dgunner71]

snowy1,

I'm not sure how you're using this - a few of the 'Ands' appear to be out of place.

In any event, I assume that LeagueID is a string - as such, you'll need to enclose it in single parentheses. Also, you may want to break this up if it is separate criteria or create a concatenated field for part or all of the field.

Last, you may need to use Right() and Left() operators if some of your search will be on various parts of the same field.

Please tell me if League ID contains all the information you want to search by.

Gunner

[zmbd]

@OP:
Let's take a look at your where clause:
"LeagueID= "&[LeagueID] And "" & [Rank] & " Between LowerRSum And UpperRSum"
yuck

"(LeagueID= " & [LeagueID] & ") And (" & [Rank] & " Between " & LowerRSum & " And " & UpperRSum & ")"
if LowerRSum/UpperRsum are fields then enclose in square brackets; however, you note that the "BETWEEN" works so you may very well leave it alone other that the grouping.

THE SPACEING IS VERY IMPORTAINT too... the string is passed to the JET/ACE engine as a SQL "WHERE" clause; thus, if it wouldn't work in a query, it will NOT work in the DLOOKUP() either... you can [F1] for more information or do a net search for the command's details.

if [LeagueID] is text then add a single quote:
( = '" & [LeagueID] & "' AND " ) same with [Rank].

Also note the grouping with the "()" around the first and second conditions, this helps the DBEngine tell what is where and when.

@dgunner71:

In any event, I assume that LeagueID is a string

we try hard not to assume anything about the poster's intent. You will find very quickly that it is usually much better to ask a clarifying question than to attempt an answer that is potentially mis-leading.

In fact, if you find the question doesn't have enough information to attempt an outright answer you'll find that one of the Mods or Experts will be along shortly to ask for further details... or if lacking too much, the Mods (like myself) will simply pull the thread with some B-T-S comments to OP to help write a better a question.

[dgunner71]

Understood, zmbd.

Thanks for the direction.

Gunner

[snowy1]

Thank you. Worked perfectly once I sorted tblBoxRsum which did not have values for all BoxID.
Many thanks