Insert Into Syntax issue (1 Viewer)

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

 

[Pat Hartman]

You never posted the string as printed to the debug window so we never got to see it as built.


I would have written the expression as:

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

You should only format dates in two situations:
1. You want to display them for human consumption and you don't want to use the system default format
2. You are constructing an SQL string and your standard Windows date format is NOT mm/dd/yyyy.

When you pass a string into SQL, SQL assumes mm/dd/yyyy format unless you use the unambiguous yyyy/mm/dd. To prevent SQL Server from interpreting your dd/mm/yyyy default format incorrectly, you use the Format() function to coerce the date into mm/dd/yyyy or yyyy/mm/dd format.

I avoid the issue whenever possible by simply not formatting dates when it is not necessary. Since Now() is a function that returns a date, SQL will handle the date correctly.

And one more thing. Use Date() if you want only the date but use Now() when you want the date plus the time of day.

Please, someone in the UK or elsewhere where dd/mm/yyyy is the format standard test this example and confirm that not turning the date into a string works and SQL formats it correctly. You'll have to wait until the first week of May before trying. does 5/1/2021 end up as Jan 5 or May 1?.

 

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