Insert Into Syntax issue

[TimTDP]

I have been staring at this for hours and can't see the syntax problem

CurrentDb.Execute "INSERT INTO tblBackEndLocation(BackendLocation, ComputerDescription, dDate) VALUES " & _
                    "('" & pubFilePathName & "', '" & strQuestion & "', #" & Now() & "#)", dbFailOnError

pubFilePathName & strQuestion as both strings

Where is my syntax problem?

Thanks in advance

 

[Minty]

Write the concatenation into a string variable and Debug.Print it;

Dim strSQL as String

strSQL = "INSERT INTO tblBackEndLocation(BackendLocation, ComputerDescription, dDate) VALUES " & _
                    "('" & pubFilePathName & "', '" & strQuestion & "', #" & Now() & "#)"

Debug.print strSQL

CurrentDb.Execute strSQL, dbFailOnError

Examine the actual string in the immediate window of the VBA editor (Ctrl + G to open it in the editor)
(it might not like the lack of a space between tblBackEndLocation(B

 

[MajP]

dim strSql as string
strSql =  "INSERT INTO tblBackEndLocation(BackendLocation, ComputerDescription, dDate) VALUES " & _
                    "('" & pubFilePathName & "', '" & strQuestion & "', #" & Now() & "#)"
debug.print StrSql

report back and you show us what is wrong.

My guess is you need a format function on that Now()

Format(Now + 0.5, "\#mm\/dd\/yyyy hh\:nn\:ss\#")

 

[The_Doc_Man]

The best way to answer that is to make that into a string that you can then print out to SEE what you telling Access to execute.
So assign the SQL part to a string and then execute the string.

EDIT: Minty beat me to it by seconds
EDIT #2: And MajP as well.

 

[MajP]

Minty beat me.

 

[Minty]

At last, marginally faster fingers !!!

 

[TimTDP]

Thanks Minty
Debug print gives me:
INSERT INTO tblBackEndLocation(BackendLocation, ComputerDescription, dDate) VALUES ('C:\Users\timpe\OneDrive\Data\MyData.accdb', 'Tim Laptop', #27/04/2021 15:31:33#)

Which looks fine to me?

 

[MajP]

No. That is not a valid SQL date. There is no month 27. Use the formatting I provided.

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

 

[TimTDP]

Thanks MajP

Format function worked.

But why the Now + 0.5? Date saved is tomorrow!

 

[MajP]

Sorry take that out. I wanted to test PM values.

 

[TimTDP]

Thanks very much

 

[MajP]

FYI,
I use a CSQL function for all of my values. Saves a lot of time and gets it correct. If you are referencing a variant datattype (anything coming from a control) no need to specify the datatype. It will know if it is a string, numeric or date.

Public Enum SQL_DataType
  sdt_boundfield = -1
  sdt_UseSubType = 0
  sdt_text = 1
  sdt_Numeric = 2
  sdt_date = 3
  sdt_Boolean = 4
  sdt_Null = 5
End Enum
'**************************************************************************************************************
'----------------------------------------Convert To Delimited SQL When Datatype can be Determined -------------
'**************************************************************************************************************


Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String
   
    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case varType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If
       
        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                  If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = str(Value)
                   'debug.print Sql
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If
   
End Function

I also use this function for my inserts

Public Function CreateInsertQuery(TableName As String, FieldNames As String, Values As String, Optional Criteria As String = "") As String
  Dim strSql As String
  If Left(FieldNames, 1) <> "(" Then FieldNames = "(" & FieldNames & ")"
  strSql = "INSERT INTO " & TableName & "(" & FieldNames & ") VALUES " & Values
  If Criteria <> "" Then
    strSql = strSql & " WHERE " & Criteria
  End If
  CreateInsertQuery = strSql
End Function

So if I want to do your insert
INSERT INTO tblBackEndLocation(BackendLocation, ComputerDescription, dDate) VALUES " & _
"('" & pubFilePathName & "', '" & strQuestion & "', #" & Now() & "#)"

Dim StrSql as string
StrSql = CreateInsertQuery("tblBackEndLocation","BackendLocation,ComputerDescription,dValues",cSql(pubfilePathName), csql(StrQuestion),csql(Now))

Does not save a lot on this, but does when you have lots of dates, strings, numerics, booleans, and null values.

 

[Isaac]

dim strSql as string
strSql =  "INSERT INTO tblBackEndLocation(BackendLocation, ComputerDescription, dDate) VALUES " & _
                    "('" & pubFilePathName & "', '" & strQuestion & "', #" & Now() & "#)"
debug.print StrSql

report back and you show us what is wrong.

My guess is you need a format function on that Now()

Format(Now + 0.5, "\#mm\/dd\/yyyy hh\:nn\:ss\#")

Can you explain why some coders use \#mm\/dd\/yyyy ? I do see this a lot. I usually just use Format(date,"mm/dd/yyyy")

Is the \/ way better?

 

[MajP]

The format function has A LOT of features. See this.

Format function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

I think your question is what is \. It is in the article

\ Display the next character in the format string. To display a character that has special meaning as a literal character, precede it with a backslash (\). The backslash itself isn't displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (\\). Examples of characters that can't be displayed as literal characters are the date-formatting and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, and , the numeric-formatting characters (#, 0, %, E, e, comma, and period), and the string-formatting characters (@, &, <, >, and !).

 

[Isaac]

So I guess the main helpfulness of the longer syntax is the ability to neatly add the literal octothorpes right there in the expression, versus doing
"#" & format(now,"mm/dd/yyyy") & "#"

That makes sense, I have noticed Format is chock-full with functionality, but had never known that about the \, thanks for the info. This makes me think of the extreme fungibility of .net expressions - good job MS on how powerful Format is for vba.

 

[MajP]

So I guess the main helpfulness of the longer syntax is the ability to neatly add the literal octothorpes right there in the expression, versus doing
"#" & format(now,"mm/dd/yyyy") & "#"

Although the documentation says you can not display the literal \ , it seems to work in most cases I know without \|. So that is confusing. This does work
Format(Date, "\#mm/dd/yyyy\#")

 

[Minty]

I always now use yyyy-mm-dd as that is also valid (with different delimiters) in SQL server.

It removes any ambiguity and I always know what the data I'm looking at is.

 

[Isaac]

I always now use yyyy-mm-dd as that is also valid (with different delimiters) in SQL server.

It removes any ambiguity and I always know what the data I'm looking at is.

I agree - in sql I always use 'YYYY-MM-DD'

 

[Minty]

Hi @Pat ,
I ran a DLookup () and I matched the criteria

  strsql = "UPDATE table1 SET StartDate =  Now()  WHERE ID = 4"
 
  CurrentDb.Execute strsql

This does indeed correctly insert the date into the date field, I changed the system date on the laptop to 5th April and it also put in the correct time and date for here 05/04/2021 .

Both Date() and Now() work as expected.