Convert sql to vba

G

giddyhead

Registered User.
Local time
Today, 15:49
Joined
Jul 3, 2014
Messages
88
Hello everyone! I am trying to convert sql to vba upon entering the following sql into vba in access SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Emal], MOB_SAILOR_INFO.[Emal-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[S-EMAL], CIAC_DATA.[Age of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], CIAC_DATA.Assigned_CIAC_Name, CIAC_FILTER.CIAC_Name, Signature_Card.Subject_Returning, Signature_Card.Returning, Signature_Card.Subject_Executing_Orders, " & _
"Signature_Card.Executing_Orders, Signature_Card.Subject_Under_Orders, Signature_Card.Under_Orders, Signature_Card.IA_Family, Signature_Card.CIAC_Members, MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.[FM_Status(4)] " & _
"FROM Signature_Card RIGHT JOIN (CIAC_DATA LEFT JOIN (CIAC_FILTER RIGHT JOIN MOB_SAILOR_INFO ON CIAC_FILTER.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON CIAC_DATA.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON Signature_Card.CIAC_Members = CIAC_FILTER.Assigned_CIAC_Name " & _
"WHERE (((CIAC_DATA.Sailor_Status)=[Forms]![CIAC]![MOB_SAIL_STATS]) AND ((CIAC_DATA.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""));") the following error came up: Run-time error '3061': Too few parameters. Expected 4. What I trying to do is put the following code into a recordset so I can send emails to personnel based upon the filters within. I am not quite sure what is wrong here request your help. Thanks
 
Hold on... need to repost with code tags...

Code: 
SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Emal], MOB_SAILOR_INFO.[Emal-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[S-EMAL], CIAC_DATA.[Age of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], CIAC_DATA.Assigned_CIAC_Name, CIAC_FILTER.CIAC_Name, Signature_Card.Subject_Returning, Signature_Card.Returning, Signature_Card.Subject_Executing_Orders, " & _
"Signature_Card.Executing_Orders, Signature_Card.Subject_Under_Orders, Signature_Card.Under_Orders, Signature_Card.IA_Family, Signature_Card.CIAC_Members, MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.[FM_Status(4)] " & _
"FROM Signature_Card RIGHT JOIN (CIAC_DATA LEFT JOIN (CIAC_FILTER RIGHT JOIN MOB_SAILOR_INFO ON CIAC_FILTER.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON CIAC_DATA.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON Signature_Card.CIAC_Members = CIAC_FILTER.Assigned_CIAC_Name " & _
"WHERE (((CIAC_DATA.Sailor_Status)=[Forms]![CIAC]![MOB_SAIL_STATS]) AND ((CIAC_DATA.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""));")
 
Okay, the problem is the red part...

Code: 
WHERE (((CIAC_DATA.Sailor_Status)=[B][COLOR=red][Forms]![CIAC]![MOB_SAIL_STATS])[/COLOR][/B] AND ((CIAC_DATA.Assigned_CIAC_Name[COLOR=red][COLOR=black])=[/COLOR][B][Forms]![CIAC]![Process_CIACs])[/B][/COLOR] AND ((IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO""))=""NO""));")

Those parameters need to be inserted another way. Are they TEXT or NUMERIC?
 
Try...

Code: 
WHERE (((CIAC_DATA.Sailor_Status)= '" & [Forms]![CIAC]![MOB_SAIL_STATS] & "') AND ((CIAC_DATA.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""));")

Note: Only adjusting the WHERE portion so the balance of the code should stay the same.
 
Last edited:
Just spotted two double quotes that you slightly missed Gina. They are before both ANDs and after the parens.
 
vbaInet... okay not seeing what your seeing because it's a TEXT field. Am I that tired??? :confused:
 
Sorry! Seriously, I'm the one that's tired! My brain needs to reboot (5 am here). :)
 
GinaWhipp said:
vbaInet... okay not seeing what your seeing because it's a TEXT field. Am I that tired??? :confused:
Click to expand...
Code: 
WHERE (((CIAC_DATA.Sailor_Status)= '" &  [Forms]![CIAC]![MOB_SAIL_STATS] & "'[B][COLOR=Red]"[/COLOR][/B]) AND  ((CIAC_DATA.Assigned_CIAC_Name)= '" &[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""));")
Look at the above
 
:eek: Okay JHB I am tired... calling it a night! (Thanks!)
 
Gina,

Thanks for the reply. Upon modifing the code and puttting WHERE (((CIAC_DATA.Sailor_Status)= '" & [Forms]![CIAC]![MOB_SAIL_STATS] & "') AND ((CIAC_DATA.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""));") The following error occurred: Run-time error '3061': Too few parameters. Expected 8. Standing by for your help. Thanks.
 
JHB,

Move love on the response. When I modified the code it gives me a comple error: Expected: expression at it refers to the area in red: "WHERE (((CIAC_DATA.Sailor_Status)= '" & [Forms]![CIAC]![MOB_SAIL_STATS] & "'") AND ((CIAC_DATA.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""));") Thanks
 
Please use code tags when you post code, including sql... see my signature for a link
Also
Code: 
mySQL = mySQL & " WHERE  CIAC_DATA.Sailor_Status     = '" & [Forms]![CIAC]![MOB_SAIL_STATS] & "' "
mySQL = mySQL & "   AND  CIAC_DATA.Assigned_CIAC_Name= '" &[Forms]![CIAC]![Process_CIACs] & "'  "
mySQL = mySQL & "   AND  IIf(   [status(1)]=""S""  "
mySQL = mySQL & "            Or [status(2)]=""S""  "
mySQL = mySQL & "            Or [status(3)]=""S""  "
mySQL = mySQL & "            Or [status(4)]=""S""   ,""YES"",""NO"")=""NO"" ;"
Looking at your (cleaned) sql, using Iif in a where clause is IFFY at best.... while you can simply solve this in normal sql:
Code: 
mySQL = mySQL & " WHERE  CIAC_DATA.Sailor_Status     = '" & [Forms]![CIAC]![MOB_SAIL_STATS] & "' "
mySQL = mySQL & "   AND  CIAC_DATA.Assigned_CIAC_Name= '" &[Forms]![CIAC]![Process_CIACs] & "'  "
mySQL = mySQL & "   AND  [status(1)]<>""S""  "
mySQL = mySQL & "   and  [status(2)]<>""S""  "
mySQL = mySQL & "   and  [status(3)]<>""S""  "
mySQL = mySQL & "   and  [status(4)]<>""S""  "
 
Last edited:
giddyhead said:
JHB,

Move love on the response. When I modified the code it gives me a comple error: Expected: expression at it refers to the area in red: "WHERE (((CIAC_DATA.Sailor_Status)= '" & [Forms]![CIAC]![MOB_SAIL_STATS] & "'") AND ((CIAC_DATA.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""));") Thanks
Click to expand...

Is because of your extra " in there in bold italic and underlined and in magenta
 
giddyhead...

PLEASE :D use code tags, it's much easier to read. (Pound (#)button on the menu when you go to Advanced.)

That message means it's not finding any of them. What is the Record Source of the Form? Can you please post here. While we wait you can try...

Code: 
WHERE (((CIAC_DATA.Sailor_Status)= '" & Me.[MOB_SAIL_STATS] & "') AND ((CIAC_DATA.Assigned_CIAC_Name)= '" & Me.[Process_CIACs] & "') AND ((IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO""))=""NO""));")
 
Thank you all. To shed a little light I have added an example of the database. Within the module it has the information on the recordset and on the main form it has which SQL I am trying to convert to vba so I can use the record on it. In the two drop down combo boxes one is the person assigned to the sailor and the next is the sailor status it can filter out the accordingly. Thanks.
 

Attachments

Okay, I'm not going to comment on the Field anmes or the Control Names. I am going to say that your Tables could definitely use some work. When I see names Like *2014-June-17* and Contact_Made(1) and Contact_Made(2) red flags go up. You really should consider fixing that now. Not just for you and because it will be easier to maintain but what about the person following you? Okay, enough said...

I found the Query CIAC_-SQL-VBA QUERY. What I can't find is where you put it behind the Form CIAC. Can you please give me a clue?
 
You must log in or register to reply here.

Similar threads

G
convert raw sql to vba
Replies
10
Views
1,201
JHB
J
G
Question SQL VBA Code Not Working
Replies
8
Views
811
giddyhead
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom