Concatenating control values for SQL statement?

[Gasman]

Hi everyone,
Recently we have had a raft of users struggling to use the correct surrounding characters when trying to build a SQL statement or even a DCount criteria.
It also appears quite regularly anyway.?

I seem to recall someone wrote a function that did it all for you?, so you just use FunctionName(ControlName) & FunctionName(ControlName) etc.?

Does anyone have a link please.?
I believe it was written by one of the experts here.?

I agree it would be best that they do learn, but at least it would give them a workaround in the meantime?

 

[arnelgp]

mr.gasman, you can create similar function.
i used this on every domain lookup.

eg: DLookup("fieldToReturn", "TableName", fFixSQLCriteria("TableName", "FieldName", theFieldValue))

Public Function fFixSQLCriteria(ByVal pstrTable As String, pstrFieldName As String, pvarValue As Variant) As String
    On Error GoTo fFixSQLCriteria_Error
    Dim strCriteria As String
    If IsNull(pvarValue) Then
        fFixSQLCriteria = "(" & pstrFieldName & " Is Null)"
        Exit Function
    End If
    With CurrentDb.OpenRecordset( _
        "SELECT " & pstrFieldName & " FROM " & pstrTable & " WHERE (1=0);", _
        dbOpenSnapshot, dbReadOnly)
        Select Case .Fields(0).Type
            Case dbText, dbMemo
                strCriteria = "(" & pstrFieldName & ") = '" & Replace(pvarValue, "'", "''") & "'"
            Case dbDate, dbTime
                If DateValue(pvarValue) <> pvarValue Then
                    strCriteria = "(" & pstrFieldName & ") = " & Format(pvarValue, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                Else
                    strCriteria = "(" & pstrFieldName & ") = " & Format(pvarValue, "\#mm\/dd\/yyyy\#")
                End If
            Case Else
                strCriteria = "(" & Replace(BuildCriteria(pstrFieldName, .Fields(0).Type, pvarValue), "=", ") = ")
        End Select
    End With
    fFixSQLCriteria = "(" & strCriteria & ")"
    
fFixSQLCriteria_Exit:
    On Error GoTo 0
    Exit Function

fFixSQLCriteria_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fFixSQLCriteria, line " & Erl & "."
    Resume fFixSQLCriteria_Exit
End Function

 

[Gasman]

Thank you arnelgp,
I was thinking some something slightly different, just for the criteria?

I tried to roll my own, just as an exercise.

Function fFormat4SQL(ByVal varInput As Variant) as String
Dim strJetDate As String, strRet As String
Dim intType As Integer
strJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
intType = VarType(varInput)
'Debug.Print VarType(varInput)

Select Case intType
    Case 7 'Date
        strRet = Format(varInput, strJetDate)
    Case 2, 3, 4, 5, 6, 14, 17, 20 ' Numeric
        strRet = varInput
    Case 8 ' String
        If InStr(1, varInput, "'") > 0 Then
            varInput = Replace(varInput, "'", "''")
        End If
        strRet = "'" & varInput & "'"
    Case Else
        MsgBox "Input type not catered for?"
        Exit Function
End Select
fFormat4SQL = strRet
End Function

The reason I went with VarType was when testing with 23A, Access thought it was a Date?

 

[theDBguy]

Hi. Not in front of a computer right now, but maybe take a look at BuildCriteria() also.

Sent from phone...

Application.BuildCriteria method (Access)

Office VBA reference topic

docs.microsoft.com

 

[Gasman]

Hi. Not in front of a computer right now, but maybe take a look at BuildCriteria() also.

Sent from phone...

Application.BuildCriteria method (Access)

Office VBA reference topic

docs.microsoft.com

Hi theDBguy,
Yes I can see arnel also used BuildCriteria, however I have never seen this offered to the new users with the type mismatch problems? I wonder why.?

 

[theDBguy]

Hi theDBguy,
Yes I can see arnel also used BuildCriteria, however I have never seen this offered to the new users with the type mismatch problems? I wonder why.?

Oh, I didn't notice @arnelgp already mentioned it. Sorry for the duplicate info.

 

[Gasman]

Oh, I didn't notice @arnelgp already mentioned it. Sorry for the duplicate info.

Not an issue, if anything, confirmation to try BuildCriteria?, however I think that will be harder to use for new users?
My thoughts were just to build simple strings for concatenation?

We can test when we get the next query.