showing result of SQL in a control

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Loralee - 04 Jan 2007 04:25 GMT

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,

Signature

Loralee

Reply to this Message

John Nurick - 04 Jan 2007 07:09 GMT

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
[quoted text clipped - 8 lines]
>
>Thanks,

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message