Syntax error in Update statement
I am getting the error in the above statement.
I have tried adding apostrophes for each value but still the same error.
GradeHistoryDates holds a date, but is declared as a Variant
Code:
strSQL = "UPDATE tbl_Grade SET " & _
"Yellow = " & GradeHistoryDates(0) & ", " & _
"Orange = " & GradeHistoryDates(1) & ", " & _
"Green = " & GradeHistoryDates(2) & ", " & _
"GreenBlack = " & GradeHistoryDates(3) & ", " & _
"Blue = " & GradeHistoryDates(4) & ", " & _
"BlueBlack = " & GradeHistoryDates(5) & ", " & _
"Brown = " & GradeHistoryDates(6) & ", " & _
"First = " & GradeHistoryDates(7) & ", " & _
"Second = " & GradeHistoryDates(8) & ", " & _
"Third = " & GradeHistoryDates(9) & ", " & _
"Fourth = " & GradeHistoryDates(10) & ", " & _
"Fifth = " & GradeHistoryDates(11) & ", " & _
"Sixth = " & GradeHistoryDates(12) & ", " & _
"Seventh = " & GradeHistoryDates(13) & ", " & _
"Eighth = " & GradeHistoryDates(14) & ", " & _
"Where Memb_ID = " & MembID
Debug.Print strSQL
cn.Execute strSQL
Re: Syntax error in Update statement
Re: Syntax error in Update statement
Quote:
Originally Posted by kfcSmitty
what is the error?
As in the thread title ;)
Syntax Error in Update Statement
Re: Syntax error in Update statement
Oh, it seems you have a comma just before your "where", remove it.
Re: Syntax error in Update statement
Quote:
Originally Posted by kfcSmitty
Oh, it seems you have a comma just before your "where", remove it.
I have removed it and still the same error.
Quote:
Originally Posted by Debug.Print
UPDATE tbl_Grade SET Yellow = 27/05/2005, Orange = 19/07/2006, Green = 12/09/2007, GreenBlack = , Blue = 31/01/2008, BlueBlack = , Brown = , First = , Second = , Third = , Fourth = , Fifth = , Sixth = , Seventh = , Eighth = Where Memb_ID = 26
Re: Syntax error in Update statement
That isn't how you specify dates in SQL statements, and you already know where to find the right way. ;)
In addition, you need a value of some sort after each = sign, even if it is the keyword Null (without delimiters).
Re: Syntax error in Update statement
These date really get me :lol:
I thought as GradeDateHistory was declared as a variant it was different!
I also thought that I had given the value a Null here:
Code:
Public GradeHistoryDates(14) As Variant
For i = 0 To 14
If chkHistory(i).Value = vbChecked Then
GradeHistoryDates(i) = dtpHistory(i).Value
Else
GradeHistoryDates(i) = Null
End If
Next i
Re: Syntax error in Update statement
That just put the value Null (rather than the text "Null") into your array.. when appended to a String (like strSQL), a Null disappears.
Declaring something as Variant doesn't help much.. it just means you can put different data types into it (as well as special values like Null and Empty).
You still need to build SQL statements in the same way, unless of course you use a Command object (which understands VB's Null, and will format & delimit the dates for you).
Re: Syntax error in Update statement
Quote:
Originally Posted by si_the_geek
That just put the value Null (rather than the text "Null") into your array.. when appended to a String (like strSQL), a Null disappears.
OK, didn't know that thanks.
Quote:
Originally Posted by si_the_geek
Declaring something as Variant doesn't help much.. it just means you can put different data types into it (as well as special values like Null and Empty).
I was suggested to use a variant type by brucevbe as I couldn't get it to work as a Date type.
Quote:
Originally Posted by si_the_geek
You still need to build SQL statements in the same way, unless of course you use a Command object (which understands VB's Null, and will format & delimit the dates for you).
I think it's about time I looked into the Command Object.
I will pop over to the FAQ's for a while I think! ;)
Re: Syntax error in Update statement
Quote:
Originally Posted by aikidokid
I was suggested to use a variant type by brucevbe as I couldn't get it to work as a Date type.
That's what you need if you want to store "no date selected" in VB, unless you get more complex (like perhaps a separate boolean array for "is a date selected?").
That Variant array (including those Nulls) will work fine with a Command object.
The FAQ you want (as nobody has done a proper one yet, including me :blush: ) is How can I add a record to a database? , which shows an identical Insert statement being done with just conn.Execute , and with a Command object.
Re: Syntax error in Update statement
Quote:
Originally Posted by si_the_geek
The FAQ you want (as nobody has done a proper one yet, including me :blush: ) is
How can I add a record to a database? , which shows an identical Insert statement being done with just conn.Execute , and with a Command object.
Been playing with this for an hour, and thought I better post a new thread ;)
