Syntax Error Driving Me Mad!

  • Thread starter Thread starter newt
  • Start date Start date
N

newt

I get a syntax error from the following On Click Event (for creation of a new
record):

sSQL = "insert into x_task (project_id, task_subcategory_id,
task_description, supervisor_id, lead_id, " & _
"junior_id, staffing_notes, task_status_id, hot_potato_id,
task_status_notes) " & _
"values (" & Me.lstProject.Value & "," & Me.lstTaskSubType.Value &
",'" & _
dbl_quotes(Me.txtTaskNotes) & "'," & Me.lstSupervisor.Value & "," &
Me.lstLead.Value & "," & intJunior & ",'" & _
dbl_quotes(Me.txtStaffNotes) & "'," & Me.lstTaskStatus.Value & "," &
inthotpotato & ",'" & _
dbl_quotes(Me.txtStatusNotes) & "');"

Can anyone see the source of the error? (intjunior and inthotpotato are
defined earlier.)

Thanks in advance.
 
hi,
sSQL = "insert into x_task (project_id, task_subcategory_id,
task_description, supervisor_id, lead_id, " & _
"junior_id, staffing_notes, task_status_id, hot_potato_id,
task_status_notes) " & _
"values (" & Me.lstProject.Value & "," & Me.lstTaskSubType.Value &
",'" & _
dbl_quotes(Me.txtTaskNotes) & "'," & Me.lstSupervisor.Value & "," &
Me.lstLead.Value & "," & intJunior & ",'" & _
dbl_quotes(Me.txtStaffNotes) & "'," & Me.lstTaskStatus.Value & "," &
inthotpotato & ",'" & _
dbl_quotes(Me.txtStatusNotes) & "');"

Can anyone see the source of the error? (intjunior and inthotpotato are
defined earlier.)
Click to expand...
Use Debug.Print sSQL the output the string to the immediate window.

Modify your dbl_quotes function. I'm using this functions:

Public Function SQLQuote(AValue As String, _
Optional ADelimimter As String = "'" _
) As String

Dim Result As String

Result = ADelimiter & _
Replace(AValue, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

SQLQuote = Result

End Function

Public Function SQLQuoteNull(AValue As Variant, _
Optional ADelimimter As String = "'" _
) As String

Dim Result As String

If IsNull(AValue) Then
Result = "NULL"
Else
Result = ADelimiter & _
Replace(AValue, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter
End If

SQLQuote = Result

End Function



mfG
--> stefan <--
 
Thanks, but that seems really complicated and my double quotes function seems
to work fine everywhere else:

If IsNull(str) Then
dbl_quotes = ""
Else
dbl_quotes = Replace(Replace(str, "'", "''"), """", """""")
End If
 
Thanks, but that seems really complicated and my double quotes function seems
to work fine everywhere else:

If IsNull(str) Then
dbl_quotes = ""
Else
dbl_quotes = Replace(Replace(str, "'", "''"), """", """""")
End If

Could it be something else?
 
hi,
Thanks, but that seems really complicated and my double quotes function seems
to work fine everywhere else:
Click to expand...
As I said before: Use Debug.Print sSQL or MsgBox sSQL to get the final
string. Otherwise I need to guess...

e.g. txtStatusNotes.Value is NULL:

INSERT INTO x_task
(
project_id, task_subcategory_id,
task_description, supervisor_id,
lead_id, junior_id,
staffing_notes, task_status_id,
hot_potato_id, task_status_notes
)
VALUES
(..,..,'..',..,..,..,'..',..,..,'');

normally you would not allow empty strings in a Text or Memo field,
which would raise an error.

The difference between my functions and yours are the quotes around the
value, which helps you writing SQL strings in code:

sSQL = "INSERT INTO x_task (" & _
"project_id, " & _
"task_subcategory_id, " & _
"task_description, " & _
"supervisor_id, " & _
"lead_id, " & _
"junior_id, " & _
"staffing_notes, " & _
"task_status_id, " & _
"hot_potato_id, " & _
"task_status_notes) " & _
"VALUES (" & _
lstProject.Value & ", " & _
lstTaskSubType.Value & ", " & _
SQLQuote(txtTaskNotes.Value) & ", " & _
lstSupervisor.Value & ", " & _
lstLead.Value & ", " & _
intJunior & ", " & _
SQLQuote(txtStaffNotes.Value) & ", " & _
lstTaskStatus.Value & ", " & _
inthotpotato & ", " & _
SQLQuoteNull(txtStatusNotes.Value) & ");"

I don't have to care about manually setting the quotes.

mfG
--> stefan <--
 
Thank you so much for your help.

Here is my final string:

insert into x_task (project_id, task_subcategory_id, task_description,
supervisor_id, lead_id, junior_id, staffing_notes, task_status_id,
hot_potato_id, task_status_notes) values (2,21,'Review
account',1,5,,'',1,7,'Jane reviewing');
 
Thank you so much for your help.

Here is my final string:

insert into x_task (project_id, task_subcategory_id, task_description,
supervisor_id, lead_id, junior_id, staffing_notes, task_status_id,
hot_potato_id, task_status_notes) values (2,21,'Review
account',1,5,,'',1,7,'Jane reviewing');
 
hi,
Here is my final string:

insert into x_task (project_id, task_subcategory_id, task_description,
supervisor_id, lead_id, junior_id, staffing_notes, task_status_id,
hot_potato_id, task_status_notes) values (2,21,'Review
account',1,5,,'',1,7,'Jane reviewing');
Click to expand...
As you may see, there is a NULL value inserted between 5,,'' which
causes the error. It seems to be the value for the field junor_id.

So use Nz(): .. ", " & Nz(intJunior, "Null") & ", " ..

How did you declare intJunior?


mfG
--> stefan <--
 
hi,
Thanks, that works! (I declared intJunior as variant)
Click to expand...
Good.

You should - at least - rename your variable to varJunior to reflect the
correct type.


mfG
--> stefan <--
 
Back
Top