Forum Discussion
Inherited a Access databse, cant get it to generate multiple reports off a date range.
Hi,
In addition to George's step by step request:
> I have modified the form to be able to input a start date and an end date for the range, but when i use the "between" formula it still only generates one form with an error in the date section.
Where does this happen? I don't see it in your posted SQL statements. You should show us your (failed) attempts to let us see eventual syntax problems etc.
Servus
Karl
****************
Access Forever
Access News
Access DevCon
Access-Entwickler-Konferenz AEK
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.
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 AEKThe 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));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
