• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

SQL INNER JOIN Error 3061 Too Few Para. Expected 1

Have an SQL statement that is erroring:

SELECT MemberAssignments.StartDate, MemberAssignments.EndDate, MemberAssignments.AssignmentName FROM MemberAssignments INNER JOIN AssignmentTypeDefinitions ON (MemberAssignments.AssignmentType = AssignmentTypeDefinitions.TADLeaveTypeID) WHERE (((MemberAssignments.MemberID) = 276) AND ((MemberAssignments.StartDate) <= #11/30/2011#) AND ((MemberAssignments.EndDate) >= #11/1/2011#) AND (((AssignmentTypeDefinitions.FullType) = 'TAD INTERNAL') OR ((AssignmentTypeDefinitions.FullType) = 'TAD DISTANT') OR ((AssignmentTypeDefinitions.FullType) = 'TAD NEAR')))

I've tried it with and without parenthesis (excepting the OR section which needs it I think.

It's the OR ('TAD INTERNAL' OR 'TAD NEAR' OR 'TAD DISTANT') section that is causing the error, as the statement will run without it.  Unfortunately, I need it :P

Working in an excel 2003 VBa envirnment, referencing a .mdb database using DAO.
0
narvinye
Asked:
narvinye
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Too few parameters means that something is not understood in the statement and Excel is looking for a value for that, which you didn't provide.

 Check your field and table name spellings carefully.  My suggestion would be to build up the statement one part at a time.

  ie. chop off the where clause, test, then add part of the where clause, test, then add another part, etc till you find the problem.

Jim.
0
 
EyalCommented:
try this...
SELECT MemberAssignments.StartDate, MemberAssignments.EndDate, MemberAssignments.AssignmentName FROM MemberAssignments INNER JOIN AssignmentTypeDefinitions ON (MemberAssignments.AssignmentType = AssignmentTypeDefinitions.TADLeaveTypeID) WHERE (((MemberAssignments.MemberID) = 276) AND ((MemberAssignments.StartDate) <= #11/30/2011#) AND ((MemberAssignments.EndDate) >= #11/1/2011#) AND (((AssignmentTypeDefinitions.FullType) = 'TAD INTERNAL') OR ((AssignmentTypeDefinitions.FullType) = 'TAD DISTANT') OR ((AssignmentTypeDefinitions.FullType) = 'TAD NEAR')))

Open in new window

0
 
EyalCommented:
the correct sql now...
SELECT MemberAssignments.StartDate, MemberAssignments.EndDate, MemberAssignments.AssignmentName 
FROM MemberAssignments 
INNER JOIN AssignmentTypeDefinitions ON (MemberAssignments.AssignmentType = AssignmentTypeDefinitions.TADLeaveTypeID) 
WHERE 
MemberAssignments.MemberID = 276 AND MemberAssignments.StartDate <= #11/30/2011# AND MemberAssignments.EndDate >= #11/1/2011# AND 
AssignmentTypeDefinitions.FullType in ('TAD INTERNAL','TAD DISTANT','TAD NEAR')

Open in new window

0
 
narvinyeAuthor Commented:
Ended up having to remove the specific table names, except in the JOIN statement.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now