showing result of SQL in a control

[Guest]

I'm trying to find a way to show the results of several queries in a form and
then a report. (I have a "laundry list" of "results" that I need to produce
and want to put as many together as possible. Because of the number of joins
involved and the amount of time it would take to use DCount, DSUM, etc. I
want to use a series of SQLs to fetch the results and display in a text box.

I tried txtTxtbox.text = docmd.runSQL " SELECT count[rest of statement goes
here]"
and that did not work. Could someone point me in the right direction. The
books I have here are on no help in the matter....

Thanks,

 

[John Nurick]

Hi Loralee,

You can't use DoCmd.RunSQL or DoCmd.Execute that way because they don't
return a value. You need to use VBA code to open a recordset with the
SQL statement and extract the data you need (which is more or less that
DLookup() etc. do).

Allen Browne's ELookup() function at http://allenbrowne.com/ser-42.html
does exactly that. It's much more flexible and rather faster than the
"D" functions. For instance, if the SQL is

SELECT COUNT(F) FROM T WHERE A<100;

you could use

txtCount.Value = ELookup("COUNT(F)", "T", "A<100")

I'm trying to find a way to show the results of several queries in a form and
then a report. (I have a "laundry list" of "results" that I need to produce
and want to put as many together as possible. Because of the number of joins
involved and the amount of time it would take to use DCount, DSUM, etc. I
want to use a series of SQLs to fetch the results and display in a text box.

I tried txtTxtbox.text = docmd.runSQL " SELECT count[rest of statement goes
here]"
and that did not work. Could someone point me in the right direction. The
books I have here are on no help in the matter....

Thanks,