diff between query and sql code?

[bbxrider]

in the example following here, it seems to be invoking the 'yourQuery'
but the sql code is doing that same thing only based on an index
lookup. so........
why is 'yourQuery' being invoked, why not open the table involved
up front and then run the sql?
i was trying to invoke a more complicated query that requires a user input
key to determine which data to look up and how to pass that key to the query
from a form

example:
getting some data based on a user input keyword and eventually displaying it
involved using a predefined query

Select field1, field2, field3 From yourTable
and it's called yourQuery

Now let's say the controls on your form are called:
txtField and txtKeyword

Now something like this code goes in the click event of your cmd button:
**air code**

Dim db As Database
Dim qdf As QueryDef
Dim strSql As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourQuery")
strSql = "Select field1, field2, field3 From yourTable " & _
"Where " & Me.txtField & " Like '*" & _
Me.txtKeyword & "*'"
'you might want to view the above to make sure
'it's syntax is correct
MsgBox strSql
qdf.SQL = strSql
'now the query that your report is
'based on has the chosen criteria
Set qdf = Nothing
Set db = Nothing

 

[Van T. Dinh]

I am not entirely sure I understood your question but ...

It looks like the RecordSource of your Report is the Query and the code
simply modifies the Query to add the criteria and then perhaps, open the
Report with the modified Query.

The whole purpose of the posted code is to modify the Query AFAICS.

HTH
Van T. Dinh
MVP (Access)

 

[bbxrider]

thanx for the reply
my question still is
if you have a query that is select1, select2, etc from some table
and
you have sql code that does select1, select2, etc from the same table based
on some
index/key value that is obtained via form input
why do you need the query at all?
the sql code does everything and more than the query?
and i don't understand afaics????

 

[Van T. Dinh]

Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the *Query
Name* is saved in the design of the Report) and then modify the Query in
code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before I open
the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.

 

[Dan Artuso]

Hi,
I'm the one who posted that code.
I don't know of you're the same person I was
dealing with, but in the original question, the intent
was to not only pass the parameter, but also the name
of the field to be used in the Where clause.
Hence the alteration of the SQL itself.

--
HTH
Dan Artuso, Access MVP

thanks again
i have used the parameter query myself but was looking to make a more
elegant and user friendly
way to get the 'input parameter' with a form and was trying to find a way to
get the 'parameter' via a
form and then invoke the query passing it the parameter and that code
example was suggested, but it
just doesn't make sense
the actual query calcs sums (or could do a lot more), see below

SELECT Sum([huprice]*[hqty]) AS [product sales], Sum([hucost]*[hqty]) AS
[product cost], sachrec02.ccode
FROM sachrec02
WHERE (((sachrec02.hdate) Between #1/1/2002# And #1/31/2002#) AND
((sachrec02.ccode)=[enter customer number]))
GROUP BY sachrec02.ccode;

what i want to to do is get the customer number 'parameter' from a form and
then run the query

Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the *Query
Name* is saved in the design of the Report) and then modify the Query in
code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before I open
the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.

 

[bbxrider]

hi thanx for the clarification, i'm not the original poster, so i was
looking at it from
a different perspective, and this is good info about being able to pass the
field name as
well.
but am still not sure about using existing queries vs/and sql code

if your not going to really 'use' the existing query code but a modified
version version created in the
sql stmt, why invoke it in the first place with the /Set qdf =
db.QueryDefs("yourQuery")/ statement??

if you have an existing parameter query, and want to pass it the parameter
info from a form, rather than having the query itself pose the prompt, can
that be done

Hi,
I'm the one who posted that code.
I don't know of you're the same person I was
dealing with, but in the original question, the intent
was to not only pass the parameter, but also the name
of the field to be used in the Where clause.
Hence the alteration of the SQL itself.

--
HTH
Dan Artuso, Access MVP

thanks again
i have used the parameter query myself but was looking to make a more
elegant and user friendly
way to get the 'input parameter' with a form and was trying to find a way to
get the 'parameter' via a
form and then invoke the query passing it the parameter and that code
example was suggested, but it
just doesn't make sense
the actual query calcs sums (or could do a lot more), see below

SELECT Sum([huprice]*[hqty]) AS [product sales], Sum([hucost]*[hqty]) AS
[product cost], sachrec02.ccode
FROM sachrec02
WHERE (((sachrec02.hdate) Between #1/1/2002# And #1/31/2002#) AND
((sachrec02.ccode)=[enter customer number]))
GROUP BY sachrec02.ccode;

what i want to to do is get the customer number 'parameter' from a form and
then run the query

Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the *Query
Name* is saved in the design of the Report) and then modify the Query in
code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before I open
the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.

--
HTH
Van T. Dinh
MVP (Access)



thanx for the reply
my question still is
if you have a query that is select1, select2, etc from some table
and
you have sql code that does select1, select2, etc from the same table
based
on some
index/key value that is obtained via form input
why do you need the query at all?
the sql code does everything and more than the query?
and i don't understand afaics????

 

[Dan Artuso]

Hi,
This statement:
Set qdf = db.QueryDefs("yourQuery")
does not 'invoke' the query.

It simply returns as object which represents the saved query.
From there you can modify it as you please.

for your second question, simply put:
Forms!yourForm!yourControl

as the criteria of the relevant field in your saved query.

--
HTH
Dan Artuso, Access MVP

hi thanx for the clarification, i'm not the original poster, so i was
looking at it from
a different perspective, and this is good info about being able to pass the
field name as
well.
but am still not sure about using existing queries vs/and sql code

if your not going to really 'use' the existing query code but a modified
version version created in the
sql stmt, why invoke it in the first place with the /Set qdf =
db.QueryDefs("yourQuery")/ statement??

if you have an existing parameter query, and want to pass it the parameter
info from a form, rather than having the query itself pose the prompt, can
that be done

Hi,
I'm the one who posted that code.
I don't know of you're the same person I was
dealing with, but in the original question, the intent
was to not only pass the parameter, but also the name
of the field to be used in the Where clause.
Hence the alteration of the SQL itself.

--
HTH
Dan Artuso, Access MVP

thanks again
i have used the parameter query myself but was looking to make a more
elegant and user friendly
way to get the 'input parameter' with a form and was trying to find a way to
get the 'parameter' via a
form and then invoke the query passing it the parameter and that code
example was suggested, but it
just doesn't make sense
the actual query calcs sums (or could do a lot more), see below

SELECT Sum([huprice]*[hqty]) AS [product sales], Sum([hucost]*[hqty]) AS
[product cost], sachrec02.ccode
FROM sachrec02
WHERE (((sachrec02.hdate) Between #1/1/2002# And #1/31/2002#) AND
((sachrec02.ccode)=[enter customer number]))
GROUP BY sachrec02.ccode;

what i want to to do is get the customer number 'parameter' from a form and
then run the query

Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the
*Query
Name* is saved in the design of the Report) and then modify the Query in
code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before I open
the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.

--
HTH
Van T. Dinh
MVP (Access)



thanx for the reply
my question still is
if you have a query that is select1, select2, etc from some table
and
you have sql code that does select1, select2, etc from the same table
based
on some
index/key value that is obtained via form input
why do you need the query at all?
the sql code does everything and more than the query?
and i don't understand afaics????

 

[bbxrider]

ok thanks again, just one more clarification then, i promise :<)

when you set qdf.sql = strSql you need to write the entire 'action' sql code
of the query, you can't just pick
and choose portions? right?
so using it like this, the instantiated (correct terminology?) query object
would supply a way to open all the relevant tables, field join properties
etc. without having to code that in the sql as well?

Hi,
This statement:
Set qdf = db.QueryDefs("yourQuery")
does not 'invoke' the query.

It simply returns as object which represents the saved query.
From there you can modify it as you please.

for your second question, simply put:
Forms!yourForm!yourControl

as the criteria of the relevant field in your saved query.

--
HTH
Dan Artuso, Access MVP

hi thanx for the clarification, i'm not the original poster, so i was
looking at it from
a different perspective, and this is good info about being able to pass the
field name as
well.
but am still not sure about using existing queries vs/and sql code

if your not going to really 'use' the existing query code but a modified
version version created in the
sql stmt, why invoke it in the first place with the /Set qdf =
db.QueryDefs("yourQuery")/ statement??

if you have an existing parameter query, and want to pass it the parameter
info from a form, rather than having the query itself pose the prompt, can
that be done

Hi,
I'm the one who posted that code.
I don't know of you're the same person I was
dealing with, but in the original question, the intent
was to not only pass the parameter, but also the name
of the field to be used in the Where clause.
Hence the alteration of the SQL itself.

--
HTH
Dan Artuso, Access MVP


thanks again
i have used the parameter query myself but was looking to make a more
elegant and user friendly
way to get the 'input parameter' with a form and was trying to find

a
way to

get the 'parameter' via a
form and then invoke the query passing it the parameter and that code
example was suggested, but it
just doesn't make sense
the actual query calcs sums (or could do a lot more), see below

SELECT Sum([huprice]*[hqty]) AS [product sales], Sum([hucost]*[hqty]) AS
[product cost], sachrec02.ccode
FROM sachrec02
WHERE (((sachrec02.hdate) Between #1/1/2002# And #1/31/2002#) AND
((sachrec02.ccode)=[enter customer number]))
GROUP BY sachrec02.ccode;

what i want to to do is get the customer number 'parameter' from a

form
and

then run the query

Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the
*Query
Name* is saved in the design of the Report) and then modify the

Query
in

code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before

I
open

the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.

--
HTH
Van T. Dinh
MVP (Access)



thanx for the reply
my question still is
if you have a query that is select1, select2, etc from some table
and
you have sql code that does select1, select2, etc from the same table
based
on some
index/key value that is obtained via form input
why do you need the query at all?
the sql code does everything and more than the query?
and i don't understand afaics????

 

[Dan Artuso]

Hi,
when you set qdf.sql = strSql you need to write the entire 'action' sql code

of the query, you can't just pick
and choose portions? right?

that's right

so using it like this, the instantiated (correct terminology?) query object

would supply a way to open all the relevant tables, field join properties
etc. without having to code that in the sql as well?

Hmm, not sure I understand what you're getting at.
Usually when you work with a QueryDef object it's because you
want to modify one or more of it's properties. You don't want to
'open' anything. For a complete list of what you can modify
check out the Object Browser. From within the VB IDE,
press F2. Then from the combo in the upper left, select the DAO library.
From the 'classes' listbox select QueryDef.
All the methods and properties will be listed.

--
HTH
Dan Artuso, Access MVP

ok thanks again, just one more clarification then, i promise :<)

when you set qdf.sql = strSql you need to write the entire 'action' sql code
of the query, you can't just pick
and choose portions? right?
so using it like this, the instantiated (correct terminology?) query object
would supply a way to open all the relevant tables, field join properties
etc. without having to code that in the sql as well?

Hi,
This statement:
Set qdf = db.QueryDefs("yourQuery")
does not 'invoke' the query.

It simply returns as object which represents the saved query.
From there you can modify it as you please.

for your second question, simply put:
Forms!yourForm!yourControl

as the criteria of the relevant field in your saved query.

--
HTH
Dan Artuso, Access MVP

hi thanx for the clarification, i'm not the original poster, so i was
looking at it from
a different perspective, and this is good info about being able to pass the
field name as
well.
but am still not sure about using existing queries vs/and sql code

if your not going to really 'use' the existing query code but a modified
version version created in the
sql stmt, why invoke it in the first place with the /Set qdf =
db.QueryDefs("yourQuery")/ statement??

if you have an existing parameter query, and want to pass it the parameter
info from a form, rather than having the query itself pose the prompt, can
that be done

Hi,
I'm the one who posted that code.
I don't know of you're the same person I was
dealing with, but in the original question, the intent
was to not only pass the parameter, but also the name
of the field to be used in the Where clause.
Hence the alteration of the SQL itself.

--
HTH
Dan Artuso, Access MVP


thanks again
i have used the parameter query myself but was looking to make a more
elegant and user friendly
way to get the 'input parameter' with a form and was trying to find a
way to
get the 'parameter' via a
form and then invoke the query passing it the parameter and that code
example was suggested, but it
just doesn't make sense
the actual query calcs sums (or could do a lot more), see below

SELECT Sum([huprice]*[hqty]) AS [product sales], Sum([hucost]*[hqty]) AS
[product cost], sachrec02.ccode
FROM sachrec02
WHERE (((sachrec02.hdate) Between #1/1/2002# And #1/31/2002#) AND
((sachrec02.ccode)=[enter customer number]))
GROUP BY sachrec02.ccode;

what i want to to do is get the customer number 'parameter' from a form
and
then run the query

message
Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the
*Query
Name* is saved in the design of the Report) and then modify the Query
in
code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before I
open
the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.

--
HTH
Van T. Dinh
MVP (Access)



thanx for the reply
my question still is
if you have a query that is select1, select2, etc from some table
and
you have sql code that does select1, select2, etc from the same
table
based
on some
index/key value that is obtained via form input
why do you need the query at all?
the sql code does everything and more than the query?
and i don't understand afaics????