Syntax error in query expression '#'

[swmorin]

CurrentDb.Execute ("UPDATE tbl_instructor_dates " & _
"SET tbl_instructor_dates.dagb = #" & [Forms]![frm_instructor_misc_info_update]![DaGB] & "# " & _
",tbl_instructor_dates.[281type] = " & [Forms]![frm_instructor_misc_info_update]![281_type] & " " & _
",tbl_instructor_dates.fisdcw = #" & [Forms]![frm_instructor_misc_info_update]![FISDcw] & "# " & _
",tbl_instructor_dates.oandtcw = #" & [Forms]![frm_instructor_misc_info_update]![OandTcw] & "#" & _
",tbl_instructor_dates.TWcw = #" & [Forms]![frm_instructor_misc_info_update]![TWcw] & "# " & _
",tbl_instructor_dates.REBICcw = #" & [Forms]![frm_instructor_misc_info_update]![REBICcw] & "# " & _
",tbl_instructor_dates.isdcw = #" & [Forms]![frm_instructor_misc_info_update]![ISDcw] & "# " & _
",tbl_instructor_dates.tscw = #" & [Forms]![frm_instructor_misc_info_update]![TScw] & "# " & _
",tbl_instructor_dates.last281 = #" & [Forms]![frm_instructor_misc_info_update]![DaGB] & "# " & _
",tbl_instructor_dates.next281 = #" & DateAdd("d", [Forms]![frm_instructor_misc_info_update]![281_days], [Forms]![frm_instructor_misc_info_update]![DaGB]) & "# " & _
"WHERE tbl_instructor_dates.InstructorID = " & [Forms]![frm_instructor_misc_info_update]![INSTRUCTORID] & ";")

 

[spikepl]

The seqence of doing SQL in code, unless you are familiar with it, goes like this:

1. Do NOT do everything in one shot!

2. Write it like this:
Dim strSQL As String

strSQL ="whatever SQL"

debug.print strSQL ' this lets you inspect the actual content in the Immediate Window

CurrentDb.Execute (strSQL), dbfailonerror

3. Take the simplest part of the SQL, just concerning one field. Make that work.

4. Add the next field, make that work.. and so on.

 

[swmorin]

The issue seems to exist with the highlighted lines

CurrentDb.Execute ("UPDATE tbl_instructor_dates " & _
"SET tbl_instructor_dates.dagb = #" & [Forms]![frm_instructor_misc_info_update]![DaGB] & "# " & _
",tbl_instructor_dates.[281type] = " & [Forms]![frm_instructor_misc_info_update]![281_type] & " " & _
",tbl_instructor_dates.fisdcw = #" & [Forms]![frm_instructor_misc_info_update]![FISDcw] & "# " & _
",tbl_instructor_dates.oandtcw = #" & [Forms]![frm_instructor_misc_info_update]![OandTcw] & "#" & _
",tbl_instructor_dates.TWcw = #" & [Forms]![frm_instructor_misc_info_update]![TWcw] & "# " & _
[COLOR=red][B]",tbl_instructor_dates.REBICcw = #" & [Forms]![frm_instructor_misc_info_update]![REBICcw] & "# " & _[/B][/COLOR]
[B][COLOR=red]",tbl_instructor_dates.isdcw = #" & [Forms]![frm_instructor_misc_info_update]![ISDcw] & "# " & _[/COLOR][/B]
[B][COLOR=red]",tbl_instructor_dates.tscw = #" & [Forms]![frm_instructor_misc_info_update]![TScw] & "# " & _[/COLOR][/B]
",tbl_instructor_dates.last281 = #" & [Forms]![frm_instructor_misc_info_update]![DaGB] & "# " & _
",tbl_instructor_dates.next281 = #" & DateAdd("d", [Forms]![frm_instructor_misc_info_update]![281_days], [Forms]![frm_instructor_misc_info_update]![DaGB]) & "# " & _
"WHERE tbl_instructor_dates.InstructorID = " & [Forms]![frm_instructor_misc_info_update]![INSTRUCTORID] & ";")

 

[jdraw]

spikepl gave you good advice for working with sql in vba.
Did you read and try what he told you?

 

[swmorin]

I did.. I took all the lines out and put them in one at a time. These three lines give me the syntax error in expresion '#'.

Those lines are basically identical to all the other lines so I don't know what I am doing wrong. All three lines are tied to a date field, and the text boxes associated are also specified as date formats.

 

[jdraw]

Comment out the db.execute
make a text variable = the Update sql
Try doing debug.print of the sql

try only 1 field, see what the sql looks like
add a field, see the rendered sql

repeat until all the fields have been included and the sql seems correct

then do the db.execute

Also, remove the brackets from around the UPDATE query

syntax db.execute sqlstring,dbfailonerror

 

[swmorin]

It turns out I am an idiot the issue is that sometimes these fields are null, and when they are null I get the error in the subject line.

How do I fix that?