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

Dlookup with 2 criteria

MSeda
Expert 100+
P: 159
I have a loop that is controlled by a Dlookup statement with two criteria. Both criteria fields are checkboxes. I have tried an assortment of quotation marks in the criteria section and cannot get the Dlookup to run. My statement looks like this (I have removed the extraneous quotes since they are not working anyway):

Do Until IsNull(DLookup("[PPO Part#]", "Quoted Parts Table", "[PPO Quote Acc] = Yes And [PPO Order Parts] = Yes"))

I've never quite understood the logic of the quotation marks in the criteria statement but it usually works after several tries, this is the first time I,ve used Dlookup with 2 criteria. Any help will be appreciated
Oct 25 '06 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,470
The amended code should work for you.

Expand|Select|Wrap|Line Numbers
  1. Do Until IsNull(DLookup( _
  2.    Expr:="[PPO Part#]", _
  3.    Domain:="Quoted Parts Table", _
  4.    Criteria:="(([PPO Quote Acc]) And ([PPO Order Parts]))"))
I've assumed from your code that [PPO Quote Acc] and [PPO Order Parts] fields are both of type Boolean so will not need to be compared to Yes.
If they are text fields and you need to compare against the text "Yes" then that is different and will need a code change.
Oct 25 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Two things:

Table and field names cannot contain blank spaces so they need to be inclosed in square brackets to show what they are.

Yes is text and all text has to be surrounded by single or double quotation marks (single inside a query).

therefore

Do Until IsNull(DLookup("[PPO Part#]", "[Quoted Parts Table]", "[PPO Quote Acc] = 'Yes' And [PPO Order Parts] = 'Yes'"))
Oct 26 '06 #3

NeoPa
Expert Mod 15k+
P: 31,470
I missed out on the '[]' for the table name.

Expand|Select|Wrap|Line Numbers
  1. Do Until IsNull(DLookup( _
  2.    Expr:="[PPO Part#]", _
  3.    Domain:="[Quoted Parts Table]", _
  4.    Criteria:="(([PPO Quote Acc]) And ([PPO Order Parts]))"))
However, Yes can be a string but can also be an alternative for True - a predefined constant within VBA.
If you want Yes, the code above is ok, if you want "Yes" then change the last line to :-
Expand|Select|Wrap|Line Numbers
  1.    Criteria:="(([PPO Quote Acc] = 'Yes') And ([PPO Order Parts] = 'Yes'))"))
as shown by MMcCarthy above.
Oct 26 '06 #4

Post your reply

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