strSQL in DLookup

[Thales750]

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

How would that be written?

Thanks all.

 

[Ranman256]

No need....the Dlookup IS an SQL.
Either use a table or a query. (Not SQL)

 

[jdraw]

Not sure what underlying requirement is.

Here is comment from techonthenet.

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]

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 >>>>

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]

@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.

 

[isladogs]

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

 

[Thales750]

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.

 

[The_Doc_Man]

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]

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]

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]

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

 

[isladogs]

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]

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]

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.

 

[Thales750]

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]

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.

 

[Thales750]

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.

 

[isladogs]

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.

I am finding it very difficult to think of examples where forms are limited or made inflexible as a result of being bound. Although many developers use one table per form, I don't subscribe to that view. Perhaps you could provide some examples to illustrate your assertions.

 

[Thales750]

I am finding it very difficult to think of examples where forms are limited or made inflexible as a result of being bound. Although many developers use one table per form, I don't subscribe to that view. Perhaps you could provide some examples to illustrate your assertions.

When you use a DAO RecordSet you can wait until the record set is inserted into a table to make decision about other programmatic events. It's 100% positive. No dirty forms, or maybe a delete or maybe a save record.

None of those things. And no missing record.

You can insert code directly in front of assigning a value to a field, that code could be a Select Statement, an If statement, a loop, whatever you might need to get the correct data.

Do you consider it a bound form if you have code to bind it at runtime?

 

[isladogs]

Pat replied before I read this - I agree with everything she wrote

I think you are labouring under a misapprehension about bound forms.
You are CERTAINLY labouring by creating masses of extra work using unbound forms