Solved

MS Access - Capture Query Parameter

Posted on 2016-10-11
2
25 Views
Last Modified: 2016-10-11
I created a simple Access form with several buttons that basically runs a set of queries and allows the user to save the result to an excel file, with a set file name. Besides Access queries, it uses macros, and a few VBA RunCode functions to do some simple stuff. I don't want to over-complicate things, but now I find that it would be helpful if the saved excel file name could be created dynamically according to a certain format, with the format containing the date of the report. To do this efficiently, I need to capture the date of the report. This date is entered by the user when the first query is run, because there is a parameter in the query which causes the the query to stop and ask for a value. The parameter ([ReportMonth]) is used in the query's WHERE clause: WHERE (((ConvertMyStringToDate(SPS.submissionDt)) Between CDate(DatePart("m",[ReportMonth]) & "/23/" & DatePart("yyyy",[ReportMonth])) And CDate(DatePart("m",DateAdd("m",1,[ReportMonth])) & "/22/" & DatePart("yyyy",DateAdd("m",1,[ReportMonth]))))) Or IP.startDate=[ReportMonth].  I would like to capture this value (as a string) and use it later in the creation of the file name, which would look something like:  fileName = ABCD_YYYYMMDD.xlsx.

I suspect I could use an input box at the beginning - before the first query - to capture it, and maybe update a table with the value, but then how would I use it in the WHERE clause?  I would have no trouble creating the file name.
0
Comment
Question by:bassman592
2 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41838588
<<I suspect I could use an input box at the beginning - before the first query - to capture it, and maybe update a table with the value, but then how would I use it in the WHERE clause?  I would have no trouble creating the file name.>>

  Just add a text control on it to capture the date.

 The query then can refer directly to the form control.  In the query designer, just add:

=Forms![<myformName>]![<myControlName>]

 on the criteria line, replace the < >'s with the correct values.

Jim.
0
 

Author Closing Comment

by:bassman592
ID: 41838850
Ok, thanks! I was hoping to not add any physical objects, just code, but that's probably the most straightforward solution.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

12 Experts available now in Live!

Get 1:1 Help Now