strSQL in DLookup (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
If possible to use a SQL Statement in a DLookup.
(not a DLookup in a Query)

How would that be written?

Thanks all.
 

Ranman256

Well-known member
Local time
Today, 17:04
Joined
Apr 9, 2015
Messages
4,339
No need....the Dlookup IS an SQL.
Either use a table or a query. (Not SQL)
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Jan 23, 2006
Messages
15,364
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));
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:04
Joined
Feb 28, 2001
Messages
27,001
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Jan 23, 2006
Messages
15,364
@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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 21:04
Joined
Jan 14, 2017
Messages
18,186
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Feb 19, 2002
Messages
42,976
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.
 

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Feb 19, 2002
Messages
42,976
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:04
Joined
Feb 28, 2001
Messages
27,001
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:04
Joined
Jan 20, 2009
Messages
12,849
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.
 

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
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.
 

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
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.

View attachment SQL2VBA.accdb
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 21:04
Joined
Jan 14, 2017
Messages
18,186
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.

View 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
 

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
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.
 

isladogs

MVP / VIP
Local time
Today, 21:04
Joined
Jan 14, 2017
Messages
18,186
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.

You sound surprised....
I'm not the Essex troll if you were wondering.
My version also allows you to paste the SQL into a query.

Where I do disagree with you is mainly using unbound forms.
As you say they are much more work and IMHO are rarely worth all that extra effort.
 
Last edited:

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
You sound surprised....
I'm not the Essex troll if you were wondering.
My version also allows you to paste the SQL into a query.

Where I do disagree with you is mainly using inbound forms.
As you say they are much more work and IMHO are rarely worth all that extra effort.

I always like that old Troll. He was one of my favorite people.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:04
Joined
Jan 20, 2009
Messages
12,849
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.

When editing in the popup forms, do you check that the record you have loaded has not been edited by another user since it was opened? Without that you could be overwriting the changes made by another user.

Bound forms do this testing for you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Feb 19, 2002
Messages
42,976
The only bound forms I use are for Continuous Forms,
Access is a Rapid Application Development (RAD) tool. The point of using a RAD tool is to let it do stuff for you. By using unbound forms, you are burdened with all the baggage of using Access without the biggest benefit. If you understand how form events work, you have all the control you need. Writing all that management code yourself isn't better than letting Access do it for you. Validation code is one place where you do need to drop into VBA, rarely would you need queries or property settings to validate. You just need to understand which events to use for which purpose and if you are working with ODBC, you need to understand how Access works with ODBC so yo make the bound form as efficient as it can be.
 
Last edited:

Thales750

Formerly Jsanders
Local time
Today, 17:04
Joined
Dec 20, 2007
Messages
2,061
When editing in the popup forms, do you check that the record you have loaded has not been edited by another user since it was opened? Without that you could be overwriting the changes made by another user.

Bound forms do this testing for you.

In cases where editing could be done by multiple Users, than I would use bound popups.

In Data Entry mode, rarely would I use bound popups. Using DAO code allows for data to be pulled in from many locations. This enhances flexibility, impossible to achieve using bound forms.

I'm not saying bound Forms don't have their place, but they are extremely limited compared to unbound. I think many users are frustrated by database deign because developers don't truly map out business requirements. They just push on the Users standard almost fitting solutions.
 

Users who are viewing this thread

Top Bottom