469,649 Members | 1,709 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,649 developers. It's quick & easy.

Dlookup with 2 criteria

MSeda
159 Expert 100+
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
3 4078
NeoPa
32,203 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
32,203 Expert Mod 16PB
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.

Similar topics

7 posts views Thread by Tony Williams | last post: by
8 posts views Thread by Christine Henderson | last post: by
2 posts views Thread by chris.thompson13 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.