Unanswered: MS Access - Need help querying data to display on a report
Can somebody please help me out. I need to make a simple microsoft access program that displays a report of trips. Each trip consists of a date, a service number, driver, trailer, start location, end location, scheduled arrival date, sceduled arrival time, actual arrival date, actual arrival time. What I'm having problems with is allowing the user to display a range of data that he wants to display. I need the program to prompt the user for a start and end date for records to retrieve. I can do this through a query using square brackets for eg. [Enter the start date] in the query design view but I need to be able to prompt the user before displaying data on a report rather than a datagrid.
Alternately I'd like to create a form that takes a start date and end date in text boxes then query the database based between those 2 dates once a command button is pressed. I woud then like the data to be displayed on a report.
My recommendation: Create an unbound form with two text boxes, one for the start date and one for the end date. Place two command buttons, one labeled close or cancel, the other labeled OK or display or whatever you want.
You mention several date fields in your table. I'm not sure which dates you're allowing the user to query.
It will make a difference if you're wanting to include more than one field.
If you are only using one date field for the user to query, open the query the report is based on. Locate the field the user will be querying and in the criteria line you can enter:
>=[forms]![FormName]![ControlName] And <=[forms]![FormName]![ControlName]
If you want the date range the user selected to appear on the report, place two text boxes on the report and in the control sources for the text boxes place this:
Of course replace FormName with the name of your form and replace ControlName with the name of your control. In your case, you have two controls, one for a start date and one for an end date.
Sorry if I got too detailed but not sure how familiar you are with Access.
Also, if you're allowing the user to view the report before printing, you will need to leave the form open in order for the date ranges to appear on the report should the user choose to print it. Then you would need to have an On Close code in the report to close the form.
I just learned how to do all this a couple of months ago.
Thanks Zenaida! You've helped me a lot. I'm just pretty new at Access. The reason why I'm using it is because it's the cheapest solution for my companies needs. They don't want to have to spend a lot of money purchasing VB or anything like that. Thanks again