Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-13
8
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

Expert Comment

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

Assisted Solution

by:jppinto
jppinto earned 200 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 1800 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 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