Solved StringFormatSQL Function from Northwind 2.0 shows 3464 Error on UPDATE Event

[DBUserNew]

Hello All,

I am sure you all know about this function from the Northwind 2.0. I copied the function code from the db of both (StringFormat & StringFormatSql). I have been using this for INSERT INTO & UPDATE in VBA. There were no issues. But, when I used for one of my table it gave me 3464 error on date fields. Below is the code.

strSql = StringFormatSQL("UPDATE tblName SET LoadDate = {0}, IsActive ={1}, ModifiedOn ={2} WHERE PKID = {3} And IsActive = {4};", ToAccessDate(Me.txtDate - 1), 0, ToAccessDate(Now), Me.cboPK, 1)
CurrentDb.Execute strSql, dbFailOnError

Debug.Print gave this result.

UPDATE tblName SET LoadDate = '2023-10-20', IsActive =0, ModifiedOn ='2023-10-20 11:44:01' WHERE PKID = '4' And IsActive = 1

When I replaced the function with regular concatenate method (shown below) it works just fine. No 3464 error.

strSql = "UPDATE tblName SET LoadDate =#" & Format(Me.txtDate - 1, "yyyy-mm-dd") & "#, IsActive =0, ModifiedOn =Now WHERE PKID =" & Me.cboPK & " And IsActive =1;"
CurrentDb.Execute strSql, dbFailOnError

The issue was on date fields. BUT the first method works on other tables, even UPDATE on Date field, no 3464 error.

So I am confused now. I am seeking for your expeienced advice, since the final product will be used by different users on different machines. What is the safest method? Should I replace StringFormatSql/ToAccessDate method with regular concatenate method?

Formats:
txtDate = "mmm d, yyyy"
LoadDate - Date/Time
IsActive - Number (Byte)
ModifiedOn - Date/Time
PKID - Number (Long Integer)

Office version: 2021
Working on Front End.

Public Function ToAccessDate(ByVal dt As Date) As String
    ToAccessDate = Format(dt, "yyyy-mm-dd hh:nn:ss")
End Function

 

[gemma-the-husky]

When you specifically use a date yyyy mm dd, you need to be sure your code manipulates it unambiguously, so it doesn't end up being an illegal month value for instance.

Also you are showing your date format as mmm d, yyyy. I haven't checked, but should that be DD, rather than just d. Also is the comma real, or a mistype?

I haven't tested anything, just thinking out loud.

 

[DBUserNew]

When you specifically use a date 2023-10-20 you need to ensure it gets accepted as a UK date rather than a not valid US date.

Also you are showing your dare format as mmm d, yyyy. I haven't checked, but should that be DD, rather than just d.

I haven't tested anything, just thinking out loud.

Thank you Dave for your reply.

My regional settings in in US formats.. mmm d, yyyy (only one d) is a format i set to the textbox control just to display. ToAccessDate function convert the date to yyyy-mm-dd format to work on Access.

 

[gemma-the-husky]

mmm d, yyyy (only one d) is a format i set to the textbox control just to display. ToAccessDate function convert the date to yyyy-mm-dd format to work on Access. My regional settings in in US formats.

I edited my original post after you quoted it, as I realised my original didn't really make sense. Anyway, as I say, I was just thinking out loud. What precise error is 3464? Can you trace the code and see what is going wrong?

 

[DBUserNew]

I edited my original post after you quoted it, as I realised my original didn't really make sense. Anyway, as I say, I was just thinking out loud. What precise error is 3464? Can you trace the code and see what is going wrong?

Thank you Dave,

3464: Data type mismatch in criteria expression.

When I replace the StringFormatSql function with regular concatenate method (specifying # for date field) it works. But, the StringFormatSql method in other table for UPDATE works (without specifying # for date fields). The textbox on other form has the same format (mmm d, yyyy).

This is why I confused, why does it work on some table & doesn't on this table.

 

[gemma-the-husky]

Can you just use the StringFormatSQL function with the date value you have, and see what it returns?
What date are you using. I haven't installed Northwind, but I am sure someone can check.

something like msgbox StringFormatSQL(yourdatestring) should try to run the code.

A formatting method that displays a date correctly may not be valid for a function parameter.

 

[cheekybuddha]

Can you post the actual function code for StringFormatSQL().

It doesn't look like it knows how to distinguish dates, or is outputting dates formatted for T-SQL or other RDBMS's

Please also post the code for ToAccessDate() function.

 

[DBUserNew]

Thank you All for your time,

I seen the issue. There was no FORMAT set to cboPK control (It is actually a FK). So I set its format to Standard and everything works perfect. Before it was between ' ' as a text value. This field using as WHERE in UPDATE (WHERE PKID = '4', now it is WHERE PKID = 4 )

Lesson learned: Must specify a format if the row source from data field and if they use in VBA.

David, you can see them in Northwind 2.0 from MS template. Please see them.

Thank you All for your time.

 

[sonic8]

Should I replace StringFormatSql/ToAccessDate method with regular concatenate method?

I assume, you're shooting at your own foot!
When you pass the date values through ToAccessDate before passing them on to StringFormatSql, the date values are converted to strings. How should StringFormatSql now know they should be formatted as dates in the SQL string?

 

[DBUserNew]

I assume, you're shooting at your own foot!
When you pass the date values through ToAccessDate before passing them on to StringFormatSql, the date values are converted to strings. How should StringFormatSql now know they should be formatted as dates in the SQL string?

Thank you Sonic for your time. I am a self learning student. Still learning new things every day. I don't know technical side of this function. I copied from Northwind 2.0 and learned how to use that function.

The issue was fixed in post #8. It is an unbound form. There is no record on its record source. So the combo control didn't have any format set. The row source of this combo was from a table and bound to column 1 which is a PK.

When I changed its format to Standard everything works perfect. No more 3464 error, even though this control was using as WHERE in UPDATE not on SET field. I never paid attention to WHERE and its '4' value (text data). i was completing looking on fields where SET in UPDATE, which most are date fields.

 

[cheekybuddha]

David, you can see them in Northwind 2.0 from MS template. Please see them.

Since I only have Access 2007 I am unable to get the new Northwind 2.0 template.

 

[DBUserNew]

Since I only have Access 2007 I am unable to get the new Northwind 2.0 template.

Here it is David, I changed On Error handler.

ToAccessDate

'PURPOSE:
'   Convert the date to US date format as expected by Access. Needed when building a SQL string with literal.
'   This ISO date format also has the advantage there are no issues with sorting strings, or comparing with > or <.
Public Function ToAccessDate(ByVal dt As Date) As String
10        On Error GoTo Err_Handler

20        ToAccessDate = Format(dt, "yyyy-mm-dd hh:nn:ss")

Exit_Handler:
30        Exit Function

Err_Handler:
40        clsErrorHandler.HandleError "modGlobal", "ToAccessDate"
50        Resume Exit_Handler
End Function

StringFormat

'NOTES:
'   Patterned after String.Format in .NET.
'   https://docs.microsoft.com/dotnet/api/system.string.format
'   Advantage of this function over traditional string concatenation is that you can focus on the string itself, what you want it to say.
'   Also makes it easier to use strings from a table (see GetString function).
'EXAMPLES:
'   Debug.Print StringFormat("Hello {0}. This is {1}.", "world", "ET")
'   =>  Hello world. This is ET.
'ARGUMENTS:
'   s       - String with zero or more {n} placeholders for parameter values. The first one is {0}.
'   params  - Zero or more parameters to replace the placeholders. Can also handle a paramarray that was passed in from another function taking a paramarray (e.g. GetString).
Public Function StringFormat(ByVal s As String, ParamArray params() As Variant) As String
10        On Error GoTo Err_Handler

          Dim n                   As Integer
          Dim vParams             As Variant

20        If UBound(params) = -1 Then GoTo Exit_Handler       'Zero params passed in.
30        If IsArray(params) And IsArray(params(0)) Then
40            vParams = params(0)
50        Else
60            vParams = params
70        End If

80        For n = 0 To UBound(vParams)
90            s = Replace(s, "{" & n & "}", vParams(n))
100       Next n

Exit_Handler:
110       StringFormat = s
120       Exit Function

Err_Handler:
130       clsErrorHandler.HandleError "modStrings", "StringFormat"
140       Resume Exit_Handler
End Function

StringFormatSQL

'PURPOSE:
'   Format a string using Access SQL rules.
'NOTES:
'   Better than built-in function Application.BuildCriteria which has some unexpected side-effects.
Public Function StringFormatSQL(ByVal s As String, ParamArray params() As Variant) As String
10        On Error GoTo Err_Handler

          Dim i                   As Integer
          Dim vParams             As Variant

20        If IsArray(params) And IsArray(params(0)) Then
30            vParams = params(0)
40        Else
50            vParams = params
60        End If

70        For i = LBound(vParams) To UBound(vParams)
80            If IsNull(vParams(i)) Then
90                vParams(i) = Nz(vParams(i), "NULL")
100           Else
      'NW 2.0 code to be replaced below.
      '            Select Case VarType(vParams(i))
      '                Case vbString:
      '                    vParams(i) = "'" & vParams(i) & "'"
      '                Case vbDate:
      '                    vParams(i) = "#" & vParams(i) & "#"
      '            End Select

      'NW 2.2 code will convert international number and date formats to those understood by Access.
      'We also escape string arguments, in case an argument includes an embedded single-quote (think: ...where LastName = 'O'Brien")
110               Select Case VarType(vParams(i))
                      Case vbCurrency, vbSingle, vbDouble
120                       vParams(i) = LTrim(Str(vParams(i)))                     'Str converts regional numbers to Access standards.
130                   Case vbString:
140                       vParams(i) = SINGLE_QUOTE & Replace(vParams(i), SINGLE_QUOTE, TWO_SINGLE_QUOTES) & SINGLE_QUOTE
150                   Case vbDate:
160                       vParams(i) = "#" & ToAccessDate(vParams(i)) & "#"       'ToAccessDate ensures this works for all regional settings.
170               End Select


180           End If
190       Next i

200       StringFormatSQL = StringFormat(s, vParams)

Exit_Handler:
210       Exit Function

Err_Handler:
220       clsErrorHandler.HandleError "modStrings", "StringFormatSQL"
230       Resume Exit_Handler
240       Resume
End Function

 

[cheekybuddha]

So you just needed:

strSql = StringFormatSQL("UPDATE tblName SET LoadDate = {0}, IsActive ={1}, ModifiedOn ={2} WHERE PKID = {3} And IsActive = {4};", Me.txtDate - 1, 0, Now, CLng(Me.cboPK), 1)
Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

No need to futz with formatting of combos

 

[DBUserNew]

So you just needed:

...CLng(Me.cboPK)...

No need to futz with formatting of combos

I used this CLng first as a test. When it worked, I set combos format and avoid using additional function.

So, what is the safest method? Use CLng or set combos format and reference control as normal?

 

[cheekybuddha]

The values in unbound combos and listboxes will be strings, even if they are numeric.

The StringFormatSQL() function tests the input types to determine what kind of delimiting (if any) is required.

So, you just have to ensure that the value being passed is of the correct datatype.

Whether you do that at the control's format level or the passing of parameters level is up to you.

Personally I would do it at the parameter passing stage since it's closer to where it's relevant.

I wouldn't want to come back to a combo months/years later in design view and wonder why the hell I set some weird format on it!