VB - Display SQL Statement from Query

  • Thread starter Thread starter Michael Kintner
  • Start date Start date
M

Michael Kintner

I have a query which displays contents in a grid. The SourceObject Property
is "Query.qry-ShowData". How can I return the actual SQL statement used by
this query? The reason is this query pulls contents from different forms
to create the WHERE statement and I would like to see the actual SQL
statement used by the "Query.qry-ShowData".

Thank you in advance for your help.

Mike
 
Thanks works however it does not show the values it pulled from the forms.

Example from Debug shows: SELECT DISTINCT [qry-Stock_LOC].*
FROM [qry-Stock_LOC]
WHERE ((([qry-Stock_LOC].PRODUCT_CD) Like
[Forms]![frmCYCLE_BUILD]![PRODUCT_CODE]) AND
(([qry-Stock_LOC].ITEM_PFX)=[Forms]![frmCYCLE_BUILD]![PFX]));

I would like it to return:
SELECT DISTINCT [qry-Stock_LOC].*
FROM [qry-Stock_LOC]
WHERE ((([qry-Stock_LOC].PRODUCT_CD) Like '69*' AND
(([qry-Stock_LOC].ITEM_PFX)='1'));

It shows the actual values used to show the data.

How can I get it to show the actual SQL statement of the grid???

thank you in advance,
Mike
 
Hi,
you can't get that value from query, Access evaluates these parameters when
it process query. So you can do the same - get parameter expression from
query sql and then Eval() them. but this require some coding.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Michael Kintner said:
Thanks works however it does not show the values it pulled from the forms.

Example from Debug shows: SELECT DISTINCT [qry-Stock_LOC].*
FROM [qry-Stock_LOC]
WHERE ((([qry-Stock_LOC].PRODUCT_CD) Like
[Forms]![frmCYCLE_BUILD]![PRODUCT_CODE]) AND
(([qry-Stock_LOC].ITEM_PFX)=[Forms]![frmCYCLE_BUILD]![PFX]));

I would like it to return:
SELECT DISTINCT [qry-Stock_LOC].*
FROM [qry-Stock_LOC]
WHERE ((([qry-Stock_LOC].PRODUCT_CD) Like '69*' AND
(([qry-Stock_LOC].ITEM_PFX)='1'));

It shows the actual values used to show the data.

How can I get it to show the actual SQL statement of the grid???

thank you in advance,
Mike

Alex Dybenko said:
Hi,
try:
debug.print currentdb.querydefs("qry-ShowData").SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
Click to expand...
Click to expand...
 
Simple Example

Dim Qdef As DAO.QueryDef
Dim Prm As DAO.Parameter

Set Db = Access.CurrentDb.QueryDefs("MyParamQuery")
For Each Prm In Qdef.Parameters
With Prm
Debug.Print .Name, Access.Eval(.Value)
End With
Next
Qdef.Close: Set Qdef = Nothing


Pieter


Alex Dybenko said:
Hi,
you can't get that value from query, Access evaluates these parameters
when it process query. So you can do the same - get parameter expression
from query sql and then Eval() them. but this require some coding.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Michael Kintner said:
Thanks works however it does not show the values it pulled from the
forms.

Example from Debug shows: SELECT DISTINCT [qry-Stock_LOC].*
FROM [qry-Stock_LOC]
WHERE ((([qry-Stock_LOC].PRODUCT_CD) Like
[Forms]![frmCYCLE_BUILD]![PRODUCT_CODE]) AND
(([qry-Stock_LOC].ITEM_PFX)=[Forms]![frmCYCLE_BUILD]![PFX]));

I would like it to return:
SELECT DISTINCT [qry-Stock_LOC].*
FROM [qry-Stock_LOC]
WHERE ((([qry-Stock_LOC].PRODUCT_CD) Like '69*' AND
(([qry-Stock_LOC].ITEM_PFX)='1'));

It shows the actual values used to show the data.

How can I get it to show the actual SQL statement of the grid???

thank you in advance,
Mike

Alex Dybenko said:
Hi,
try:
debug.print currentdb.querydefs("qry-ShowData").SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I have a query which displays contents in a grid. The SourceObject
Property is "Query.qry-ShowData". How can I return the actual SQL
statement used by this query? The reason is this query pulls contents
from different forms to create the WHERE statement and I would like to
see the actual SQL statement used by the "Query.qry-ShowData".

Thank you in advance for your help.

Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top