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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2018, 12:53 PM   #1
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,597
Thanks: 36
Thanked 27 Times in 27 Posts
Thales750 is on a distinguished road
strSQL in DLookup

If possible to use a SQL Statement in a DLookup.
(not a DLookup in a Query)

How would that be written?

Thanks all.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 05-16-2018, 01:34 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,862
Thanks: 0
Thanked 623 Times in 608 Posts
Ranman256 will become famous soon enough
Re: strSQL in DLookup

No need....the Dlookup IS an SQL.
Either use a table or a query. (Not SQL)
Ranman256 is online now   Reply With Quote
Old 05-16-2018, 03:19 PM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,271
Thanks: 47
Thanked 1,817 Times in 1,767 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: strSQL in DLookup

Not sure what underlying requirement is.

Here is comment from techonthenet.

Code:
Let's look at how to use the DLookup function in MS Access:

DLookup("[UnitPrice]", "Order Details", "OrderID = 10248")

In this example, you would be retrieving the UnitPrice field from the Order Details table
 where the OrderID is 10248. This is the same as the following SQL statement:

SELECT UnitPrice AS Expr1
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));

You can also retrieve a calculation using the DLookup function. For example:

DLookup("UnitPrice * Quantity", "Order Details", "OrderID = 10248")

This example would return the UnitPrice field multiplied by the Quantity field from
 the Order Details table where the OrderID is 10248. This is the same as the 
following SQL statement:

SELECT UnitPrice * Quantity AS Expr1
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 05-16-2018, 08:56 PM   #4
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,598
Thanks: 41
Thanked 1,020 Times in 930 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: strSQL in DLookup

After a quick bit of research in three of the more techie sources, I think the direct answer to the question is "no" because the 2nd argument, the "domain," is called out as a table or named query that doesn't involve a parameter. I have to think that if the domain argument could be an SQL string, they would have described it differently.

Hey, I could be wrong, but I'm not seeing it so far in what I've researched.

FYI, I used: support.office.com, techonthenet.com, and msdn.microsoft.com as likely places to have a discussion on the subject. They all insist that a domain name is required.

I think the reason is because the DLookup function is building its own SQL string via string substitution to create a recordset query "on-the-fly". While I cannot prove that it actually does this, I could easily imagine that the function does this: <<<< WARNING: CONJECTURE ALERT >>>>

Code:
Public Function DLookup( field, domain, "criteria" ) as Variant

Dim strSQL as String
Dim rsDLK as DAO.Recordset

  strSQL = "SELECT " & field & " FROM " & domain 
  IF NZ( criteria, "" ) <> "" THEN strSQL = strSQL & " WHERE " & criteria
  strSQL = strSQL & ";"
  SET rsDLK = CurrentDB.OpenRecordset( strSQL )
  rsDLK.MoveFirst
  DLookup = rsDLK.Fields(0)
  rsDLK.Close

End Function
If I am anywhere close to right, then imagine what it would look like for you to put SQL into that function as the domain argument. The substitution would look ugly.

Now if you wanted to test this and see if it is really THAT simple, then I believe you MIGHT be able to use SQL for the domain if you enclose the entire SQL string in parentheses so as to make it a sub-query. But to be honest, I wouldn't hold my breath. I'm not going to bet that it would work because I'm SURE it can't be THAT simple.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Thales750 (05-19-2018)
Old 05-17-2018, 04:01 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,271
Thanks: 47
Thanked 1,817 Times in 1,767 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: strSQL in DLookup

@Thales750,

Can you give us more info on the basis/rationale for your question?

I seem to recall (that's dangerous these days) someone/article or a youtube saying that the domain of a DLookup (or possibly other domain aggregate functions) could be an SQL statement. Having said that I can not find any reference to same.

I agree with Doc's comment- definitely no, with hint of maybe in case it ends up being possible- even if rare. (A poor summary of his answer, but I think that's the bottom line.)

If an SQL statement as domain were readily possible, you would think somewhere on the great Google/Bing we'd find some examples.

Update: As usual after posting I did find this older post with same question, but solution was to use a different construct.
__________________

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

Last edited by jdraw; 05-17-2018 at 04:07 AM.
jdraw is offline   Reply With Quote
Old 05-17-2018, 05:01 AM   #6
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: strSQL in DLookup

Quote:
Originally Posted by jdraw View Post
I seem to recall (that's dangerous these days) someone/article or a youtube saying that the domain of a DLookup (or possibly other domain aggregate functions) could be an SQL statement. Having said that I can not find any reference to same.
I seem to recall suggesting it myself a few months back. I was wrong.
Table or query only
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.


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

New example databases:
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.
ridders is offline   Reply With Quote
Old 05-17-2018, 05:13 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,941
Thanks: 13
Thanked 1,318 Times in 1,257 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: strSQL in DLookup

If you want to use a query string, you don't need a domain function. Just make the query return the field you want it to, or better yet, if you do it yourself, the query can return multiple fields.

Domain functions are intended for use in places where you want a single value from a different table or query. They are frequently misused in queries where they run a separate query for each row of the domain. So if your main query returns 2,000 rows, you are actually running 2,001 queries. It gets very expensive very fast as most people eventually discover.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-19-2018, 06:45 AM   #8
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,597
Thanks: 36
Thanked 27 Times in 27 Posts
Thales750 is on a distinguished road
Re: strSQL in DLookup

Thanks all,

I try to use as much code as I can these days, with QueryDefs running in the thousands.

I guess this is just another example of why you should use code whenever possible. Some things require a query.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 05-19-2018, 02:01 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,941
Thanks: 13
Thanked 1,318 Times in 1,257 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: strSQL in DLookup

Actually, my position is that it is best to write as little code as possible. I've written my million lines of code so I don't need the practice nor do I have any illusion that code I write is somehow better than code written by Microsoft which has been optimized and tested millions of times. It is much more efficient to use action queries and property settings than to write custom code to do things that Access will do for you for "free". Save your efforts for the things Access cannot do for you.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
NauticalGent (05-19-2018)
Old 05-19-2018, 08:11 PM   #10
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,598
Thanks: 41
Thanked 1,020 Times in 930 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: strSQL in DLookup

I'm with Pat on this. Sometimes in my big project, I HAD to write the code first. But eventually I figured out a set of gyrations that allowed me to convert VBA recordset code to a reasonable sequence of SQL action queries. As bizarre as it sounds, I had to go into deriving some logic equations to prove my point (to myself) that SQL could do what was required. But it paid off in a 30:1 speed improvement on the simple stuff and even better on some of the more complex stuff. Granted, the more complex stuff took more than one query - but the difference was worth it.

Long term, if there is even the slightest chance to make an SQL statement out of something, I will eventually go there even if at first I don't happen to see the way through the convolutions via an SQL path.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 05-20-2018, 12:01 AM   #11
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,092
Thanks: 68
Thanked 1,371 Times in 1,293 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: strSQL in DLookup

Add me to the list of those who say do it in queries rather than code. A stored query is a lot faster than running code.

I do wonder with "thousands of querydefs" if you are perhaps not making as much use of bound forms as you could be.
Galaxiom is offline   Reply With Quote
Old 05-20-2018, 05:17 AM   #12
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,597
Thanks: 36
Thanked 27 Times in 27 Posts
Thales750 is on a distinguished road
Re: strSQL in DLookup

Quote:
Originally Posted by Galaxiom View Post
Add me to the list of those who say do it in queries rather than code. A stored query is a lot faster than running code.

I do wonder with "thousands of querydefs" if you are perhaps not making as much use of bound forms as you could be.
Thousands was an exaggeration. But hundreds anyway. A number that was dramatically reduced by using code.

There are many advantages to having a balance between querydefs and code.

The only bound forms I use are for Continuous Forms, and those have very limited editing capabilities. all popups and data entry is done with unbound. It takes longer to build them but you have complete control over when records are created and have much more flexibility to conform to business rules.

We bind some forms to give a source to controls, and then use code to assign new data. Often this is triggered by OpenArgs values or Public Functions.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 05-20-2018, 05:28 AM   #13
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,597
Thanks: 36
Thanked 27 Times in 27 Posts
Thales750 is on a distinguished road
Re: strSQL in DLookup

Quote:
Originally Posted by Pat Hartman View Post
Actually, my position is that it is best to write as little code as possible. I've written my million lines of code so I don't need the practice nor do I have any illusion that code I write is somehow better than code written by Microsoft which has been optimized and tested millions of times. It is much more efficient to use action queries and property settings than to write custom code to do things that Access will do for you for "free". Save your efforts for the things Access cannot do for you.
Yes and no. DAO code is more flexible than a query. Allen Browne made a code converter which was later modified by Gina Whipp that makes writing SQL code a no brainer.

SQL2VBA.accdb
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus

Last edited by Thales750; 05-20-2018 at 05:33 AM.
Thales750 is offline   Reply With Quote
Old 05-20-2018, 05:53 AM   #14
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,914
Thanks: 75
Thanked 1,216 Times in 1,133 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: strSQL in DLookup

Quote:
Originally Posted by Thales750 View Post
Yes and no. DAO code is more flexible than a query. Allen Browne made a code converter which was later modified by Gina Whipp that makes writing SQL code a no brainer.

Attachment 70813
Nobody is suggesting that VBA code should be avoided where it is needed.
There are many occasions where a query can't do the job.
However if a query can do so, it should be faster/more efficient due to optimisation

I've not seen Gina's version of Allen Browne's utility before but I created my own which does a bit more still.
See SQL to VBA and back again
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.


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

New example databases:
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.
ridders is offline   Reply With Quote
Old 05-20-2018, 06:06 AM   #15
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,597
Thanks: 36
Thanked 27 Times in 27 Posts
Thales750 is on a distinguished road
Re: strSQL in DLookup

Quote:
Originally Posted by ridders View Post
Nobody is suggesting that VBA code should be avoided where it is needed.
There are many occasions where a query can't do the job.
However if a query can do so, it should be faster/more efficient due to optimisation

I've not seen Gina's version of Allen Browne's utility before but I created my own which does a bit more still.
See SQL to VBA and back again
This is Colin?
I know no one is saying that. Gina's version added action Queries. Yours converts it back again? that's cool.
I usually just run it through the debug to get it back to queryDef format.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 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
dcount - strsql sven2 Modules & VBA 13 11-29-2007 10:53 AM
strSQL with WHERE edojanssen Forms 4 09-04-2007 02:23 AM
strSQL doubt EL-g Modules & VBA 5 11-09-2006 06:04 AM
DoCmd.run(strSQL) vs. CurrentDb.Execute (strSQL) Willem! Modules & VBA 2 06-14-2006 11:09 PM
[SOLVED] strSQL Rich Forms 0 02-21-2001 11:37 AM




All times are GMT -8. The time now is 11:57 PM.


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

Sponsored Links

How to advertise

Media Kit


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