Solved Help with syntax error

[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

 

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