Syntax error on Update

[gmazza via AccessMonster.com]

Hey there,
I'm going crazy in my head over this one.
I am getting a syntax error on my Update.
Here is a little background followed by code.
SubmitDate is a Text field with a yyyy-mm-dd format.
Submit is Dimmed as String
I am importing a .csv file into table 620ExportTemp
After receiving the file, the SubmitDate has nothing in it.
Here is my sample code in the middle of my open recordset on the same table.

If fld.Name = "PromoId" Then
If IsNull(fld.Value) Then
Else
Promo = fld.Value
End If
End If

If fld.Name = "SubmitDate" Then
If IsNull(fld.Value) Then
Submit = ""
Else
Submit = fld.Value
End If
End If

Next fld
rs.MoveNext

sSql = "UPDATE 620ExportTemp Set SubmitDate = " & Submit & _
"Where PromoId = '" & Promo & "';"

CurrentDb.Execute sSql, dbFailOnError

I keep getting a syntax error on this update no matter if I add a single
quote behind like:
sSql = "UPDATE 620ExportTemp Set SubmitDate = '" & Submit & _

If I put:
sSql = "UPDATE 620ExportTemp Set SubmitDate = " & "1 " & _
then it works, but I don't want a 1 in the table, I want a blank, ""

Reason for this is I am inserting these records into another table and when I
look at the other table after insert, the SubmitDate = 1899-12-30 for all
records which makes no sense because after I imported the file the
SubmitDate's were ALL blank.

P.S. I Have NO fields ANYWHERE that are a date, they are all text.
Please help!

 

[Rick Brandt]

Hey there,
I'm going crazy in my head over this one.
I am getting a syntax error on my Update.
Here is a little background followed by code.
SubmitDate is a Text field with a yyyy-mm-dd format.
Submit is Dimmed as String
I am importing a .csv file into table 620ExportTemp
After receiving the file, the SubmitDate has nothing in it.
Here is my sample code in the middle of my open recordset on the same
table.

If fld.Name = "PromoId" Then
If IsNull(fld.Value) Then
Else
Promo = fld.Value
End If
End If

If fld.Name = "SubmitDate" Then
If IsNull(fld.Value) Then
Submit = ""
Else
Submit = fld.Value
End If
End If

Next fld
rs.MoveNext

sSql = "UPDATE 620ExportTemp Set SubmitDate = " & Submit & _
"Where PromoId = '" & Promo & "';"

CurrentDb.Execute sSql, dbFailOnError

I keep getting a syntax error on this update no matter if I add a single
quote behind like:
sSql = "UPDATE 620ExportTemp Set SubmitDate = '" & Submit & _

If I put:
sSql = "UPDATE 620ExportTemp Set SubmitDate = " & "1 " & _
then it works, but I don't want a 1 in the table, I want a blank, ""

Reason for this is I am inserting these records into another table and
when I look at the other table after insert, the SubmitDate = 1899-12-30
for all records which makes no sense because after I imported the file the
SubmitDate's were ALL blank.

P.S. I Have NO fields ANYWHERE that are a date, they are all text.
Please help!

You need a quote on BOTH sides of the value...

sSql = "UPDATE 620ExportTemp Set SubmitDate = '" & Submit & "' " & _
"Where PromoId = '" & Promo & "';"

 

[gmazza via AccessMonster.com]

Thanks Rick, I really appreciate it! Works great now.

Hey there,
I'm going crazy in my head over this one.

[quoted text clipped - 45 lines]

P.S. I Have NO fields ANYWHERE that are a date, they are all text.
Please help!

You need a quote on BOTH sides of the value...

sSql = "UPDATE 620ExportTemp Set SubmitDate = '" & Submit & "' " & _
"Where PromoId = '" & Promo & "';"