DLookup Missing Operator

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
DLookup("BageNumber", "tblBadges", "BadgeNumber=" & Result1 & " And Returned = No  And BadgeType = Blue V Badge")

Can't seem to figure this one out.  Tried all the different combinations with quotes but can't get it right....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
DLookup("[BageNumber]", "tblBadges", "[BadgeNumber] = " & Result1 & " And [Returned] = False And [BadgeType] = 'Blue V Badge'")

assuming BadgeNumber is a numeric.   All the square brackets ( [ ] ) are for safety.  While nothing here explicitly needed it, it's best to always use them on field names.

Also you needed false for "no" and BadgeType is text, so it needed string delimiters on it.

Jim.
Top Expert 2016

Commented:
DLookup("BageNumber", "tblBadges", "BadgeNumber=" & Result1 & " And Returned = 'No'  And BadgeType = 'Blue V Badge'")


if returned is boolean type field

DLookup("BageNumber", "tblBadges", "BadgeNumber=" & Result1 & " And Returned = 0  And BadgeType = 'Blue V Badge'")
Ernest GroggSecurity Management InfoSec

Author

Commented:
Good call, but gives me the error on BadgeNumber

That is for either way....
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ernest GroggSecurity Management InfoSec

Author

Commented:
also, note that I take out the 2nd "And" it it works just fine, just adding this doesn't seem to work?
Top Expert 2016

Commented:
<also, note that I take out the 2nd "And" it it works just fine>

that is weird?????

can you do a compact and repair, then try again

also, is this correct field name "BageNumber" ???
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Is badgenumber numeric or text?

Jim.
Top Expert 2016
Commented:
if badgeNumber is text, did you try
                       
DLookup("BageNumber", "tblBadges", "BadgeNumber='" & Result1 & "' And Returned = 0  And BadgeType = 'Blue V Badge'")
Ernest GroggSecurity Management InfoSec

Author

Commented:
OK, gotcha.  Sorry, I misspelled the field in the table....

Sometimes....I could....well you know...
Top Expert 2016

Commented:
so,  BageNumber is BadgeNumber
if that is the case, why are you still looking for the "BadgeNumber" in the dlookup when you are already know it from the variable "Result1" ?

maybe you should look for other Field values, based on BadgeNumber, Returned and BadgeType  parameters..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial