Mysterious date syntax error message

[Monterey_Manzer]

Hi All,



I'm having some pesky problems with an UPDATE SQL statement that I have written inside a loop. The really pesky problem is that, when I copy and paste the specific SQL statement into a blank query in Access, it returns the correct values when viewed in datasheet view. When I try to run it in VBA I get Run-time error '3075' - Syntax error in date in query expression '#'. The offending SQL Statement is shown below:

UPDATE All_MIPRS SET FY = 'FY12',DocNum = 'DWAM20812', Program = 'SMART', typeSMART = '', SMARTID = '', facRec = '', DateGen = ##, Committed = 16467, Obligated = 16467, Executed = 16170.84, Notes = '', [High Priority] = False WHERE DocNum = 'DWAM20812'

Now I'm assuming it is complaining about the "DateGen = ##" portion but as I said, when I run this in an Access query it doesn't present a problem. Do I need to add some sort of Null value check for the DateGen field in order to avoid this problem? I don't want to enter an inaccurate date. If we don't have a value for DateGen, the correct value is Null. Since this is coded inside a loop where DateGen mostly does have date values but occasionally does not, I need my code to handle both situations.


The code I'm using to run this loop is shown below. Thanks for any and all insights!

Do Until rst.EOF
        varX = DLookup("[ID]", "All_MIPRS", "[DocNum] = '" & rst![DocNum] & "'")
        
        If IsNull(varX) = True Then
            strSQL = "INSERT INTO All_MIPRS (FY, DocNum, Program, typeSMART, " & _
                     "SMARTID, facRec, DateGen, Committed, Obligated, Executed, " & _
                     "Notes, [High Priority]) VALUES ('" & rst![FY] & "', '" & _
                     rst![DocNum] & " ', '" & rst![Program] & "','" & _
                     rst![typeSMART] & "', '" & rst![SMARTID] & "'," & rst![facRec] & ", #" & _
                     rst![DateGen] & "#, " & rst![Committed] & ", " & rst![Obligated] & ", " & _
                     rst![Executed] & ",'" & _
                     rst![Notes] & "', " & rst![High Priority] & ")"
        ElseIf IsNull(varX) = False Then
            strSQL = "UPDATE All_MIPRS SET FY = '" & rst![FY] & "'," & _
                     "DocNum = '" & rst![DocNum] & "', " & _
                     "Program = '" & rst![Program] & "', typeSMART = '" & rst![typeSMART] & "', " & _
                     "SMARTID = '" & rst![SMARTID] & "', facRec = '" & rst![facRec] & "', " & _
                     "DateGen = #" & rst![DateGen] & "#, Committed = " & rst![Committed] & ", " & _
                     "Obligated = " & rst![Obligated] & ", Executed = " & rst![Executed] & ", " & _
                     "Notes = '" & rst![Notes] & "', " & _
                     "[High Priority] = " & rst![High Priority] & " " & _
                     "WHERE DocNum = '" & rst![DocNum] & "'"
        End If
        Debug.Print strSQL
        CurrentDb.Execute (strSQL)
    
        rst.MoveNext
    Loop

[ItsMe]

I guess that should work. It seems like you are coming up against a syntax issue. So at your line continuations something like

rst![FY] & "', '" & _
should have some space like
rst![FY] & "', ' " & _

this line may be OK
& rst![facRec] & ", #"
because it has a variable directly after it in the continued line.

You could place a debug.print line of code directly after your sql string and see what it looks like in the immediate window.

Having said that, Maybe you can squish your Dlookup into your SQL, resulting in a select query. Put your select query into a second DAO recordset. Or even merge that resulting SELECT query into the DAO rst in a way that was updateble and have a single iteration with a loop.

Kinda looks like duplicate data which is causing a couple small hurdles.

[June7]

No data is provided for fields. The empty strings for the text fields will probably work if you allow empty strings in tables (I don't) but the VBA/sql engine can't deal with empty ##.

An IIf for the date value might work.

[ItsMe]

No data is provided for fields. The empty strings for the text fields will probably work if you allow empty strings in tables (I don't) but the sql engine can't deal with empty ##.

I guess I should have read the post more closely rather than looking at the code

[Monterey_Manzer]

Unfortunately I'm helping another coworker clean up someone else's mess and in some cases we just don't have all the information we would like but we still need the record. In these cases the empty strings are in themselves data since they tell us where we are lacking information.

So it sounds like I'll have to build in Iif statements that change the syntax of both the INSERT and UPDATE SQL statements that avoids these empty date fields or can I just set the DateGen value to NULL?

[June7]

A Null field tells me information is lacking. I just don't like empty strings in tables.

No, I don't think Null will work with ##.

"DateGen =" & IIf(IsNull(rst![DateGen]), Null, "#" & rst![DateGen] & "#") & ", Committed=" &

[Monterey_Manzer]

June,

Tried the string you provided and I get another syntax error. The SQL Statement output by the Debug.Print strSQL command just prior to CurrentDb.Execute command is as follows:
UPDATE All_MIPRS SET FY = 'FY12',DocNum = 'DWAM20812', Program = 'SMART', typeSMART = '', SMARTID = '', facRec = '', DateGen =, Committed=Obligated = 16467, Executed = 16170.84, Notes = '', [High Priority] = False WHERE DocNum = 'DWAM20812'

It doesn't seem to the like the "DateGen =,". I would really like to insert Null values rather than empty strings and other situations but I keep running into these errors.

[June7]

Try putting the word Null between quote marks. I just check some of my code and that works for me.

Without apostrophe delimiters the engine will process as Null value, not a string 'Null'.

[Monterey_Manzer]

Thanks June!

This is the Iif statement I have now:

IIf(IsNull(rst![DateGen]), "Null", "#" & rst![DateGen] & "#")

This works great. I will start applying this also to any Null values to avoid creating empty strings. I think you might have just helped me in a big way to cleaning up my data tables.

Thanks again!

[ItsMe]

Are you sure that is not inserting a literal string? What is the data type of the field? Interresting

[Monterey_Manzer]

I double checked the update. After the query is run, that field comes up with IsNull = True. I think you would only insert a literal Null string if you included the single quotation marks (remember this is written in the VB Editor). The full SQL statement comes out like this:

UPDATE All_MIPRS SET FY = 'FY12',DocNum = 'DWAM20812', Program = 'SMART', typeSMART = '', SMARTID = '', facRec = '', DateGen = Null ,Committed = 16467,Obligated = 16467, Executed = 16170.84, Notes = '', [High Priority] = False WHERE DocNum = 'DWAM20812'

[June7]

I am sure. It's been working for years. Without the apostrophe delimiters VBA/sql will recognize the Null keyword/function and process Null. Same thing works for other intrinsic functions like Date(), Year(), Left(), Right(), Mid(), etc.

It works for text, date, number fields. Multi-value field and other data types (OLE, Attachment) might have an issue.

[ItsMe]

Good to know. Thanks for posting and mentioning the double quotes thing too!