Solved Help with syntax error (1 Viewer)

[duke217]

Hi all,

I am getting a syntax error when I try to execute this piece of code:

DoCmd.RunSQL "INSERT INTO tblLogins " _
            & "(lkpUser, lstAppName, lstLogType, lkpWorkstation, chrPubIP, chrLocIP, chrLogInLocation, chrLoginRegion, chrLoginCountry, chrTimeZone, chrTimeStampUTC, chrSSID) VALUES )" _
            & "(" & chrUserLogin & ", 'In', " & chrWorkstation & ", " & chrPublicIP & ", " & chrLocalIP & ", " & chrCurrentGeoLoc & ", " & chrGeoAera & ", " & chrCountry & ", " & lngUtcOffset & ", " & Now() & ", " & chrServiceSet & ");"

Can anybody help? It probably has to do with separators or parens, but my eyes are tired, I just can't figure it out

Thanks a lot

 

[Minty]

Think you have to add some # around your date field;

dim strSQL as string

strSQL = "INSERT INTO tblLogins "
strSQL = strsql & " (lkpUser, lstAppName, lstLogType, lkpWorkstation, chrPubIP, chrLocIP, chrLogInLocation, chrLoginRegion, chrLoginCountry, chrTimeZone, chrTimeStampUTC, chrSSID) "
strSQL = strsql & "    VALUES ("  & chrUserLogin & ", 'In', " & chrWorkstation & ", " & chrPublicIP & ", " & chrLocalIP & ", " & chrCurrentGeoLoc & ", " & chrGeoAera & ", " & chrCountry & ", " & lngUtcOffset & ", #" & Now() & "#, " & chrServiceSet & "); "
debug.Print strSQL

DoCmd.RunSQL strSQL

 

[Uncle Gizmo]

Can anybody help? It probably has to do with separators or parens, but my eyes are tired, I just can't figure it out

You are not the only one! This is a recurring issue on AWF. I call it "The Notorious Insert Into SQL Statement" and I have blogged about it on my website here.

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

Minty has hit the nail on the head and given you a workable solution. However I strongly recommend you have a look at my blog where I offer a much simpler way of avoiding this syntax problem.

My method keeps the insert into SQL statement as a simple string which is an identical, repeatable sequence, which makes very easy to edit and manipulate if you have to add any extra Fields.

Then the delimiters for dates and text are added to a variable which is then placed within the insert into SQL string.

By dividing and conquering, by simplifying the process into logical easily understandable steps, you will always get your insert into SQL statement correct every time.

 

[Gasman]

Plus you should put all that into a string variable, then you can Debug.Print it and see where the error is?

Didi you notice Minty did exactly that?

 

[CJ_London]

suspect you have not provided delimiters for text. for example perhaps this

& ", " & chrCountry & ", " &

should be

& ", '" & chrCountry & "', " &

if that's the case you wont get a syntax error, but you will get a runtime error

 

[onur_can]

Hi all,

I am getting a syntax error when I try to execute this piece of code:

DoCmd.RunSQL "INSERT INTO tblLogins " _
            & "(lkpUser, lstAppName, lstLogType, lkpWorkstation, chrPubIP, chrLocIP, chrLogInLocation, chrLoginRegion, chrLoginCountry, chrTimeZone, chrTimeStampUTC, chrSSID) VALUES )" _
            & "(" & chrUserLogin & ", 'In', " & chrWorkstation & ", " & chrPublicIP & ", " & chrLocalIP & ", " & chrCurrentGeoLoc & ", " & chrGeoAera & ", " & chrCountry & ", " & lngUtcOffset & ", " & Now() & ", " & chrServiceSet & ");"

Can anybody help? It probably has to do with separators or parens, but my eyes are tired, I just can't figure it out

Thanks a lot

VALUES )
Do you think the red brackets are correct?
Minty seems to have fixed this issue.

 

[MajP]

Everything you need to make this super simple. Never have to worry about delimiters again. Look at the functions to make parameter queries and insert queries. Pass in the controls and get a formatted insert query or simply use the paramater query.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[MajP]

To demonstrate how easy this module makes things. Here is a complex insert test case. In real life you would likely use controls and not variables

Public Sub TestInsertQryValues()
  Dim flds As String
  Dim Vals As String
  Dim strSqlInsert As String
  Dim lngInStock
   'demoing a number literal and number in a variable
  lngInStock = 2500
  flds = "'FirstName', 'LastName', 'OrderID', 'OrderDate', 'OrderQuantity', 'InStock'"
  Vals = CreateInsertQueryValues(CSql("Mike"), CSql("O'Brien"), Null, CSql(Date, sdt_date), 100, lngInStock)
  Debug.Print Vals
  strSqlInsert = CreateInsertQuery("MyTable", flds, Vals, "LastName = " & CSql("jones"))
  Debug.Print strSqlInsert
End Sub

Result

INSERT INTO MyTable('FirstName', 'LastName', 'OrderID', 'OrderDate', 'OrderQuantity', 'InStock') VALUES ('Mike', 'O''Brien', NULL, #12/18/2020#, 100, 2500) WHERE LastName = 'jones'

using my module

  Dim flds As String
  Dim Vals As String
  Dim strSqlInsert As String
 
 flds = "'lkpUser', 'lstAppName', 'lstLogType', 'lkpWorkstation', 'chrPubIP', 'chrLocIP', 'chrLogInLocation', 'chrLoginRegion', 'chrLoginCountry', 'chrTimeZone', 'chrTimeStampUTC', 'chrSSID'"
 
 vals = CreateInsertQueryValues(CSql(chrUserLogin), CSql('In'), CSql(chrWorkstation ),CSql(chrPublicIP),... CSql(chrServiceSet))
 
  strSqlInsert = CreateInsertQuery("tblLogins", flds, Vals)
  currentdb.execute strSqlInsert

 

[duke217]

Thanks a lot everybody, problem solved!