Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
query input 2
- Thread starter 99mel
- Start date
-
1
- #2
Are you saying you want to prompt for a date range in a report that pulls it's data from a query? I don't know the SQL way, but in the query design grid you can put the following in the date criteria field. (type exactly)
Between [Enter Beginning Date] And [Enter Ending Date]
When you run the query or report you will be prompted with 2 parameter boxes to enter the beginning and ending dates.
Does this help?
Dawn
Between [Enter Beginning Date] And [Enter Ending Date]
When you run the query or report you will be prompted with 2 parameter boxes to enter the beginning and ending dates.
Does this help?
Dawn
-
1
- #3
Using Dawns method is a straightforward, simple approach.
If you switch your query to SQL view, you can insert her criteria line into the WHERE clause.
Select * From tablename
Where YourDateField Between [Enter Beginning Date] And [Enter Ending Date]
If you'd like to include the dates that the user specified in your result set, use the following (the above example would exclude any record that occured on the start date or end date. It would take only records in between the 2 dates)
Select * From tablename
Where YourDateField >= [Enter Begining Date] and YourDateField <= [Enter Ending Date]
As soon as your query is run, access will encounter the two elements in brackets, it will send up one dialog box to accept a start value, and once that dialog is closed, it will send up the second for the ending date value to be entered. This is the easiest method to do what you are asking, however a more elegant method would to be to make a small form for entry of the criteria all at the same time.
Instead of triggering the query to run immediately, first trigger the small criteria form to open and have the user enter values into 2 text boxes on the form. Next edit your query. You would want to reference the two text boxes in your criteria to accept the two values from the form. Also you would want a button on your criteria form to start the query running once the criteria had been entered (also attach code to close your criteria form here). It sounds like alot of work but the results allow you to accept the values all in one place cutting down on confusion for the user.
Its worth the excercise and will open up alot of new possibilities for you to offer more intricate queries options to novice users.
Blake
If you switch your query to SQL view, you can insert her criteria line into the WHERE clause.
Select * From tablename
Where YourDateField Between [Enter Beginning Date] And [Enter Ending Date]
If you'd like to include the dates that the user specified in your result set, use the following (the above example would exclude any record that occured on the start date or end date. It would take only records in between the 2 dates)
Select * From tablename
Where YourDateField >= [Enter Begining Date] and YourDateField <= [Enter Ending Date]
As soon as your query is run, access will encounter the two elements in brackets, it will send up one dialog box to accept a start value, and once that dialog is closed, it will send up the second for the ending date value to be entered. This is the easiest method to do what you are asking, however a more elegant method would to be to make a small form for entry of the criteria all at the same time.
Instead of triggering the query to run immediately, first trigger the small criteria form to open and have the user enter values into 2 text boxes on the form. Next edit your query. You would want to reference the two text boxes in your criteria to accept the two values from the form. Also you would want a button on your criteria form to start the query running once the criteria had been entered (also attach code to close your criteria form here). It sounds like alot of work but the results allow you to accept the values all in one place cutting down on confusion for the user.
Its worth the excercise and will open up alot of new possibilities for you to offer more intricate queries options to novice users.
Blake
- Thread starter
- #4
Cheers! thats a great help! will try it out!
Haven't used Access in a while and I think the ideal approach is to let the user click on a simple shortcut on the desktop where they are shown the form.
You can make the form pop up on start up of a db can't u?
Theres no other way of making a shortcut to this specfic query is there?
Cheers again!
Haven't used Access in a while and I think the ideal approach is to let the user click on a simple shortcut on the desktop where they are shown the form.
You can make the form pop up on start up of a db can't u?
Theres no other way of making a shortcut to this specfic query is there?
Cheers again!
To trigger a form to open when the database first opens do the following:
On the main toolbar for your database- click on "Tools" and then "StartUp".
You will then see a dialog box with a combo box labeled
"Display Form/Page"
Select your form name from the combo box and click "OK".
The next time your database is opened, your form will open automatically.
You could also attach a macro(or vba code) to an command button to trigger this criteria form to open any time the user would like to run it.
Blake
On the main toolbar for your database- click on "Tools" and then "StartUp".
You will then see a dialog box with a combo box labeled
"Display Form/Page"
Select your form name from the combo box and click "OK".
The next time your database is opened, your form will open automatically.
You could also attach a macro(or vba code) to an command button to trigger this criteria form to open any time the user would like to run it.
Blake
- Status
Similar threads
- Locked
- Question
- Locked
- Question
