passing a parameter between 2 queries

alant37

Registered User.
Local time
Today, 11:38
Joined
Jun 8, 2005
Messages
10
I have 2 queries as follows:

DoCmd.OpenQuery "qryappendtblHelpdeskcallstoArchive", acViewNormal, acEdit
DoCmd.OpenQuery "qrydeletetblHelpdeskcallsArchived", acViewNormal, acEdit

Both queries have a parameter of Archive date which is used to first append calls less than the Archive date to another table then delete those calls from the original table.

The SQL for the 2 queries are below respectively:
PARAMETERS [Archive Date] DateTime;
INSERT INTO tblHelpdeskcallsArchive ( ID, [Date Opened], Priority, [User Name], [User Name1], Department, [Contact Number], [Call Description], [Allocated To], Email, [Date Closed], [Days Open], [Additional Coments], [Call Resolution], Category, Organisation, [Route received] )
SELECT tblHelpdeskcalls.ID, tblHelpdeskcalls.[Date Opened], tblHelpdeskcalls.Priority, tblHelpdeskcalls.[User Name], tblHelpdeskcalls.[User Name1], tblHelpdeskcalls.Department, tblHelpdeskcalls.[Contact Number], tblHelpdeskcalls.[Call Description], tblHelpdeskcalls.[Allocated To], tblHelpdeskcalls.Email, tblHelpdeskcalls.[Date Closed], tblHelpdeskcalls.[Days Open], tblHelpdeskcalls.[Additional Coments], tblHelpdeskcalls.[Call Resolution], tblHelpdeskcalls.Category, tblHelpdeskcalls.Organisation, tblHelpdeskcalls.[Route received]
FROM tblHelpdeskcalls
WHERE (((tblHelpdeskcalls.[Date Closed]) Is Not Null And (tblHelpdeskcalls.[Date Closed])<[Archive Date]));


PARAMETERS [Archive Date] DateTime;
DELETE tblHelpdeskcalls.*, tblHelpdeskcalls.[Date Closed]
FROM tblHelpdeskcalls
WHERE (((tblHelpdeskcalls.[Date Closed]) Is Not Null And (tblHelpdeskcalls.[Date Closed])<[Archive Date]));


Is there any way I can get the 2 queries to just accept one Archive Date as a parameter initially when doing the append then pass this value to the delete query. I created the 2 queries first seperately then created the code to run them, but they both are working at the moment in isolation from one another.
 
Put the date on a (hidden) form and refer to the control. That way the parameter will just take the value from the control and won't ask you for a date.

If you can automate it, then automate it.
 

Users who are viewing this thread

Back
Top Bottom