Solved

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

Posted on 2008-06-13
8
246 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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