CODEWITHSUNDEEP
sqlms-access
alexles
alexles

Reputation: 1

How to create query-based reports using user input?

I'm trying to create a report that pulls all transactions on a given day, and while I'm able to generate a static report (choosing an account at random by me and pulling all transactions for an arbitrary date), I need to be able to allow for user input so the user can select the date for which they'd like to see all transactions. How would I go about doing this?

See example table below...

TRANSACTIONS

ID|DATE       |STORE_ID |ACCT_ID
--+-----------+---------+---------
 1| 02/07/2017|      113|    1005
 2| 02/01/2017|      557|    1003
 3| 02/04/2017|      224|    1000
 4| 02/07/2017|      678|    1005
 5| 02/09/2017|      579|    1003
 6| 02/11/2017|      678|    1000
 7| 02/07/2017|      900|    1005

Thanks in advance!

Upvotes: 0

Views: 423

Answers (1)

Will Jobs
Will Jobs

Reputation: 360

This is exactly what Forms are for. Basically, a form has one or more "controls" that a user uses to specify various criteria. Then they click a button (another kind of control) which opens a report which is based on a query that references those form controls, e.g.

SELECT * FROM tblTransactions 
WHERE ACCT_ID = Forms!MyForm!ctrlAccountID AND
[DATE] < Forms!MyForm!ctrlDate;

You can layout the form and the report however you'd like. Access has a Form Wizard that can help you with the layout of the form, and creating a button only requires adding that control and then double clicking the button to open the VBA window, where you'd add code like:

DoCmd.OpenReport "myReport", acViewReport

I have created an imgur album showing the process. Also, here is my example Access file.

Upvotes: 1

Related Questions