Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL statement with filter form not working

Status
Not open for further replies.

knifey

Technical User
Nov 14, 2006
180
GB
Hi,
I have a report that I would like to filter by name, role or area (so that I can export all reports for admin staf or senior managers, etc.)
I know a bit of Excel VBA but am a complete beginner at Access. My problem is that when I run the SELECT statement below a get a 3061 error (too few parameter values). It seems when the query is run it is unable to match the fields content to my filter form.
I suspect this is something very straightforward (probably syntax).
Any help or advice would be much appreciated.

SQL1:
SELECT DISTINCT fac_Employees.Id_Employee, fac_Employees.Area, fac_Employees.Role, fac_Employees.Complete_Name, fac_Employees.First_Name, fac_Employees.Last_Name, fac_Employees.email, [Rep 10 Mandatory Training Report Data].CurrentStaff FROM fac_Employees INNER JOIN [Rep 10 Mandatory Training Report Data] ON fac_Employees.Id_Employee = [Rep 10 Mandatory Training Report Data].Id_Employee WHERE ((([Rep 10 Mandatory Training Report Data].CurrentStaff) = "Yes"))

sqlFilter:
AND fac_Emploees.[Complete_Name]="Upeniece, Vita" AND fac_Emploees.[Area]="Admin" AND fac_Emploees.[Role]="Admin Assistants"

SQL2:
GROUP BY fac_Employees.Id_Employee, fac_Employees.Area, fac_Employees.Role, fac_Employees.Complete_Name, fac_Employees.First_Name, fac_Employees.Last_Name, fac_Employees.email, [Rep 10 Mandatory Training Report Data].CurrentStaff

Set qry = dbs.CreateQueryDef("temp_VBAQry20EmailDetails", SQL1 & sqlFilter & SQL2)

Thanks,
Knifey
P.S. I'll soon be bald if I continue scratching my head over this one:)
 
It isn't clear how SQL1, sqlFilter, and SQL2 get assigned their values. It may be simply a matter of changing the double quotes to singles like:
Code:
sqlFilter= " AND fac_Emploees.[Complete_Name]='Upeniece, Vita' " & _
    "AND fac_Emploees.[Area]='Admin' AND fac_Emploees.[Role]='Admin Assistants' "
To further debug, add a line:
Code:
'...
Debug.Print "SQL Statement: " & SQL1 & sqlFilter & SQL2
Set qry = dbs.CreateQueryDef("temp_VBAQry20EmailDetails", SQL1 & sqlFilter & SQL2)
'...
I also just noticed your mis-spelling of fac_Emploees.

Duane
Hook'D on Access
MS Access MVP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top