Parameter Date query not the results I want

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new to Access and I don't know any VBA. I am trying to use a parameter query to type a specific date. It’s based on qryAssignUmps (based on tblUmpires with not all the table’s fields included) and tblUmpAvailable. The fields in UmpAvailable are UmpID, GameDate, Available (Yes/No), AvailableNote
There are 121 records in tblUmpires and 121 records in qryAssignUmps. When I run the query, it returns 61 records. It should return 82 records. I sent the query results to Excel and checked. If the umpire is listed in the UmpAvailable table with a date other than the parameter date, the record is not included in the query results. I’m trying to get a list of all Umpires for a given date that haven’t said no they can’t work. This is the SQL statement that Access wrote

SELECT qryAssignUmps.Umpire, qryAssignUmps.ASAClinic, qryAssignUmps.CICA, qryAssignUmps.Slow, qryAssignUmps.Fast, qryAssignUmps.City, tblUmpAvailable.GameDate, tblUmpAvailable.Available, tblUmpAvailable.AvailableNotes, qryAssignUmps.HomeNum, qryAssignUmps.WorkNum, qryAssignUmps.Ext, qryAssignUmps.CellNum, qryAssignUmps.BeeperNum, qryAssignUmps.AltNum, qryAssignUmps.Notes, qryAssignUmps.UmpI
FROM qryAssignUmps LEFT JOIN tblUmpAvailable ON qryAssignUmps.UmpID = tblUmpAvailable.UmpI
WHERE (((tblUmpAvailable.GameDate)=[Enter Date]) AND ((tblUmpAvailable.Available)=Yes)) OR (((tblUmpAvailable.Available) Is Null)
ORDER BY qryAssignUmps.Umpire

What am I doing wrong? Can I get the list I need
Thanks in advance for any hel
 
Grandma,

If I understand you correctly, the WHERE clause of the SQL statement
should read...

WHERE (((tblUmpAvailable.GameDate)=[Enter Date]) AND
((tblUmpAvailable.Available)=Yes)) OR
((tblUmpAvailable.GameDate)<>[Enter Date]) OR ((tblUmpAvailable.UmpID)
Is Null)
 
That gave me 353 records and didn't limit GameDate to 5/15/04. I am trying to get a list of umpires from the AssignUmps query that excludes anyone that has said no for 5/15/04. The date will change frequently. If an umpire is listed in the table UmpAvailable for any date, the record is not returned in the query result unless the date is the same as the parameter date. I just can't get it to work.
 
Grandma,

Hmmm. Maybe you could give some examples of the data and what the
desired result is.
 
Umps Available Table (Primary Key UmpID and GameDate
Umpire UmpID GameDate Availabl
Name1 ID1 5/8/04 Ye
Name2 ID2 5/8/04 Ye

The query does not have GameDate. Both of the umpires available for 5/8/04 were excluded from the query result. I want the query to ignore any date except the parameter date and return All umpires from the query except the umpires listed in the Umps Available table that say no for the parameter date. I've been trying with 5/15/04. Any umpire in the Umps Available table with any date are not included in the result unless they are listed in the available table with Yes for the same date as the parameter.
 
Back
Top