Macro to run multiple queries based on a single date range

[timmer2686]

I have a database in which there are 5 or 6 queries that I run in sequential order. I use a single date range for all the queries and currently have to update that criteria for each individual query. Is there a way that I could create a macro to run the queries in sequential order, allowing the first query to finish before moving to the next one, and manually enter the date range needed only once prior to running the macro?

Any help would be greatly appreciated.

Thank you!

 

[pbaldy]

I would have a form where the dates were entered, and have all the queries get them from there.

 

[timmer2686]

I would have a form where the dates were entered, and have all the queries get them from there.

Pbaldy - Thank you for the quick response. I am a complete noob at forms and macros in access 2010. Is there any way that you could explain what I would need to do?

Thanks!

 

[pbaldy]

Have you tried yet? Create a form. Put 2 textboxes on it. Add a button to run the macro. Have the queries use the textboxes in the criteria:

Forms!FormName.TextboxName

 

[timmer2686]

Created the macro:

Function Macro1()
On Error GoTo Macro1_Err

DoCmd.OpenQuery "Sales Query Step 1", acViewNormal, acEdit
DoEvents
DoCmd.OpenQuery "Sales Query Step 1A", acViewNormal, acEdit
DoEvents
DoCmd.OpenQuery "Sales Query Step 2", acViewNormal, acEdit
DoEvents
DoCmd.OpenQuery "Sales Query Step 2A", acViewNormal, acEdit
DoEvents
DoCmd.OpenQuery "Sales Query Step 3", acViewNormal, acEdit


Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

Created the form, see below:

Text Box: Start Date
Text Box: End Date
Button: Run Macro

Added the criteria as follows in GL Date column: >=[Forms]![Form1.Start Date] And <=[Forms]![Form1].[End Date]

How would I enter the date range and run? Thanks for the help.

 

[pbaldy]

I feel like I'm missing something, but you'd type two dates into the textboxes and then click on the button.

FYI, that's VBA code, not a macro.

 

[timmer2686]

I feel like I'm missing something, but you'd type two dates into the textboxes and then click on the button.

FYI, that's VBA code, not a macro.

It works that way currently, which is a good sign for me that I did it right (thank goodness), but it populates a parameter box for each query that uses the form criteria (2 of them) after I click the run button in which I have to re-enter the parameter values.

As for the Macro, I converted it to VBA so that I could add DoEvents to allow each query to run before starting the next one. Is there a way to do this in Macro Design View (Non-VBA)? Maybe a specific step between each OpenQuery?

Thanks!

 

[pbaldy]

You mean it's popping up parameter boxes instead of using the textboxes? If so, something is likely spelled wrong. Can you post the db here?

I don't use macros so I'm not sure if there's an equivalent to DoEvents in them. Personally I'd stick with the VBA.

 

[timmer2686]

You mean it's popping up parameter boxes instead of using the textboxes? If so, something is likely spelled wrong. Can you post the db here?

I don't use macros so I'm not sure if there's an equivalent to DoEvents in them. Personally I'd stick with the VBA.

I enter the date into the text boxes on the form and then click the run button. When the query runs into the criteria from the form an enter parameter value box appears for both text boxes in which I had previously entered the data. See screenshot.

I am not comfortable posting the actual database here as it is work related. Let me know if there is other information that can help.

 

[pbaldy]

Screen shot didn't make it. If you're getting a parameter prompt, likely the form name or textbox name is spelled wrong. What is the SQL of the query?

 

[timmer2686]

Screen shot didn't make it. If you're getting a parameter prompt, likely the form name or textbox name is spelled wrong. What is the SQL of the query?

SELECT DISTINCTROW PRODDTA_F0101.ABAC01 AS [Sales Division], dbo_Date_Conversion_Master.greg_date AS [G/L Date], PRODDTA_F42119.SDDOC AS [Invoice Number], PRODDTA_F42119.SDDCT AS [Invoice Type], PRODDTA_F0101.ABAN8 AS [Parent Number], PRODDTA_F0101.ABALPH AS [Parent Name], PRODDTA_F0101_1.ABAN8 AS [Ship-To Number], PRODDTA_F0101_1.ABALPH AS [Ship-To Name], PRODDTA_F4215.XHCTY1 AS City, PRODDTA_F4215.XHADDS AS State, PRODDTA_F4215.XHADDZ AS [Zip Code], Sum([SDAEXP]*0.01) AS [Gross Amount], PRODDTA_F42119.SDDCTO INTO Sales_Detail_1
FROM ((((PRODDTA_F42119 INNER JOIN dbo_Date_Conversion_Master ON PRODDTA_F42119.SDDGL = dbo_Date_Conversion_Master.JDE_date) INNER JOIN PRODDTA_F0101 AS PRODDTA_F0101_1 ON PRODDTA_F42119.SDSHAN = PRODDTA_F0101_1.ABAN8) INNER JOIN PRODDTA_F0101 ON PRODDTA_F42119.SDPA8 = PRODDTA_F0101.ABAN8) INNER JOIN PRODDTA_F0116 ON PRODDTA_F42119.SDSHAN = PRODDTA_F0116.ALAN8) INNER JOIN PRODDTA_F4215 ON PRODDTA_F42119.SDSHPN = PRODDTA_F4215.XHSHPN
GROUP BY PRODDTA_F0101.ABAC01, dbo_Date_Conversion_Master.greg_date, PRODDTA_F42119.SDDOC, PRODDTA_F42119.SDDCT, PRODDTA_F0101.ABAN8, PRODDTA_F0101.ABALPH, PRODDTA_F0101_1.ABAN8, PRODDTA_F0101_1.ABALPH, PRODDTA_F4215.XHCTY1, PRODDTA_F4215.XHADDS, PRODDTA_F4215.XHADDZ, PRODDTA_F42119.SDDCTO, PRODDTA_F42119.SDCO, PRODDTA_F42119.SDGLC
HAVING (((dbo_Date_Conversion_Master.greg_date)>=[Forms]![Form1].[Start Date] And (dbo_Date_Conversion_Master.greg_date)<=[Forms]![Form1].[End Date]) AND ((PRODDTA_F42119.SDDCT)="RI") AND ((PRODDTA_F42119.SDCO)="00001") AND ((PRODDTA_F42119.SDGLC)>"69" And (PRODDTA_F42119.SDGLC)<"96"))
ORDER BY dbo_Date_Conversion_Master.greg_date;

 

[pbaldy]

Nothing jumps out at me there. Can you try the picture again? Probably have to go into the attachments area.

 

[timmer2686]

Nothing jumps out at me there. Can you try the picture again? Probably have to go into the attachments area.

See attachment

 

[pbaldy]

I suspect the name of the textbox is spelled wrong. That's the caption of the associated label. You want the name of the textbox. It probably started out as "Text1" or something similar.

 

[timmer2686]

I suspect the name of the textbox is spelled wrong. That's the caption of the associated label. You want the name of the textbox. It probably started out as "Text1" or something similar.

Yes!!!!! It works. You were correct. I needed to label the criteria based on the text0 or text4 box name, not the name that I gave it on the form. The numbers match and I do not see the parameter value box populating. You are a life saver! Thank you very much!!!

 

[pbaldy]

Excellent! Happy to help.