SQL Variable adds on

[Joy83]

Good Day,
I need your help in the following
I want to build SQL string that adds on only if the text is not empty..

Dim Variable1,Variable2, Variable3 as string


Variable2=me.TXT2
Variable2=me.TXT3

Dim COND as string

If Len( txt1.value &"" )=0 then
Variable1= me.TXT1
COND=COND+ "FName='" & variable1 &"' "

Else
End if



If Len( txt2.value &"" )=0 then
Variable2= me.TXT2
COND=COND+ "LName='" & variable2 &"' "

Else
End if



If Len( txt3.value &"" )=0 then
Variable3= me.TXT3
COND=COND+ "MName='" & variable3 &"' "

Else
End if



Dim SQL as string
SQL="Select * from Table Where " COND


I am doing something wrong in COND. basically want to end up having SQL statement with AND condition (only if the test is filled)



Thanks for your help

 

[Dreamweaver]

I don't think what your doing will work as you have missed the AND Etc "MName='" & variable3 &"' "
You can replace the field names with yours and use Not IsNull() Instead of IsNothing()
I Normally Use Something like this

On Error GoTo HandleErr

W = ""
    If Not IsNothing(Me![txt_Description]) Then W = " AND [CustomerName] like """ & "*" & Me![txt_Description] & "*" & """"
    If Not IsNothing(Me![Cbo_SystemTypeID]) Then W = W & " AND [SystemTypeID]=" & Me![Cbo_SystemTypeID]
    If Not IsNothing(Me![Cbo_City]) Then W = W & " AND [CityID]=" & Me![Cbo_City]
    If Not IsNothing(Me![Cbo_Region]) Then W = W & " AND [RegionID]=" & Me![Cbo_Region]
    If Not IsNothing(Me![Cbo_CompanyTypeID]) Then W = W & " AND [CompanyTypeID]=" & Me![Cbo_CompanyTypeID]
    If Not IsNothing(Me![Txt_Postcode]) Then W = W & " AND [Postcode] like """ & "*" & Me![Txt_Postcode] & "*" & """"
    
        'Now Remove The First " And "
    If W <> "" Then
        W = Right(W, Len(W) - 5)
    End If
    Me![lst_Results].RowSource = "SELECT * FROM QryCustomerSearchResults WHERE " & W
    Me![lst_Results].Requery
    
HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

 

[jdraw]

In addition to Mick's comments, you should be aware that

Dim Variable1,Variable2, Variable3 as string

does not do what you think.
Variable3 as string will be a string, but Variable1,Variable2 will be Variant.

With VBA you must expressly define/Dim each variable. So, these will ensure each variable is a String datatype.

Dim Variable1 as string ,Variable2 as string , Variable3 as string
or
Dim Variable1 as string
Dim Variable2 as string
Dim Variable3 as string

Also, if you use a Debug.Print your SQL variable, you will get a string in the immediate window showing how Access has interpreted and rendered your SQL.