Solved

Copy Query Parameters (MS-Access-2003) from another Query.

Posted on 2008-06-13
8
252 Views
Last Modified: 2010-04-21
How can I copy Query Parameters from another query?

In the Column where I'm specifying the parameters (within a query) - Can I just point it to look for the parameters show in another query within the same database?

Both Queries have a PPEndDate parameter that reads:  Between #10/13/2007# And #6/7/2008#
I want the 2nd Query - to look at the first one and replace it's date parameters with what it sees in the 1st query.

Is there a simple way that I can write this in write into the column?

Please assist...  Thanking you for your timely feedback & help,  sincerely, Raj.
0
Comment
Question by:R B
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 21779055
No.

It sounds like you need to create a form for the input of your parameter dates and then you can refer to the form textboxes in any query.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 21779134
peter57 nailed it.  
0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 50 total points
ID: 21779151
The straight answer is: No! You can't do this. What you can do is create a form with both date in textboxes (like txtInitialDate and txtEndDate) and put on both your queries the parameters like this:

>=Forms]![Form1]![txtInitialDate] And <=[Forms]![Form1]![txtEndDate]

jppinto
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:R B
ID: 21779531
That's not the case scenario here.  The 1st Query would have been run as part of a huge array module that executes via a Macro.   It's really a convoluted & very complicated set-up scenario.

I can't touch what I mentioned above - but, need to supplement to it - with a new Make-Table-Query that will need to "copy" the parameter that would have saved within Query 1 of that huge array.  This is the PPEndDate for which the parameter would be in a format of "  Between #mm/dd/yyyy# And #mm/dd/yyyy##.

Can I copy what's there in that column over to this column?  Or - Word the SQL to reflect this?
Both Queries exist within the same database.
Here's what's in the Query SQL of the one that needs to be changed referencing the 1st Query named _AUDDatesQry  :

SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN 'L:\$CA_Prod\Fy2008\AUD\RAJ_AUD_FY2008_MAY.mdb'
FROM _dbo_ncv_audit_val_allocs
WHERE ((([_dbo_ncv_audit__val_allocs].PPEndDate) Between #10/13/2007# And #5/24/2008#));

Please assist... thanks,  Raj.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21779614
'a new Make-Table-Query that will need to "copy" the parameter that would have saved within Query 1 of that huge array'

Unless you have actively saved the parameter values they would have been lost as soon as the query had been run.
0
 

Author Comment

by:R B
ID: 21780342
Yes... The parameter value gets saved in Query 1.  If you open the design view, the parameter column reads ' Between #10/13/2007# And #6/7/2008# '.  The SQL View of Query 1 show it too.  Here it is from Query 1:

SELECT [_dbo_ncv_audit_dates].*
FROM _dbo_ncv_audit_dates
WHERE ((([_dbo_ncv_audit_dates].PPEndDate) Between #10/13/2007# And #6/7/2008#));

So, I just literally need for Query 2 to go and copy what is showing above in Query 1.
Is this possible?  perhaps via a VBA Module?

Please assist,  thanking you for your time,  sincerely, Raj.

0
 
LVL 77

Accepted Solution

by:
peter57r earned 450 total points
ID: 21780799
To copy the Between clause you can do:

Dim strSQLq1
Dim strwhere1
Dim strsqlq2
strSQLq1 = CurrentDb.QueryDefs("query1").SQL
strwhere1 = Mid(strSQLq1, InStr(strSQLq1, "Between"))
strsqlq2 = CurrentDb.QueryDefs("query2").SQL
strsqlq2 = Left(strsqlq2, InStr(strsqlq2, "between") - 1) & strwhere1
CurrentDb.QueryDefs("query2").SQL = strsqlq2


This assumes that the between clause is the last text  in both cases
0
 

Author Closing Comment

by:R B
ID: 31466935
Thanks Guys!!!!    Your coding to copy the query-parameters worked - helped me greatly!!!  
And - the form idea - also - I'll be use in other scnearios.
Thanks a Million!!  to both of you.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question