field variables in sql string (6 Viewers)

[Imb]

My only observation is that "as_date" and "as_String" rely on a "Null" value but don't consider an empty string.
Maybe "NZ()" rather than "isNull" would make them more reliable.

Hi John,

Thank you.
These codes are some 40 years old, and I have never found a signal that there was something missing. But adept them for your purposes.
Your example was for retrieving records based on field-values, and in the tables I don't use ZLS-values.
Internally I work with controls having a value (control > "") or controls having no value (control & "" = "").
Probably I convert ZLS-values to Null's before inserting in the SQL-string.

But I will reconsider it in my environment.

At the end I replace all "= Null" by "IS NULL"


What I now also see is that As_text has an optional argument "on_null" that is not used. Dropping that argument "could" give some compile-errors in one of my 100+ applications. I will check that.

 

[mdlueck]

Using Parameters eliminates the need to cleanup the text and the possibility of injection.

Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
    ' I don't know where oDB is, so I used CurrentDb
    ' See the new ,Nones in insert and [sStr] as NewNote
    With CurrentDb.CreateQueryDef(vbNullString, _
                    "PARAMETERS " & _
                    "dFamily Text (255), " & _
                    "sFamily Text (255), " & _
                    "nAccno IEEEDouble, " & _
                    "sStr Text (255); " & _
                "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, [sStr] as NewNote " & _
                "FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
                "WHERE A.Main_Family =[sFamily] " & _
                "AND B.Accession = [nAccno] " & _
                "AND A.Raw_Box = B.boxno " & _
                "AND B.Main_Family = [dFamily];")
        ' I Add "AND B.Main_Family = [dFamily]" for
        ' "Main_Family" field needs to be controlled by the variable "dFamily"
        .Parameters("dFamily") = dFamily
        .Parameters("sFamily") = sFamily
        .Parameters("nAccno") = nAccno
        .Parameters("sStr") = sStr
        .Execute dbFailOnError
    End With
End Sub

ADODB Command and Parameter example here:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects

There are very few examples to be found on the Internet of using an ADODB.Command with ADODB.Parameters objects to issue a SQL INSERT statement to an Access table. Here is a working example! :D Public Function Insert() As Boolean On Error GoTo Err_Insert Dim adoCMD As Object Dim adoRS As...

www.access-programmers.co.uk

I am thankful,

 

[RonPaii]

Place these functions in a general module. Modify/extend these functions if necessary.

Function As_date(cur_datum As Variant) As String
  ' ISO-format:  yyyy-mm-dd
  If (IsNull(cur_datum)) Then
    As_date = "Null"
  Else
    As_date = "#" & Format(cur_datum, "yyyy-mm-dd") & "#"
  End If
End Function

Function As_text(cur_text As Variant, Optional on_null As String) As String
  If (IsNull(cur_text)) Then
    As_text = "Null"
  Else
    As_text = "'" & Replace(cur_text, "'", "''") & "'"
  End If
End Function

Function As_real(cur_value) As String
  As_real = Replace(cur_value, ",", ".")
End Function

Trying to handle reginal issues with string processing looks like a never ending chase. Work with your values in native types and let Microsoft handle the reginal issues. Wherever possible I use typed parameters in queries and process nulls using functions to return a typed value.

For example my date function. (NULLDATE is a date constant that I use for the earliest date allowed in the application, returning NULLDATAE would be as an error condition)

'----------------------------
' Check for Null or non date string, returns vValue as a date
'
'   vValue          Value to check
'   dateDefault     Value to return if not date make optional
'   defaultIsMin    Return default if vValue is before default
'
Public Function DateNZ(ByVal vValue As Variant, _
              Optional ByVal DateDefault As Date = NULLDATE, _
              Optional ByVal DefaultIsMin As Boolean = False) As Date
    On Error GoTo errdateNZ
    Dim ValueConvertedToDate As Date
    If IsNull(vValue) Then
        ValueConvertedToDate = DateDefault
    ElseIf IsDate(vValue) Then
        If CDate(vValue) <> NULLDATE Then
            ValueConvertedToDate = vValue
        Else
            ValueConvertedToDate = DateDefault
        End If
    Else
        ValueConvertedToDate = DateDefault
    End If
    If DefaultIsMin Then
        If ValueConvertedToDate < DateDefault Then
            ValueConvertedToDate = DateDefault
        End If
    End If
doneDateNZ:
    DateNZ = ValueConvertedToDate
    Exit Function
errdateNZ:
    ValueConvertedToDate = NULLDATE
    Resume doneDateNZ
End Function

 

[Imb]

Trying to handle reginal issues with string processing looks like a never ending chase.

Well, interestingly.
Can you elaborate on that?
Thank you.

 

[RonPaii]

Well, interestingly.
Can you elaborate on that?
Thank you.

Each region handles numbers and dates differently with dates being particular difficult. Converting to string for processing leaves much room for error. Converting to native types allows the operating system to handle the reginal differences. Comparing 2 date/time values is simple If DateTime1 > DateTime2 then do something, no conversion is needed and no concerns of what the region setting was on the computer entering the data. DateTime1 may have been entered #10/27/2025# and DateTime2 may have been entered #2025-10-27#.

 

[Imb]

Each region handles numbers and dates differently with dates being particular difficult. Converting to string for processing leaves much room for error. Converting to native types allows the operating system to handle the reginal differences. Comparing 2 date/time values is simple If DateTime1 > DateTime2 then do something, no conversion is needed and no concerns of what the region setting was on the computer entering the data. DateTime1 may have been entered #10/27/2025# and DateTime2 may have been entered #2025-10-27#.

OK, I understand.
The only thing the As_xxx functions do, is to convert control-values (that are formatted according regional settings), are converted to native types. That is all.