Solved

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

Posted on 2008-06-13
8
237 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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. …
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now