Forum Discussion
HKleinman
Dec 19, 2023Copper Contributor
Inherited a Access databse, cant get it to generate multiple reports off a date range.
As the subject line states, I inherited a Microsoft Access database that assists me in printing a list of names based off where people work. I have an input form that generated a lot of information b...
HKleinman
Dec 21, 2023Copper Contributor
OK. I will do what I can to explain how I utilize the database and how it is set up.
When the program is launched i am prompted by a "start up" form where in you can scan employee ID's to track people in real time as they enter the building OR you can click the cancel button to access the next menu form. For the purposes I use the database for, I click the cancel button and am prompted with another form the "Main Menu" of the database. I then pick from a set of selection buttons for the next form for the program to call up. I click the "Administrator" button and am prompted with another form to enter my sign in credentials.
After entering my sign in information, another selection box form is populated and I select the option I want. This brings up another Form with selection boxes for me to then select another selection box which brings me to the final form which has the fillable boxes and looks like:
(I have made some visual updates in the past few days)
Each of the fillable boxes call specific information from the quarry tables. Currently the only fillable boxes I use from this form are "Select Facility", "Zone", "Start Date" (Was formally just "Date", until i modified the form), and "Enter Facility Heading".
When I click the OK button, it populates this report:
The top right box with the formula is : =[Forms]![SelectRoster]![Heading], which pulls the heading from the willable form.
The next box with a formula is: =[Forms]![SelectRoster]![Startdate]. Which you can see just above the "in and out" label boxes. I have attempted to change this formula to utilize the "between" function for a date range but when I do so the report still only pulls one page and provides me with a date of "1/1/1999". the Formula I attempted to utilize was BETWEEN![Forms]![SelectRoster]![Startdate]AND![Forms]![SelectRoster]![Enddate].
The "Full Name", "Post" and "Unit" boxes in the Detail section will pull data straight from the quarry table based off the selection from the form fill in box "Select Facility". this narrows down the scope of the quarry.
I hope this helps further explain what is going on with the report.
Dec 21, 2023
Hi,
There are 2 things you need to show us and probably change it in order to filter the data in the report with your date range:
1. The code or macro action behind the Ok button in your form.
Open the form in design view, click at the button and in the properties of the button search for the OnClick property. Click at the little button with the elipsis to the right of the property line. This should bring you to the code (or macro) that opens the report.
2. The Record Source property of the report.
That could be a table or query name or a SQL text.
Servus
Karl
****************
Access Forever
Access News
Access DevCon
Access-Entwickler-Konferenz AEK
- HKleinmanDec 21, 2023Copper Contributor
The Record source calls to another query table, "selectrosterqry"
here is the SQL info from that qry table:
SELECT Employeesqry.BadgeIDNumber, Employeesqry.[Personnel Number], Employeesqry.[Position #], Employeesqry.[Job Title], Employeesqry.[Full Name], Employeesqry.Post, Employeesqry.Unit, Employeesqry.Location, Employeesqry.Zone, Employeesqry.Onshift, Employeesqry.[Accounted for:], Employeesqry.Status, Employeesqry.[Separation Date], Employeesqry.[Holiday Off], Employeesqry.Shift, Employeesqry.[Start/End Time], Employeesqry.RDO
FROM Employeesqry
WHERE (((Employeesqry.Zone)=forms!selectroster!zone) And ((Employeesqry.Location.Value)=Forms!SelectRoster!facility Or (Employeesqry.Location.Value)=forms!selectroster!facility2)) Or (((Employeesqry.Location.Value)=Forms!SelectRoster!facility Or (Employeesqry.Location.Value)=forms!selectroster!facility2) And ((forms!selectroster!zone) Is Null));- Dec 22, 2023
Hi,
I had hoped George would bite the bullet. 😉
So, your SQL text for the report shows that the parameters from the form controls (zone, facility, facility2) are used there for filtering. Of course, there is no sign of your date controls there yet.
The first question is why, according to your description, there is only 1 record in the report with this filter data, the other is which of these form controls you still need for filtering, in addition to the date range. If we know this, we can perhaps adapt the SQL text as desired.
Servus
Karl
****************
Access Forever
Access News
Access DevCon
Access-Entwickler-Konferenz AEK- HKleinmanDec 22, 2023Copper ContributorI believe the date aspect was an afterthought and is directly tied to the Filled out form for filtering and not to the query at all.
When I pull the report in the normal use of the form, it gives me all active employees for that specific filter set. Thats why there is only one record. It was designed previous to my obtaining it to have multiple records but anything beyond the first record only gave extra blank lines. All the other filters are working properly.
The "date box" at the top of the In and out section is directly tied to the Fillable form and not the query table.
- HKleinmanDec 21, 2023Copper Contributor