Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2019, 04:17 PM   #1
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 520
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Dlookup with two criteria

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?

peskywinnets is offline   Reply With Quote
Old 07-18-2019, 04:39 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,498
Thanks: 58
Thanked 1,420 Times in 1,401 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Dlookup with two criteria

Try
Code:
DLookup("surname","members","[name]=' & search_name & "' And age=" & search_age)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-18-2019, 11:34 PM   #3
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 520
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Dlookup with two criteria

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


Last edited by peskywinnets; 07-18-2019 at 11:42 PM.
peskywinnets is offline   Reply With Quote
Old 07-18-2019, 11:50 PM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,262
Thanks: 115
Thanked 3,079 Times in 2,797 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Dlookup with two criteria

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 07-19-2019, 12:01 AM   #5
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 520
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Dlookup with two criteria

Quote:
Originally Posted by isladogs View Post
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.
peskywinnets is offline   Reply With Quote
Old 07-19-2019, 12:24 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,343
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Dlookup with two criteria

Quote:
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'
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-19-2019, 02:10 AM   #7
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 520
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Dlookup with two criteria

Quote:
Originally Posted by CJ_London View Post
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 :-)

peskywinnets is offline   Reply With Quote
Old 07-19-2019, 04:03 AM   #8
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,262
Thanks: 115
Thanked 3,079 Times in 2,797 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Dlookup with two criteria

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookUp as criteria within DLookup chuckcoleman Modules & VBA 15 04-10-2019 11:39 AM
DLookup criteria help ... DKoehne Queries 4 11-19-2018 08:50 AM
[SOLVED] Dlookup two criteria ikzouhetnietweten Modules & VBA 3 11-23-2015 10:57 AM
DLookUp with 2 criteria connerlowen Modules & VBA 3 08-05-2015 06:42 AM
DLookup with 2 criteria mikependleton Forms 4 09-21-2005 04:23 AM




All times are GMT -8. The time now is 10:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World