Forum Discussion
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 based off queries in the form, however it only will print out one sheet at a time and I have to manually input a date for each day. Being as I print these sheets 2 weeks at a time it means that I have to generate 14 individual sheets for each location.
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.
Is there a way to get the report to generate the date on each page in it current iteration? Or am I looking at building a new database from the ground up?
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 AEKOK. 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 AEK
- Show us the SQL from the query, please.
And a screen shot of the table in design view would also be helpful.This is the SQL from the qry.
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));This is the form used to perform the qry
Thank you, although that query is, in turn, based on another query called Employeesqry, it doesn't appear to be limiting the number of records returned to only one. Perhaps the problem is in that source query; can you show that SQL as well?
Also, it would be helpful to confirm that there are multiple records returned. How many records does it return when run as a stand-alone query?
I see nothing here that would create a report. How does that part work? Is that what the button on the form is supposed to do? We need to see that VBA as well.
