Generate Query Definition

[Chris Morris]

Hoping you can help.

I'd like to use DLookup, DCount etc. on a SQL string held within the VBA code itself. Something like:

stSQL = "SELECT * From Contacts"

MsgBox DCount("Contact_ID", stSQL, "Deceased = False")

I'm trying to avoid creating lots of queries.

 

[JHB]

Here is a link to the DCount function, (read the description under Domain arguments): https://support.office.microsoft.co...946-a8cc-2601825dbaaf&ui=en-US&rs=en-US&ad=US

 

[pr2-eugin]

Unless you save this Query as a Temporary Query, the only way to do is,

MsgBox CurrentDb.OpenRecordSet("SELECT Count(*) FROM (" & strSQL & ") As tmpQry WHERE tmpQry.Deceased = False").Fields(0)

 

[Chris Morris]

Thank you for your responses, I'll have another experiment.

C

 

[namliam]

Or perhaps something like:
MsgBox DCount("Contact_ID", "Contacts", "Deceased = False")

Dont overuse DLookups/Dcounts, queries usually are faster, particularly if you are next going to do
MsgBox DCount("Contact_ID", "Contacts", "Deceased = True")

and more Dcounts on Contacts.

Something like:

Select 
sum(iif(Deceased;1;0) ) Deceased,
sum(iif(Deceased;0;1) ) Living
From Contacts

Will be faster, much more faster if you get more and more dcounts/sums