SQL - Syntax Error 3075

[fat controller]

I am trying to copy data from one record to another via VBA/SQL, however I have ran into a problem.

Oddly, it has worked without issue a couple of times, but the vast majority of times I get a Syntax Error 3075, and within the error box I can see the start of the text that is contained within a text field.

The text in this field has commas in it, so I am wondering if these are upsetting the code somehow?

 

[Minty]

What's the code - and sample of the data that's causing the hang ?

 

[The_Doc_Man]

This is "Invalid Operator" which is usually caused by a bad substitution or concatenation in a code-built SQL statement. Without seeing what you did, all I can suggest is that you put a breakpoint just before you are about to execute the SQL and examine the string that you are about to execute, or put in a trap handler for that event and have it print out or otherwise display the SQL string at that moment.

This can't happen so easily for a pre-defined query (i.e. that appears as a named query in the AllQueries collection) because the syntax is checked when you store it and I don't recall that you can do concatenation in a stored query. (Parameters... yes. Dynamic concatenation... no.)

 

[fat controller]

It hasn't done it every time, but I think I have found it - one of the fields can contain dimensions in feet and inches - if those dimensions are written as 13' 3", then that seems to be the source of the failure. 13ft 3in seems to work - I will plod away and come back when I have something more meaningful... thanks guys

 

[The_Doc_Man]

If that is your problem it would make sense. Having a concatenated field implies the presence of quote marks of either flavor. Having similar (and unpaired!) quote marks as possible components of the data to be updated can cause the parser to mismatch quotes thus leaving your SQL expression with something "dangling" behind the data but before the next normal element of your SQL string. Thus the parser gets confused.

As to why that specific error (as opposed to Syntax Error or Missing Operator") just kind of depends on what comes after that concatenation.

 

[fat controller]

Well, it worked just fine for a short while, and it has lobbed its toys about again this morning - I am clearly missing something, which is not overly surprising given that I am pretty much a newbie at this.

Here is the line that is throwing up a Syntax Error (3075 - Missing Operator):

  strSQL2 = "INSERT INTO RouteRecords (Route, InBoundDest, OutBoundDest, StartDate, StreetsTraversedInbound, StreetsTraversedOutbound, Status, StandName1, StandName2, StandName3, StandName4, StandName5, StandName6, StandName7, StandName8, StandName9, StandName10, StandName11, StandName12, StandName13, StandName14, StandName15)" & "Values ('" & Route & "','" & InDest & "','" & OutDest & "','" & DFrom & "','" & TravIn & "','" & TravOut & "','" & Status & "','" & StN1 & "','" & StN2 & "','" & StN3 & "','" & StN4 & "','" & StN5 & "','" & StN6 & "','" & StN7 & "','" & StN8 & "','" & StN9 & "','" & StN10 & "','" & StN11 & "','" & StN12 & "','" & StN13 & "','" & StN14 & "','" & StN15 & "')"

All fields are text, if that makes any difference?

Also, some of those fields will be null, if that makes any difference?

 

[JHB]

..
Also, some of those fields will be null, if that makes any difference?

Then you've found the problem, because it matters.

 

[fat controller]

Ah, that I did not know! So do I have to assign some sort of value if the field is null, or can I have the null values ignored somehow?

 

[JHB]

Use the NZ function:

.... "','" & NZ(StN1,"") & "','" ....

 

[Minty]

I would probably us Nz(YourField,'') Nz replaces a null with whatever you put after the comma. https://support.office.com/en-gb/article/Nz-Function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c

And as pointed out before some function to remove any quotes would be useful.

You may be better off with a parameter query definition, as it is not so fussy about either nulls or other characters in strings.

Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("Parameter1") = Me.cmbFirstParam
qdf.Parameters("Parameter2") = Me.txtboxSecondParam 
qdf.Parameters("Parameter3") = Me.ListBoxParam3
etc, etc

Set rst = qdf.OpenRecordset

 

[fat controller]

And the learning continues! I didn't know that functions like Nz could be used in SQL. Minty, I will try using Nz first, as I can see what it is doing - I must confess that I don't fully understand what your method is doing - maybe that is something to learn for the future though.

Thank you both

 

[fat controller]

Well, I used Nz throughout, and it still fell over. Interestingly, the text that is produced is broken into two paragraphs at a specific point which is the same point with or without Nz being used on the fields.

Is there a maximum number of characters that can be handled in each field or SQL string?

 

[Minty]

There is a limit but i think its 8000 odd characters. If you have any single or double quotes, or possibly CR / LF sequences they might be tripping you up.

Sent from my Nexus 7 using Tapatalk

 

[The_Doc_Man]

StandName15)" & "Values ('

Pretty sure you need a space after that last parenthesis and before the "Values" keyword.

There is also the possibility that since you have a recordset, you COULD have a query

strSQL2 = "INSERT INTO RouteRecords (....) " & _
    "SELECT Route, InDest, OutDest, DFrom, TravIn ... " & _
    "FROM {whatever query or table contains the records} " & _
    "WHERE {put your filtration specification here if you aren't copying every record from the source} ;"

If you do that, the presence of nulls is not such a stinker. OR you could still use NZ( Route,''), NZ(Indest,''), ... (Those are two apostrophes since you can't use double-quotes so easily inside this kind of string-builder.

 

[fat controller]

Nothing like that in there, it is purely text.

I have attached a screenprint of a Word document showing the Debug.Print to show you what I mean about it splitting into paragraphs -- this is a direct copy & paste, and as you can see it just stops mid-word and chucks a couple of line breaks in.

Going by what you have suggested, does it appear that these 'phantom' line breaks are being seen as CrLf's in the code somehow? Where would they be coming from?

 

[The_Doc_Man]

Looking at the picture makes me ask the question. I'm not going to do the count, but how many characters are there in the sequence before you have the "phantom" break? If it is 255 then you blew out a string buffer, which implies truncation and all the ills that come with such a thing... particularly if the truncation occurred near the end of a longer string that had some syntax in it, not just a quoted data string.

 

[fat controller]

I've just done one at random that has quite a bit of text, and it is 1001 characters including spaces up to the point where it breaks - however, I have a feeling that this is the problem, it is simply too long blowing something out.

There are two memo fields in there - I am tempted to take them out, and then have them further on in the code as an update to the record, one at a time, to try and get round this?

 

[The_Doc_Man]

The number 1001 is equally likely as any other number for the actual data but totally UNLIKELY for the formal declaration. (I.e. people don't declare fields to be 1001 bytes long, though they might declare 1000 bytes.) Try a few more at random to see if you get phantom breaks at exactly 1001 bytes each time.

Another question comes to mind... based on this statement of yours:

There are two memo fields in there - I am tempted to take them out, and then have them further on in the code as an update to the record, one at a time, to try and get round this?

Are you updating TWO long text fields in a single query? There is a limit of 4 KB on a disk buffer and having 2 KB in your long text fields plus any other text fields (short text) and numerics can bring you to the limit pretty fast. E.g. 8 maximum-length short text fields is 2040 bytes. Add to that two 1 KB long text fields and bang, zoom, you hit the limit. If you add up the declared lengths of each field that you are trying to store, how big is the record?

 

[static]

Shepherd's Bush

As already stated, you need to check for quotes in your values.

Public Function sqlstr(ParamArray vals() As Variant) As String
    For Each s In vals
        If Len(sqlstr) Then sqlstr = sqlstr & ","
        If Len(s) Then
            sqlstr = sqlstr & "'" & Replace(s, "'", "''") & "'"
        Else
            sqlstr = sqlstr & "''"
        End If
    Next
End Function

Sub eg()
    strvalues = sqlstr(str1, str2, str3, str4)
    mysql = "insert into table1 values (" & strvalues & ")"
    CurrentDb.Execute mysql
End Sub

 

[fat controller]

As already stated, you need to check for quotes in your values.

Public Function sqlstr(ParamArray vals() As Variant) As String
    For Each s In vals
        If Len(sqlstr) Then sqlstr = sqlstr & ","
        If Len(s) Then
            sqlstr = sqlstr & "'" & Replace(s, "'", "''") & "'"
        Else
            sqlstr = sqlstr & "''"
        End If
    Next
End Function

Sub eg()
    strvalues = sqlstr(str1, str2, str3, str4)
    mysql = "insert into table1 values (" & strvalues & ")"
    CurrentDb.Execute mysql
End Sub

Having separated everything out field by field, and this is definitley the problem. Bearing in mind that I still have my 'L' plates on with this stuff, what does the above code do? It looks to me that it changes ' for "?