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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-06-2018, 05:48 AM   #1
Sodslaw
Newly Registered User
 
Join Date: Jun 2017
Posts: 11
Thanks: 14
Thanked 0 Times in 0 Posts
Sodslaw is on a distinguished road
Multiple Dlooups with one result

Hello Guys,
Bit of a newbie so apologies in advance I wish to match 3 results if all 3 are true pop up a single message box appears, using the Dlookup method.

Code:
'If DLookup("Retailer", "OnSite_CoverTrue", "Retailer='" & Me!PURCHASED_FROM & "'") = Me!PURCHASED_FROM Then
'            MsgBox "ONsite Retailer = True"
'    End If
'If DLookup("Brand", "OnSite_CoverTrue", "Brand='" & Me!TV_BRAND & "'") = Me!TV_BRAND Then
'
'            MsgBox "ONsite Brand = True"
'    End If
'If DLookup("Model", "OnSite_CoverTrue", "Model='" & Me!TV_MODEL & "'") = Me!TV_MODEL Then
'
'            MsgBox "ONsite Model = True"
Obviously using this method I get 3 individual pop-ups if all criteria are met. but the goal is to get 1 popup if all 3 are met. please help

Sodslaw is offline   Reply With Quote
Old 06-06-2018, 06:20 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multiple Dlooups with one result

Try

If DLookup(...) And DLookup(...) And DLookup(...) Then
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Sodslaw (06-06-2018)
Old 06-06-2018, 06:26 AM   #3
Sodslaw
Newly Registered User
 
Join Date: Jun 2017
Posts: 11
Thanks: 14
Thanked 0 Times in 0 Posts
Sodslaw is on a distinguished road
Re: Multiple Dlooups with one result

Absolutely Brilliant!

That worked a treat thanks!

Sodslaw is offline   Reply With Quote
Old 06-06-2018, 06:29 AM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,890
Thanks: 38
Thanked 572 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Multiple Dlooups with one result

You could also make a single query that uses
Forms!FormName.PURCHASED_FROM
Forms!FormName.Tv_Brand
Forms!FormName.Tv_Model
as parameters
and do a dcount to see if 1 record is returne
dcount(*,"Myquery") >0
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
Sodslaw (06-06-2018)
Old 06-06-2018, 06:46 AM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,890
Thanks: 38
Thanked 572 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Multiple Dlooups with one result

Or you could make a single where clause and see if one returned.
Code:
dim strWhere as string
string where = "Retailer='" & Me!PURCHASED_FROM & "' AND Brand='" & Me!TV_BRAND & "' AND Model='& Me!TV_MODEL & "'"
debug.print 'verify string is correct
if dcount(*,"Onsite_Cover",strwhere) > 0
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
Sodslaw (06-06-2018)
Old 06-06-2018, 06:47 AM   #6
Sodslaw
Newly Registered User
 
Join Date: Jun 2017
Posts: 11
Thanks: 14
Thanked 0 Times in 0 Posts
Sodslaw is on a distinguished road
Re: Multiple Dlooups with one result

Thanks MajP,
I will try to use the method you described next as I love learning especially new methods using fewer resources and faster results. But, As I'm a bit of an old geezer what life has taught me is that, if it ain't broke, don't fix it.

Thanks again guys, what a great forum!
Sodslaw is offline   Reply With Quote
Old 06-06-2018, 06:49 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multiple Dlooups with one result

Quote:
Originally Posted by Sodslaw View Post
Absolutely Brilliant!

That worked a treat thanks!
Happy to help!

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 06-06-2018, 08:14 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,890
Thanks: 38
Thanked 572 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Multiple Dlooups with one result

Sorry for the typo
if dcount(*,"Onsite_Cover",strwhere) > 0
should be
Code:
if dcount("*","Onsite_Cover",strwhere) > 0
Need to make first argument a string. Also if you do the first solution provided
On that note

Code:
If DLookup("Retailer", "OnSite_CoverTrue", "Retailer='" & Me!PURCHASED_FROM & "'") = Me!PURCHASED_FROM
This may be simpler to write and likely more efficient. A few less places to make mistakes.
Code:
If DCount("*", "OnSite_CoverTrue", "Retailer='" & Me!PURCHASED_FROM & "'") > 0

MajP is online now   Reply With Quote
Reply

Tags
multiple dlookup criteria

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query with Multiple conditions to return result dlafko1 Queries 5 04-16-2019 10:54 AM
Result based on multiple conditions Thrasher Macros 1 03-14-2017 10:48 AM
Calculate multiple fields for one result k9drh Queries 2 02-21-2015 09:35 AM
Visualize the result of multiple queries in a report christophe.dewinne Introduce Yourself 0 11-27-2012 05:20 AM
Consolidating multiple queries into one single result danikuper Queries 4 12-18-2006 05:19 PM




All times are GMT -8. The time now is 08:41 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