Error 3075 (missing operator)

Chalkie42

Registered User.
Local time
Today, 02:35
Joined
Feb 18, 2008
Messages
42
This is one of these occasions where I just can't see the wood for the trees. I am attemting to convert client records from a waiting list onto a live list via a userform. The userform shows the user the client details before they press the command button. The following code should take that information and INSERT a record into tblClientList but I get error 3075 (missing operator). I have tried everything but just can't see the problem. (the immediate window looks okay to me) Any help would be appreciated.
Code:
SQL = "INSERT INTO tblClientList (ClientID,DateAdded,FirstName,LastName,Title,[Address Line 1],[Address Line 2],[Address Line 3],"
SQL = SQL & "[Post Code],[Telephone Home],[Telephone Mobile],Counsellor,FullName,ClientAvailable,emailAddress,WLID,ContactTelHome,ContactTelMob,Voicemail,MobileText,ContactEmail,NoContact,OKtoIDTM,CounsellorPref)"
SQL = SQL & "VALUES ('" & Forms!frmConvert!ClientID & "','" & Forms!frmConvert!TxtDateAdded & "',"
SQL = SQL & "'" & Forms!frmConvert!FirstName & "','" & Forms!frmConvert!LastName & "','" & Forms!frmConvert!Title2 & "',"
SQL = SQL & "'" & Forms!frmConvert!AddressLine1 & "','" & Forms!frmConvert!AddressLine2 & "','" & Forms!frmConvert!AddressLine3 & "',"
SQL = SQL & "'" & Forms!frmConvert!PostCode & "','" & Forms!frmConvert!HomePhone & "','" & Forms!frmConvert!MobilePhone & "',"
SQL = SQL & "'" & Forms!frmConvert!Counsellor & "','" & Forms!frmConvert!FullName & "','" & Forms!frmConvert!ClientAvailable & "',"
SQL = SQL & "'" & Forms!frmConvert!emailAddress & "','" & Forms!frmConvert!WLID2 & "','" & Forms!frmConvert!Check79 & "',"
SQL = SQL & "'" & Forms!frmConvert!Check81 & "','" & Forms!frmConvert!Check82 & "','" & Forms!frmConvert!Check83 & "',"
SQL = SQL & "'" & Forms!frmConvert!Check84 & "','" & Forms!frmConvert!Check85 & "','" & Forms!frmConvert!Check86 & "','" & Forms!frmConvert!Text90 & "');"
  
Debug.Print SQL
DoCmd.RunSQL SQL
 
put a space before the word Values
 
Mmmm. Same error.
 
Can you show us what you get from that debug.print ?
That way we can check what is wrong with the sql.
Thanks
 
Apologies. The immediate window displayed confidential information so I set up a dummy record that I could post from the immediate window. When I pressed the button to convert the record it worked perfectly (no error). So I'm guessing the problem lies with the data in the sending table not being compatible with the field in the receiving table?
 
Just for the sake of closing this thread in a helpful way - The problem was indeed in the data that was being passed to the new table. The Address field in question had an entry that included an apostrophe (') i.e. "5 Martin's Way" The sql saw that as an end of field hence the error. I researched a way to overcome that by using double quotation marks instead of single for that field and it works fine.
Code:
SQL = SQL & "[COLOR=Red]"[/COLOR]" & Forms!frmConvert!AddressLine1 & "[COLOR=Red]"[/COLOR],'" & Forms!frmConvert!AddressLine2 & "','" & Forms!frmConvert!AddressLine3 & "',"
 

Users who are viewing this thread

Back
Top Bottom