By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Multiple criteria in DLookup in Access.

P: 2
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
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 30 '13 #1

✓ answered by 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.

Share this Question
Share on Google+
4 Replies


100+
P: 107
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
Nov 30 '13 #2

zmbd
Expert Mod 5K+
P: 5,287
@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.
Nov 30 '13 #3

100+
P: 107
Understood, zmbd.

Thanks for the direction.

Gunner
Dec 1 '13 #4

P: 2
Thank you. Worked perfectly once I sorted tblBoxRsum which did not have values for all BoxID.
Many thanks
Dec 1 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.