Dlookup with two criteria

[peskywinnets]

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]

Try

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

 

[peskywinnets]

[Post Self deleted - I spoke to soon & still haven't solved the problem yet!]

 

[isladogs]

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]

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]

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]

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]

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