VB - Display SQL Statement from Query

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 


0
Michael
10/9/2007 12:49:35 PM
access 16762 articles. 3 followers. Follow

4 Replies
636 Views

Similar Articles

[PageSpeed] 23

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

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

"Michael Kintner" <michaelkintner@nospam.com> wrote in message 
news:13gmu76sh305b@corp.supernews.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
> 

0
Alex
10/9/2007 1:09:37 PM
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" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message 
news:uktPmWnCIHA.1184@TK2MSFTNGP04.phx.gbl...
> Hi,
> try:
> debug.print currentdb.querydefs("qry-ShowData").SQL
>
> -- 
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
> "Michael Kintner" <michaelkintner@nospam.com> wrote in message 
> news:13gmu76sh305b@corp.supernews.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
>>
> 


0
Michael
10/9/2007 2:24:20 PM
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" <michaelkintner@nospam.com> wrote in message 
news:13gn3olbslb8k3a@corp.supernews.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
>
> "Alex Dybenko" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message 
> news:uktPmWnCIHA.1184@TK2MSFTNGP04.phx.gbl...
>> Hi,
>> try:
>> debug.print currentdb.querydefs("qry-ShowData").SQL
>>
>> -- 
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>> "Michael Kintner" <michaelkintner@nospam.com> wrote in message 
>> news:13gmu76sh305b@corp.supernews.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
>>>
>>
>
> 

0
Alex
10/9/2007 5:03:32 PM
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" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message 
news:%23fqwTZpCIHA.1212@TK2MSFTNGP05.phx.gbl...
> 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" <michaelkintner@nospam.com> wrote in message 
> news:13gn3olbslb8k3a@corp.supernews.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
>>
>> "Alex Dybenko" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message 
>> news:uktPmWnCIHA.1184@TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>> try:
>>> debug.print currentdb.querydefs("qry-ShowData").SQL
>>>
>>> -- 
>>> Best regards,
>>> ___________
>>> Alex Dybenko (MVP)
>>> http://accessblog.net
>>> http://www.PointLtd.com
>>>
>>> "Michael Kintner" <michaelkintner@nospam.com> wrote in message 
>>> news:13gmu76sh305b@corp.supernews.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
>>>>
>>>
>>
>>
> 


0
Pieter
10/9/2007 8:53:59 PM
Reply:

Similar Artilces:

Display As = File as & (address in parens)
I just moved all my contacts into a new .pst file (I'm using OL 2003) and something changed. I USED TO have "Display As" show only the person's name, and not the person's name PLUS the email address in parens as the default does. In other words, default would show "JOHN SMITH (johnsmith@aol.com)". But when I created all those entries, I changed them to show only "JOHN SMITH". In my new file, Outlook has changed all my "Display As" entries to the default: name AND email address. It's not how I want it, so I'm hoping there'...

SQL Server
I'm currently using MS-Access to write reports using a SQL Server db as the data source in a client/server arrangement. The database is sufficiently large (2gb) that data retrieval speed is an issue. The current setup is linked tables to the SQL Server db via ODBC, and then the usual queries. 1. Can views and/or stored procedures on the SQL Server side along with paramaterized queries provide a significant performance boost? 2. I've read that views and stored procedures can be created w/i Access. Is this the same as creating them in SQL Server? 3. Are there other things I sho...

SBS2008 and SQL 2005
Hello All, So here is my question, we have the SBS 2008 Premium, and all the installation media are sent to us. And that includes the SQL 2005 and 2008 installation disks. But after I installed the SBS 2008 on my first server which we also would like to turn it to SQL, I looked into the Program And Features and found SQL 2005 already installed. But I haven't really insert any SQL installation disk. So is that a valid copy of SQL or just some thing like SQL Express? Do I need to uninstall that and install from my SQL install disk or I can start to use that? Thanks! Ben T...

Money 2006 Investment Account balance display
I have two Investment Accounts (401k) in Money 2006 Deluxe. For some reason, Money stopped displaying the account totals on the Account List page. It used to show the account balances and the subtotal for all of my investment accounts. Additionally, I now notice that each account now has a second account with the same name, but has the word "Contributions" added in parenthesis. This seems to be new as well. The data in the accounts still seems alright. My investment subtotal on the Account list page shows a zero balance and there is no balance listed for the accounts the...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

STATEMENT DOWNLOAD PROBLEMS #2
Hi, I am trying to download statements and keep coming up with an error which says that "A communication error has occurred. Please try or call again." I have tried deleting and recreating the OFX file extension but this hasn't helped. Does anyone have any ideas? Thanks Caz In microsoft.public.money, Cazzy123 wrote: >I am trying to download statements and keep coming up with an error which >says that "A communication error has occurred. Please try or call again." I >have tried deleting and recreating the OFX file extension but this hasn't >he...

SOP Statements
Hello, We are using GP v 9.0. I have just recently taken on the task of sending out customer statements. I find that the User Definable Form has a great layout that I would like to send to our customers. However, our company name and address does not print on the form even though the option is marked when selecting the statement. Any suggestions on how to get the company information to print on the SOP Statement - User Definable Form? Thank you, Kristy If you're talking about the statements printed using Tools>Routines>Sales>Statements, then you'll need to modify t...

A SQL error has occurred. Going off line with Outlook CRM 4.
I have seen this in multiple workstations. Running the diagnostics, does not report any errors, however removing the temprary data under advenced fixes the problem by deleting the content of the off line database. The problem with this fix is that it may take up to 12 hours for some of our overseas employees to do their initial off line. When is SP1 due? ...

vb script for opening notepad
Hi, I am a newbie to vb...Can somebodyprovide me a simple vb script for opening notepad in vista using windows shell Thanks VB baby "Melvin" <whereismelvin@gmail.com> wrote in message news:41a15213-b062-41f5-8525-7eb95c68864a@s22g2000prd.googlegroups.com... > Hi, > > I am a newbie to vb...Can somebodyprovide me a simple vb script for > opening notepad in vista using windows shell What version of VB are you planning to use? This group is for VB classic(VB6 or lower) which is very different from VB.Net(sometimes called VB 2005/2008). If this ...

Outlook 2000
I'm having a strange Outlook issue and cannot figure it out for the life of me. Our mail server is Exchange 5.5 (can't recall the SP right now), and the Client is Outlook 2000 SR-1. The user can view his primary email box without any troubles, but also has access to another Exchange email box. He can view every folder in it except the Sent Items. The error it returns is "Unable to display the folder". What's strange is he can view the subfolder in Sent Items, no problem. I've configured his email on a different PC, and everything shows up fine, so I know ...

Calling a checkbox control with VB
Hi Is there a way I can reference a checkbox item that I can manipulate in VBA. For instance... I have a series of check boxes that I would like to change the properties of with a loop So my checkboxes are called: CheckBox1 CheckBox2 CheckBox3 etc and I want to be able to reference them like so: For i = 1 to 20 "CheckBox" & i.Value = true next i Any ideas? By the format of the name, I assume that they are control toolbox checkboxes. Dim oOLE As Object For Each oOLE In ActiveSheet.OLEObjects If TypeName(oOLE.Object) = "CheckBox" Then ...

Outlook security features in VB
Hi, I am using MAPI in VB to access outlook to sent email out based the email ID in excel file. When every new email was sent, the popup box shows "A program is trying to access e-mail address you have stored in Outlook. Do you want to allow this? ...." Is there any way to turn off this feature? I am using W2k and Office XP. Thanks in advance. Wilson ...

Complicated IF statement
Afternoon everyone. Have been struggling to get a formula together for the following. I am being told due to its complexity that I will probably need to break it down into 2 sections but hoping someone can help me simplify things. I need to do the following A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am struggling with) B1 = €10000 (TARGET) C1 - €6500 (ACTUAL) Then I have a few boxes showing the following <70% between 70-99% >100% A5 = 1 B5 = 10% C5 = 15% D5 = 18% A6 = 2 ...

Embedded Pictures received from Others do not display in outlook.
I recently switched to Outlook from Outlook Express. When e-mails are received with embedded pictures, they display properly in Outlook Express but do not display at all in Outlook. Is this related to a security setting in Outlook that I can change to allow these pictures to be displayed? PLEASE HELP!!!! Jim <Jim@discussions.microsoft.com> wrote: > I recently switched to Outlook from Outlook Express. When e-mails are > received with embedded pictures, they display properly in Outlook > Express but do not display at all in Outlook. > > Is this related to a securi...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

Update Query based on Current Recordset
I have a continuous form with filtered records. I want to run an update query on a field in a table based on the current filtered records on the form. What code would I use to accomplish this task? Any help appreciated in getting me started. NEWER USER, Without a wee bit more information, like what are you trying to update, field names, table names, do you want this attached to a button or an event, etc... The best I can do is... UPDATE SomeTable SET SomeTable.SomeField= WhateverValue WHERE (((SomeTable.SomeOtherFied)=WhateverValue)); Of course, the above would be an ...

IIF Query for Numeric Values in Text Field
I’m attempting to flag interest rate spread errors and omissions in a file from a sales database using IIF statements I adapted from another database (I’m a novice at this). The field I’m querying is text format and contains both alpha and numeric values. When I test for a null value the IIF works fine, but when I test for a numeric value I get an #ERROR. Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is Null),1,0) Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0...

Downloading Statements #4
I have been using Money 2005 for a long time with no problems, however in the last couple of weeks when I download statements I get the message "You have no statements to read", but when I go to my individual accounts there are items to read. Why do I get this message? I have cleared my temp file and uninstalled/reinstalled Money. In microsoft.public.money, maltesebudgie wrote: >I have been using Money 2005 for a long time with no problems, however in the >last couple of weeks when I download statements I get the message "You have >no statements to read", ...

"Display Map of Address" not working...
Hi - noticed this on two machines, one XP and the other WIN2K, both running Outlook 2003. When I click on "Display Map of Address" for a contact, a browser window opens, but it cannot connect to the web site it uses, which i believe is expedia maps; so the page is just empty. I've used this feature a lot in the past without problems, and have never had mappoint installed on either machine. I also found this problem when trying two separate browsers as the default. Given that i'm seeing the issue on two separate machines, thinking it's some sort of more global issue...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...

SQL Server and MSDE ?
cenario.... I have a server box running SQL Server 2000 and RMS HQ Server. I have another server running MSDE, RMS SO and HQ Client. (at another location, connected via ADSL VPN) The problem i have is that HQ Client downloads all worksheets from HQ Server. BUT it will not upload data back to HQ Server on a 401 it gives me a correlation error. SQL Server and MSDE use different collation. any ideas on how to change the default collation so they are both the same? You can do it using SQL - see SQL Books online or MSDN. Also, HQ Administrator and SO administrator both have options on the dat...

Multiple display names for one user?
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C42D19.8EED3880 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is this possible? We have a few people that I like to call "special" = who seem to have some identity problems. These people have a first = name, middle name and a nick name. Well they don't like the fact that = their email shows up as their legal name, but they still want it there = incase people use it. So what we have ended up doing is creating = multiple logins and email accounts th...

Excel 2003 vsto 2005 vb.net
Hello, I have an add-in that works well and executes the me.startup event perfectly when Excel is started manually. If Excel is started by double clicking an .xls file, the add-in fails to start and becomes disabled. The xls file then loads. I can re-enable the add-in and it executes me.starup fine. I found that when I strip much code back in me.startup the add-in can start sucessfully when double clicking an .xls file to open Excel. There does not appear to be any 'offending code' that causes the problem, it appears to be more along the line of timing. Thank...

auto display name in shared calendar
We have set up a shared calendar in the Excahnge public folder area on which each employee will begin to record when they are in/out - on vacation etc... This will enable staff to determine who is available and who is not when phone calls come in for them etc... It seems to work fine except that when you create a calendar entry, you always have to enter your name - so that others will know who the entry is for. Is there any way to set it up so that when you create a calendar entry, your name is automatically displayed as the creator of the item on the calendar entry?? Thanks, Brad ...