Dlookup with two criteria (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 06:22
Joined
Feb 4, 2014
Messages
576
if I have two criteria for a dlookup...one being text & the other a number ...bot stored in variable what would the command be?

So for example, two variables....

search_name = "john"
search_age = 49

I wish to use those variables above to search a table called members for a surname using dlookup...

dlookup("surname","members", name= search_name AND age = search_age )

it's the syntax in red I can't get right ...any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:22
Joined
Oct 29, 2018
Messages
21,322
Try
Code:
DLookup("surname","members","[name]=' & search_name & "' And age=" & search_age)
 

peskywinnets

Registered User.
Local time
Today, 06:22
Joined
Feb 4, 2014
Messages
576
[Post Self deleted - I spoke to soon & still haven't solved the problem yet!]
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 06:22
Joined
Jan 14, 2017
Messages
18,164
So what happened when you tried DBG's suggestion?

Two other things
1. Name is a reserved word in Access so shouldn't be used as a field name. That's why DBG enclosed it in []
2. Age should never be used as a field as it keeps changing. Use date of birth or year of birth instead and calculate Age in queries as needed.
 

peskywinnets

Registered User.
Local time
Today, 06:22
Joined
Feb 4, 2014
Messages
576
So what happened when you tried DBG's suggestion?

I think I got a syntax error for the command (not 100% sure, it was many iterations ago!)

I managed to get it working with this syntax....

DLookup("surname", "members", "[age] = " & search_age & " And [name] = """ & search_name & """")

Point taken about the reserved words...I'm not actually using those words, I just picked those words in my opening post to make following what I was trying to achieve a little easier on the eye.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:22
Joined
Feb 19, 2013
Messages
16,521
I'm not actually using those words, I just picked those words in my opening post to make following what I was trying to achieve a little easier on the eye.
the problem with doing that is that it potentially hides the true problem. If you don't want to show your real names do as you did but say something like 'I've changed the names to make it easier on the eye'
 

peskywinnets

Registered User.
Local time
Today, 06:22
Joined
Feb 4, 2014
Messages
576
the problem with doing that is that it potentially hides the true problem. If you don't want to show your real names do as you did but say something like 'I've changed the names to make it easier on the eye'


it wasn't because I want to keep stuff secret, but more that the actual field names involved are a little obtuse, hence trying to make it a little easier to read ...the reason I didn't make the disclaimer., is because I'd overlooked the fact that they were reserved words(!), but in future I will :)
 

isladogs

MVP / VIP
Local time
Today, 06:22
Joined
Jan 14, 2017
Messages
18,164
I only said that Name was a reserved word. Whatever you call it an age field is a bad choice fora different reason as previously explained
 

Users who are viewing this thread

Top Bottom