Question SQL VBA Code Not Working

[giddyhead]

Hi! How is everyone doing? When I ran the below code minus the highlighted part it works and when the current code is ran the following error message appear Run time error 3061 To Few parameters Expected 1. Your help is greatly appreciated. Thanks.

strSQL = "SELECT MOB_SAILOR_INFO.ID, DLookUp(""[Sailor_Rank_Rate]"",""[CIAC]"",""[SAILOR_NAME]='"" & [SAILOR_NAME] & ""'"") AS [RANK/RATE], DLookUp(""[Assigned_CIAC_Name]"",""[CIAC]"",""[SAILOR_NAME]='"" & [SAILOR_NAME] & ""'"") AS [Assigned CIAC], MOB_SAILOR_INFO.SAILOR_NAME, MOB_SAILOR_INFO.[HOME #], MOB_SAILOR_INFO.[WORK #], MOB_SAILOR_INFO.[CELL #], MOB_SAILOR_INFO.Family_Contact, MOB_SAILOR_INFO.First_Attempt, MOB_SAILOR_INFO.[Contact_Made(1)], IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO"") AS [Mobilization Member], MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.Second_Attempt, MOB_SAILOR_INFO.[Contact_Made(2)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.Third_Attempt, MOB_SAILOR_INFO.[Contact_Made(3)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.Fourth_Attempt, MOB_SAILOR_INFO.[Contact_Made(4)], MOB_SAILOR_INFO.[Status(4)]," & _
"MOB_SAILOR_INFO.Comments, MOB_SAILOR_INFO.FM_First_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(1)]," & _
"MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.FM_Second_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(2)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.FM_Third_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(3)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.FM_Fourth_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(4)], MOB_SAILOR_INFO.[FM_Status(4)], MOB_SAILOR_INFO.FM_Comments, MOB_SAILOR_INFO.MOB_Sailor_Status " & vbCrLf & _
"FROM MOB_SAILOR_INFO " & vbCrLf & _
"GROUP BY MOB_SAILOR_INFO.ID, DLookUp(""[Assigned_CIAC_Name]"",""[CIAC]"",""[SAILOR_NAME]='"" & [SAILOR_NAME] & ""'""), MOB_SAILOR_INFO.SAILOR_NAME, MOB_SAILOR_INFO.[HOME #], MOB_SAILOR_INFO.[WORK #], MOB_SAILOR_INFO.[CELL #], MOB_SAILOR_INFO.Family_Contact, MOB_SAILOR_INFO.First_Attempt, MOB_SAILOR_INFO.[Contact_Made(1)], MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.Second_Attempt, MOB_SAILOR_INFO.[Contact_Made(2)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.Third_Attempt, MOB_SAILOR_INFO.[Contact_Made(3)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.Fourth_Attempt, MOB_SAILOR_INFO.[Contact_Made(4)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.Comments, MOB_SAILOR_INFO.FM_First_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(1)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.FM_Second_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(2)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.FM_Third_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(3)], MOB_SAILOR_INFO.[FM_Status(3)]," & _
"MOB_SAILOR_INFO.FM_Fourth_Attempt , MOB_SAILOR_INFO.[FM_Contact_Made(4)], MOB_SAILOR_INFO.[FM_Status(4)], MOB_SAILOR_INFO.FM_Comments, MOB_SAILOR_INFO.MOB_Sailor_Status " & vbCrLf & _
"HAVING (((DLookUp(""[Assigned_CIAC_Name]"",""[CIAC]"",""[SAILOR_NAME]='"" & [SAILOR_NAME] & ""'""))[COLOR="Red"]=[Forms]![CIAC]![Process_CIACs][/COLOR]) AND ((IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO""))=""NO""));"

 

[pr2-eugin]

I stopped looking further when I spotted a DLookup in your SQL. Why would you do that? Domain functions should be used very rarely, using them in a SQL is very very inefficient and not to mention very expensive. Why have you not considered JOINING the tables?

The error is mainly because of the fact you have not concatenated the values.

 

[jdraw]

Ditto what Paul said.

Also error 3061 To Few parameters can often be traced to a spelling error. May not be in this case, but make sure you fieldnames arecorrect.

 

[giddyhead]

As stated I have joined the tables but the same error came up Run time error 3061 To Few parameters Expected 1.

strSQL = "SELECT MOB_SAILOR_INFO.ID, CIAC.Sailor_Rank_Rate, CIAC.Assigned_CIAC_Name, MOB_SAILOR_INFO.SAILOR_NAME, MOB_SAILOR_INFO.[HOME #], MOB_SAILOR_INFO.[WORK #], MOB_SAILOR_INFO.[CELL #], MOB_SAILOR_INFO.[Email-Address-P], MOB_SAILOR_INFO.[Email-Address-S], MOB_SAILOR_INFO.Family_Contact, MOB_SAILOR_INFO.First_Attempt, MOB_SAILOR_INFO.[Contact_Made(1)], IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO"") AS [Mobilization Member], MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.Second_Attempt, MOB_SAILOR_INFO.[Contact_Made(2)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.Third_Attempt, MOB_SAILOR_INFO.[Contact_Made(3)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.Fourth_Attempt, MOB_SAILOR_INFO.[Contact_Made(4)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.Comments, MOB_SAILOR_INFO.FM_First_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(1)], " & _
"MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.FM_Second_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(2)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.FM_Third_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(3)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.FM_Fourth_Attempt, MOB_SAILOR_INFO.[FM_Contact_Made(4)], MOB_SAILOR_INFO.[FM_Status(4)], MOB_SAILOR_INFO.FM_Comments, MOB_SAILOR_INFO.MOB_Sailor_Status " & vbCrLf & _
"FROM CIAC [COLOR="Red"]INNER JOIN[/COLOR] MOB_SAILOR_INFO ON CIAC.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME " & vbCrLf & _
"WHERE (((CIAC.Assigned_CIAC_Name)=[Forms]![CIAC]![Process_CIACs]) AND ((IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO""))=""NO""));"

 

[jdraw]

Have you tried using only some of the fields to get a query to work without syntax errors?

You may find that starting with a more basic query, getting it working, and then add additional fields may be the best debugging strategy.

Have you checked for a spelling error?

 

[pr2-eugin]

So what about my second comment? Concatenate values?

strSQL = "SELECT MOB_SAILOR_INFO.ID, CIAC.Sailor_Rank_Rate, CIAC.Assigned_CIAC_Name, " & _
         "MOB_SAILOR_INFO.SAILOR_NAME, MOB_SAILOR_INFO.[HOME #], MOB_SAILOR_INFO.[WORK #], " & _
         "MOB_SAILOR_INFO.[CELL #], MOB_SAILOR_INFO.[Email-Address-P], MOB_SAILOR_INFO.[Email-Address-S], " & _
         "MOB_SAILOR_INFO.Family_Contact, MOB_SAILOR_INFO.First_Attempt, MOB_SAILOR_INFO.[Contact_Made(1)], " & _
         "IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO') AS [Mobilization Member], " & _
         "MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.Second_Attempt, MOB_SAILOR_INFO.[Contact_Made(2)], " & _
         "MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.Third_Attempt, MOB_SAILOR_INFO.[Contact_Made(3)], " & _
         "MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.Fourth_Attempt, MOB_SAILOR_INFO.[Contact_Made(4)], " & _
         "MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.Comments, MOB_SAILOR_INFO.FM_First_Attempt, " & _
         "MOB_SAILOR_INFO.[FM_Contact_Made(1)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.FM_Second_Attempt, " & _
         "MOB_SAILOR_INFO.[FM_Contact_Made(2)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.FM_Third_Attempt, " & _
         "MOB_SAILOR_INFO.[FM_Contact_Made(3)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.FM_Fourth_Attempt, " & _
         "MOB_SAILOR_INFO.[FM_Contact_Made(4)], MOB_SAILOR_INFO.[FM_Status(4)], MOB_SAILOR_INFO.FM_Comments, MOB_SAILOR_INFO.MOB_Sailor_Status "

strSQL = strSQL & "FROM CIAC INNER JOIN MOB_SAILOR_INFO ON CIAC.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME " & _
         "WHERE ((CIAC.Assigned_CIAC_Name [B][COLOR=Red]= '" &[/COLOR][/B] [Forms]![CIAC]![Process_CIACs] [B][COLOR=Red]& "'[/COLOR][/B]) AND (" & _
         "IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO') = 'NO'));"

 

[Minty]

I'm pretty certain the quotes around the horrendous field names in the IIf are incorrect. Breaking it down to a vaguely readable format;

SELECT MOB_SAILOR_INFO.ID,
CIAC.Sailor_Rank_Rate, 
CIAC.Assigned_CIAC_Name,
MOB_SAILOR_INFO.SAILOR_NAME,
MOB_SAILOR_INFO.[HOME #],
MOB_SAILOR_INFO.[WORK #],
MOB_SAILOR_INFO.[CELL #],
MOB_SAILOR_INFO.[Email-Address-P],
MOB_SAILOR_INFO.[Email-Address-S],
MOB_SAILOR_INFO.Family_Contact,
MOB_SAILOR_INFO.First_Attempt,
MOB_SAILOR_INFO.[Contact_Made(1)],
IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO"") AS [Mobilization Member],
MOB_SAILOR_INFO.[Status(1)],
MOB_SAILOR_INFO.Second_Attempt,
MOB_SAILOR_INFO.[Contact_Made(2)],
MOB_SAILOR_INFO.[Status(2)],
MOB_SAILOR_INFO.Third_Attempt,
MOB_SAILOR_INFO.[Contact_Made(3)],
MOB_SAILOR_INFO.[Status(3)],
MOB_SAILOR_INFO.Fourth_Attempt,
MOB_SAILOR_INFO.[Contact_Made(4)],
MOB_SAILOR_INFO.[Status(4)],
MOB_SAILOR_INFO.Comments,
MOB_SAILOR_INFO.FM_First_Attempt,
MOB_SAILOR_INFO.[FM_Contact_Made(1)],
MOB_SAILOR_INFO.[FM_Status(1)],
MOB_SAILOR_INFO.FM_Second_Attempt,
MOB_SAILOR_INFO.[FM_Contact_Made(2)],
MOB_SAILOR_INFO.[FM_Status(2)],
MOB_SAILOR_INFO.FM_Third_Attempt,
MOB_SAILOR_INFO.[FM_Contact_Made(3)],
MOB_SAILOR_INFO.[FM_Status(3)],
MOB_SAILOR_INFO.FM_Fourth_Attempt,
MOB_SAILOR_INFO.[FM_Contact_Made(4)],
MOB_SAILOR_INFO.[FM_Status(4)],
MOB_SAILOR_INFO.FM_Comments,
MOB_SAILOR_INFO.MOB_Sailor_Status
FROM CIAC INNER JOIN MOB_SAILOR_INFO ON CIAC.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME 
WHERE (((CIAC.Assigned_CIAC_Name)=[Forms]![CIAC]![Process_CIACs]) 
AND ((IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO""))=""NO""));

You also have some serious lack of data normalization going on.
Have you tried making this run in the query window?

Try debug.printing your SQL string to see what it evaluates to.

 

[giddyhead]

pru-eugin,

As stated concatenated the value worked. It was overlook by accident. Thanks for your help.

 

[giddyhead]

Minty,

When I used the debug.print method the SQL minus the Concatenate value showed and pr2-eugin reminded me about it. When you said data normalization how would you do it?