field variables in sql string

[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