Good day, I am a beginner with MSACCESS and would like some advice on creating a query/report. I have a table in my DB that i would like to run a query on. The table has a few columns but i would like to focus on only 2, which contain dates.
I want to run a query/report that will show me the following:
First i would like the query/report to prompt the user for a 'start date' and and 'end date'
Then i would like the query/report to somehow use these dates and calulate the number of items in the table that are between these dates and produce that value on the screen.
- I was able to get the input dates prompt setup in the query but it returns a list of all the records that it finds between these two dates(which is ok i guess cause i can just look on the screen to see how many records it returend to get the total), but is it possible to have it just produce the one number that shows the total?
So far i have only created a basic query for this with the information below: I havent started on the report yet.
Field: Order#
Table: orders_tbl
Field: OpenDate
Table: orders_tbl
Sort: Ascending
Field: CloseDate
Table: orders_tbl
Criteria:Between [Start Date (MM/DD/YYYY)] And [End Date (MM/DD/YYYY)]
*The information i have listed in the criteria above is what prompts the user to enter the dates that the query will go through the table and show me the list of all the orders that were closed between those dates. But like i mentioned earlier i would like it to just show me a total number value and not a list of all the orders. I have tried to use the count and dcount with no luck
Also to expand on this query, is it possible to produce a total number of entries that have a specific number of days between them?
- for example if the record has a startdate of A and and enddate of B and if there is 10 or less days inbetween. I would like the query/report to give me another total number value of all the records that fall in that range. Again I have played around with doing some datediffs, counts and grouping but i havent had any luck
Please let me know if this is possible or not with a basic query/report.
I believe in order to do what i need to, i have to some how get these user input dates into 'variables' that i can use and calculate against but thats were i am getting confused on how to do this within MSACCESS. Probably need to do a module or access page of some sort, but I would just like some suggestions on were to get started.
Just to give you some background on this. We currently have this database with a web front end to input the data and report on the data using HTML, JSCRIPT and VBSCRIPT. It works great, but unfortuantly we are not able to keep the server environment and need an MSACCESS only solution. We have already created an access form to enter and manipulate the data directly but we are trying to create the same types of reports we had before but its not quite working correctly.
Any sugestions would be greatly appreaciated
Thanks
MF