Hello all,

How can I display the results of SQL in VB. Here is the SQL and it will
have multiple records.

vSql = "select count(*), area_id from wcs_to_via_t " & _
"where trans_stt = '00' " & _
"and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
"group by area_id " & _
"Order by area_id, count(*)"

DoCmd.RunSQL (vSql)

RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ... INTO or
DELETE)

You would have to create a query and run the query:

Dim qdfTemp As DAO.QueryDef

Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
DoCmd.OpenQuery "qryTemp"

Note that qryTemp cannot already exist in this case. If this is something
you're going to do repeatedly, you're best off creating a "permanent" query
object, and just resetting its SQL property as required:

Dim qdfTemp As DAO.QueryDef

Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
qdfTemp.SQL = vSQL
DoCmd.OpenQuery "qryTemp"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mark" <(E-Mail Removed)> wrote in message
news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
> Hello all,
>
> How can I display the results of SQL in VB. Here is the SQL and it will
> have multiple records.
>
> vSql = "select count(*), area_id from wcs_to_via_t " & _
> "where trans_stt = '00' " & _
> "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
> "group by area_id " & _
> "Order by area_id, count(*)"
>
> DoCmd.RunSQL (vSql)

It is something that will run quite frequent. How can I pass by variables to
the query?

"Douglas J Steele" wrote:

> RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ... INTO or
> DELETE)
>
> You would have to create a query and run the query:
>
> Dim qdfTemp As DAO.QueryDef
>
> Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
> DoCmd.OpenQuery "qryTemp"
>
> Note that qryTemp cannot already exist in this case. If this is something
> you're going to do repeatedly, you're best off creating a "permanent" query
> object, and just resetting its SQL property as required:
>
> Dim qdfTemp As DAO.QueryDef
>
> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> qdfTemp.SQL = vSQL
> DoCmd.OpenQuery "qryTemp"
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Mark" <(E-Mail Removed)> wrote in message
> news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
> > Hello all,
> >
> > How can I display the results of SQL in VB. Here is the SQL and it will
> > have multiple records.
> >
> > vSql = "select count(*), area_id from wcs_to_via_t " & _
> > "where trans_stt = '00' " & _
> > "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
> > "group by area_id " & _
> > "Order by area_id, count(*)"
> >
> > DoCmd.RunSQL (vSql)
>
>
>

Notice that I specified vSql in my code, the same variable name that you
were assigning the SQL string to. Simply continue assigning vSql the way you
have been.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Mark" <(E-Mail Removed)> wrote in message
newsE8A202D-67AC-46F3-9741-(E-Mail Removed)...
> It is something that will run quite frequent. How can I pass by variables
> to
> the query?
>
> "Douglas J Steele" wrote:
>
>> RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ... INTO
>> or
>> DELETE)
>>
>> You would have to create a query and run the query:
>>
>> Dim qdfTemp As DAO.QueryDef
>>
>> Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
>> DoCmd.OpenQuery "qryTemp"
>>
>> Note that qryTemp cannot already exist in this case. If this is something
>> you're going to do repeatedly, you're best off creating a "permanent"
>> query
>> object, and just resetting its SQL property as required:
>>
>> Dim qdfTemp As DAO.QueryDef
>>
>> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
>> qdfTemp.SQL = vSQL
>> DoCmd.OpenQuery "qryTemp"
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Mark" <(E-Mail Removed)> wrote in message
>> news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
>> > Hello all,
>> >
>> > How can I display the results of SQL in VB. Here is the SQL and it
>> > will
>> > have multiple records.
>> >
>> > vSql = "select count(*), area_id from wcs_to_via_t " & _
>> > "where trans_stt = '00' " & _
>> > "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
>> > "group by area_id " & _
>> > "Order by area_id, count(*)"
>> >
>> > DoCmd.RunSQL (vSql)
>>
>>
>>

I'm wondering, if you repeatedly rewrite the SQL, the query would not
be optimized. Wouldn't it be better to use parameters?

SQL:

select count(*), area_id from wcs_to_via_t "where trans_stt = '00' and
dtimecre Between [SDate] and [EDate] group by area_id Order by area_id,
count(*)


VB Code:

Dim dbs As DAO.Database
Dim rst As DAO.QueryDef
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryTemp")
qdf.Parameters("sDate") = vSDate
qdf.Parameters("eDate") = vEDate
Set rst = qdf.OpenRecordset



Chris Nebinger

Dim

Douglas,

Thanks for getting this going. I have one more question.

I'm wanting the results to be displayed on a sub report in a form. How can I
get the results to display in the sub report? I'm using the:

If this is something
> >> you're going to do repeatedly, you're best off creating a "permanent"
> >> query
> >> object, and just resetting its SQL property as required:
> >>
> >> Dim qdfTemp As DAO.QueryDef
> >>
> >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> >> qdfTemp.SQL = vSQL
> >> DoCmd.OpenQuery "qryTemp"


"Douglas J. Steele" wrote:

> Notice that I specified vSql in my code, the same variable name that you
> were assigning the SQL string to. Simply continue assigning vSql the way you
> have been.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Mark" <(E-Mail Removed)> wrote in message
> newsE8A202D-67AC-46F3-9741-(E-Mail Removed)...
> > It is something that will run quite frequent. How can I pass by variables
> > to
> > the query?
> >
> > "Douglas J Steele" wrote:
> >
> >> RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ... INTO
> >> or
> >> DELETE)
> >>
> >> You would have to create a query and run the query:
> >>
> >> Dim qdfTemp As DAO.QueryDef
> >>
> >> Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
> >> DoCmd.OpenQuery "qryTemp"
> >>
> >> Note that qryTemp cannot already exist in this case. If this is something
> >> you're going to do repeatedly, you're best off creating a "permanent"
> >> query
> >> object, and just resetting its SQL property as required:
> >>
> >> Dim qdfTemp As DAO.QueryDef
> >>
> >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> >> qdfTemp.SQL = vSQL
> >> DoCmd.OpenQuery "qryTemp"
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Mark" <(E-Mail Removed)> wrote in message
> >> news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
> >> > Hello all,
> >> >
> >> > How can I display the results of SQL in VB. Here is the SQL and it
> >> > will
> >> > have multiple records.
> >> >
> >> > vSql = "select count(*), area_id from wcs_to_via_t " & _
> >> > "where trans_stt = '00' " & _
> >> > "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
> >> > "group by area_id " & _
> >> > "Order by area_id, count(*)"
> >> >
> >> > DoCmd.RunSQL (vSql)
> >>
> >>
> >>
>
>
>

That's a completely separate request!

You don't actually want to open a query. Instead, you want to set the
RecordSource for the subform to your query.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mark" <(E-Mail Removed)> wrote in message
news:19E235D3-9BFE-452E-8FB6-(E-Mail Removed)...
> Douglas,
>
> Thanks for getting this going. I have one more question.
>
> I'm wanting the results to be displayed on a sub report in a form. How can
I
> get the results to display in the sub report? I'm using the:
>
> If this is something
> > >> you're going to do repeatedly, you're best off creating a "permanent"
> > >> query
> > >> object, and just resetting its SQL property as required:
> > >>
> > >> Dim qdfTemp As DAO.QueryDef
> > >>
> > >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> > >> qdfTemp.SQL = vSQL
> > >> DoCmd.OpenQuery "qryTemp"
>
>
> "Douglas J. Steele" wrote:
>
> > Notice that I specified vSql in my code, the same variable name that you
> > were assigning the SQL string to. Simply continue assigning vSql the way
you
> > have been.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no private e-mails, please)
> >
> >
> > "Mark" <(E-Mail Removed)> wrote in message
> > newsE8A202D-67AC-46F3-9741-(E-Mail Removed)...
> > > It is something that will run quite frequent. How can I pass by
variables
> > > to
> > > the query?
> > >
> > > "Douglas J Steele" wrote:
> > >
> > >> RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ...
INTO
> > >> or
> > >> DELETE)
> > >>
> > >> You would have to create a query and run the query:
> > >>
> > >> Dim qdfTemp As DAO.QueryDef
> > >>
> > >> Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
> > >> DoCmd.OpenQuery "qryTemp"
> > >>
> > >> Note that qryTemp cannot already exist in this case. If this is
something
> > >> you're going to do repeatedly, you're best off creating a "permanent"
> > >> query
> > >> object, and just resetting its SQL property as required:
> > >>
> > >> Dim qdfTemp As DAO.QueryDef
> > >>
> > >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> > >> qdfTemp.SQL = vSQL
> > >> DoCmd.OpenQuery "qryTemp"
> > >>
> > >> --
> > >> Doug Steele, Microsoft Access MVP
> > >> http://I.Am/DougSteele
> > >> (no e-mails, please!)
> > >>
> > >>
> > >> "Mark" <(E-Mail Removed)> wrote in message
> > >> news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
> > >> > Hello all,
> > >> >
> > >> > How can I display the results of SQL in VB. Here is the SQL and it
> > >> > will
> > >> > have multiple records.
> > >> >
> > >> > vSql = "select count(*), area_id from wcs_to_via_t " & _
> > >> > "where trans_stt = '00' " & _
> > >> > "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
> > >> > "group by area_id " & _
> > >> > "Order by area_id, count(*)"
> > >> >
> > >> > DoCmd.RunSQL (vSql)
> > >>
> > >>
> > >>
> >
> >
> >

Well, maybe I need I a little of both. I have a form that has a selection
shift, or hour. Then they select the date from a calendar control. In my vb
if use these fields and format the date for the query. I would then like to
have the results display in a pane on the form. The pane could be a text
field, or even a sub report.

The problem with using a sub report is it populates when the form is first
opened.

I have used recordsets before and populated it by reading line by line and
putting it in a text field. But, I was hoping that there was a better way.


"Douglas J Steele" wrote:

> That's a completely separate request!
>
> You don't actually want to open a query. Instead, you want to set the
> RecordSource for the subform to your query.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Mark" <(E-Mail Removed)> wrote in message
> news:19E235D3-9BFE-452E-8FB6-(E-Mail Removed)...
> > Douglas,
> >
> > Thanks for getting this going. I have one more question.
> >
> > I'm wanting the results to be displayed on a sub report in a form. How can
> I
> > get the results to display in the sub report? I'm using the:
> >
> > If this is something
> > > >> you're going to do repeatedly, you're best off creating a "permanent"
> > > >> query
> > > >> object, and just resetting its SQL property as required:
> > > >>
> > > >> Dim qdfTemp As DAO.QueryDef
> > > >>
> > > >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> > > >> qdfTemp.SQL = vSQL
> > > >> DoCmd.OpenQuery "qryTemp"
> >
> >
> > "Douglas J. Steele" wrote:
> >
> > > Notice that I specified vSql in my code, the same variable name that you
> > > were assigning the SQL string to. Simply continue assigning vSql the way
> you
> > > have been.
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no private e-mails, please)
> > >
> > >
> > > "Mark" <(E-Mail Removed)> wrote in message
> > > newsE8A202D-67AC-46F3-9741-(E-Mail Removed)...
> > > > It is something that will run quite frequent. How can I pass by
> variables
> > > > to
> > > > the query?
> > > >
> > > > "Douglas J Steele" wrote:
> > > >
> > > >> RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT ...
> INTO
> > > >> or
> > > >> DELETE)
> > > >>
> > > >> You would have to create a query and run the query:
> > > >>
> > > >> Dim qdfTemp As DAO.QueryDef
> > > >>
> > > >> Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
> > > >> DoCmd.OpenQuery "qryTemp"
> > > >>
> > > >> Note that qryTemp cannot already exist in this case. If this is
> something
> > > >> you're going to do repeatedly, you're best off creating a "permanent"
> > > >> query
> > > >> object, and just resetting its SQL property as required:
> > > >>
> > > >> Dim qdfTemp As DAO.QueryDef
> > > >>
> > > >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> > > >> qdfTemp.SQL = vSQL
> > > >> DoCmd.OpenQuery "qryTemp"
> > > >>
> > > >> --
> > > >> Doug Steele, Microsoft Access MVP
> > > >> http://I.Am/DougSteele
> > > >> (no e-mails, please!)
> > > >>
> > > >>
> > > >> "Mark" <(E-Mail Removed)> wrote in message
> > > >> news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
> > > >> > Hello all,
> > > >> >
> > > >> > How can I display the results of SQL in VB. Here is the SQL and it
> > > >> > will
> > > >> > have multiple records.
> > > >> >
> > > >> > vSql = "select count(*), area_id from wcs_to_via_t " & _
> > > >> > "where trans_stt = '00' " & _
> > > >> > "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" & _
> > > >> > "group by area_id " & _
> > > >> > "Order by area_id, count(*)"
> > > >> >
> > > >> > DoCmd.RunSQL (vSql)
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>

You can not set the Recordsource property for the subform (i.e.: set the
property to blank), so that it doesn't load when the parent form is loaded,
and then set the property back to what it should be once you're ready to
have the subform populated.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mark" <(E-Mail Removed)> wrote in message
news:0084A888-5060-4076-B292-(E-Mail Removed)...
> Well, maybe I need I a little of both. I have a form that has a selection
> shift, or hour. Then they select the date from a calendar control. In my
vb
> if use these fields and format the date for the query. I would then like
to
> have the results display in a pane on the form. The pane could be a text
> field, or even a sub report.
>
> The problem with using a sub report is it populates when the form is first
> opened.
>
> I have used recordsets before and populated it by reading line by line and
> putting it in a text field. But, I was hoping that there was a better
way.
>
>
> "Douglas J Steele" wrote:
>
> > That's a completely separate request!
> >
> > You don't actually want to open a query. Instead, you want to set the
> > RecordSource for the subform to your query.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Mark" <(E-Mail Removed)> wrote in message
> > news:19E235D3-9BFE-452E-8FB6-(E-Mail Removed)...
> > > Douglas,
> > >
> > > Thanks for getting this going. I have one more question.
> > >
> > > I'm wanting the results to be displayed on a sub report in a form. How
can
> > I
> > > get the results to display in the sub report? I'm using the:
> > >
> > > If this is something
> > > > >> you're going to do repeatedly, you're best off creating a
"permanent"
> > > > >> query
> > > > >> object, and just resetting its SQL property as required:
> > > > >>
> > > > >> Dim qdfTemp As DAO.QueryDef
> > > > >>
> > > > >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> > > > >> qdfTemp.SQL = vSQL
> > > > >> DoCmd.OpenQuery "qryTemp"
> > >
> > >
> > > "Douglas J. Steele" wrote:
> > >
> > > > Notice that I specified vSql in my code, the same variable name that
you
> > > > were assigning the SQL string to. Simply continue assigning vSql the
way
> > you
> > > > have been.
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no private e-mails, please)
> > > >
> > > >
> > > > "Mark" <(E-Mail Removed)> wrote in message
> > > > newsE8A202D-67AC-46F3-9741-(E-Mail Removed)...
> > > > > It is something that will run quite frequent. How can I pass by
> > variables
> > > > > to
> > > > > the query?
> > > > >
> > > > > "Douglas J Steele" wrote:
> > > > >
> > > > >> RunSQL is only for Action queries (INSERT INTO, UPDATE, SELECT
....
> > INTO
> > > > >> or
> > > > >> DELETE)
> > > > >>
> > > > >> You would have to create a query and run the query:
> > > > >>
> > > > >> Dim qdfTemp As DAO.QueryDef
> > > > >>
> > > > >> Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", vSql)
> > > > >> DoCmd.OpenQuery "qryTemp"
> > > > >>
> > > > >> Note that qryTemp cannot already exist in this case. If this is
> > something
> > > > >> you're going to do repeatedly, you're best off creating a
"permanent"
> > > > >> query
> > > > >> object, and just resetting its SQL property as required:
> > > > >>
> > > > >> Dim qdfTemp As DAO.QueryDef
> > > > >>
> > > > >> Set qdfTemp = CurrentDb.QueryDefs("qryTemp")
> > > > >> qdfTemp.SQL = vSQL
> > > > >> DoCmd.OpenQuery "qryTemp"
> > > > >>
> > > > >> --
> > > > >> Doug Steele, Microsoft Access MVP
> > > > >> http://I.Am/DougSteele
> > > > >> (no e-mails, please!)
> > > > >>
> > > > >>
> > > > >> "Mark" <(E-Mail Removed)> wrote in message
> > > > >> news:AB19E00B-0D2A-4209-AEB7-(E-Mail Removed)...
> > > > >> > Hello all,
> > > > >> >
> > > > >> > How can I display the results of SQL in VB. Here is the SQL
and it
> > > > >> > will
> > > > >> > have multiple records.
> > > > >> >
> > > > >> > vSql = "select count(*), area_id from wcs_to_via_t " & _
> > > > >> > "where trans_stt = '00' " & _
> > > > >> > "and dtimecre Between #" & vSDate & "# and #" & vEDate & "#" &
_
> > > > >> > "group by area_id " & _
> > > > >> > "Order by area_id, count(*)"
> > > > >> >
> > > > >> > DoCmd.RunSQL (vSql)
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >