Syntax error in Update statement

[aikidokid]

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

            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

[kfcSmitty]

what is the error?

[aikidokid]

what is the error?

As in the thread title
Syntax Error in Update Statement

[kfcSmitty]

Oh, it seems you have a comma just before your "where", remove it.

[aikidokid]

Oh, it seems you have a comma just before your "where", remove it.

I have removed it and still the same error.

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

[si_the_geek]

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).

[aikidokid]

These date really get me

I thought as GradeDateHistory was declared as a variant it was different!

I also thought that I had given the value a Null here:

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

[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.


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).

[aikidokid]

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.

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.

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!

[si_the_geek]

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 ) 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.

[aikidokid]

The FAQ you want (as nobody has done a proper one yet, including me ) 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