VB - Display SQL Statement from Query

[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

 

[Alex Dybenko]

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

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

 

[Michael Kintner]

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]

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

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,
try:
debug.print currentdb.querydefs("qry-ShowData").SQL

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

 

[Pieter Wijnen]

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

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

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,
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