Inherited a Access databse, cant get it to generate multiple reports off a date range.

Copper Contributor

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?

13 Replies
Show us the SQL from the query, please.

And a screen shot of the table in design view would also be helpful.

@George Hepworth 

 

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

 

HKleinman_0-1703086492470.png

 

@HKleinman 

 

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.

 

 

 

I apologize, I am newer to Access and utilizing VBA and trying to learn and debug as I go.

Here is the SQL for Employeesqry:
 
SELECT AllEmployeesqry.BadgeIDNumber, AllEmployeesqry.[Personnel Number], AllEmployeesqry.[Position #], AllEmployeesqry.[Job Title], AllEmployeesqry.[Full Name], AllEmployeesqry.Post, AllEmployeesqry.Unit, AllEmployeesqry.Location, AllEmployeesqry.Zone, AllEmployeesqry.Onshift, AllEmployeesqry.[Accounted for:], AllEmployeesqry.Status, AllEmployeesqry.[Separation Date], AllEmployeesqry.[Holiday Off], AllEmployeesqry.Shift, AllEmployeesqry.[Start/End Time], AllEmployeesqry.RDO, AllEmployeesqry.WorkSites, AllEmployeesqry.Perimeter
FROM AllEmployeesqry
WHERE (((AllEmployeesqry.Status)="Active" Or (AllEmployeesqry.Status) Is Null) AND ((AllEmployeesqry.[Separation Date]) Is Null)) OR (((AllEmployeesqry.[Separation Date])>Date()));

This is the VBA for the form. there isnt mush to reference to inside of VBA at all. most of it looks like this.

Option Compare Database

Private Sub Command7_Click()

End Sub

I am trying to provide as much info as I can.

@HKleinman 

No need to apologize. You aren't responsible for knowing everything, all at once, the first time.

 

That query also should still return all employees which satisfy the criteria.

 

That VBA is actually nothing but an empty sub which does nothing!

 

So, the problem is elsewhere. I made an assumption early on that we need to validate. I assumed you were creating the reports from a procedure in a form. But that can't be the case on the basis of what we see so far. So, it's on me for having leapt to a conclusion.

 

How do you create the reports? Step by step if you can.

 

 

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

@Karl Donaubauer 

 

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)

 

HKleinman_0-1703172887562.png

 

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:

 

HKleinman_1-1703173400227.png

 

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

@Karl Donaubauer 

 

HKleinman_0-1703179917562.png

 

Here is the Macro that is being used for the "OK" button.

@Karl Donaubauer 

 

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));

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

Sorry, I was required to spend the day yesterday with my granddaughter, who has first priority in all things. I'll get back on board today, I hope.
I 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.