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 20, 2023Copper Contributor
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
George_Hepworth
Dec 20, 2023Silver Contributor
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.
- HKleinmanDec 20, 2023Copper ContributorI 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.- George_HepworthDec 20, 2023Silver Contributor
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.