Solved

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

Posted on 2008-06-13
8
230 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

13 Experts available now in Live!

Get 1:1 Help Now