My form has 6 text boxes for user input
The user may enter criteria in all or 1 of the text boxes.
based on the text boxes that have the criteria how can i string a SQL statement together.

Try something like this...


Hope that helps


Mike
EchoVue.com

Well I gave that a try and I think I'm getting closer
The problem is I need to be able to combine the criteria from txt1,txt2,txt3, or if txt2 is not filled in than txt1,txt3.

Right now if all three txtboxes are filled in its just returning a
"AND Field 1...
Or if txt1 is blank than its returning a
"And Field 2...

so i think its in the right direction but it still needs some work
any ideas?

Try setting strWhere to vbNullstring. For each textbox, it checks to see if it is blank, and if not then it check to see if the strWhere clause already has text. If so it needs and AND, otherwise, it just puts the field = to the value.

Dim strSQL as String
Dim strWhere as String

strSQl = "SELECT * FROM myTable"
strWhere = vbNullstring

if txtOne <> vbNullstring then
    If len(strWhere) > 0 then
        strWhere = " AND Field1 = '" & txtOne & "'"
    Else
        strWhere = " Field1 = '" & txtOne & "'"
    End
End If


Mike
EchoVue.com

I tried that but it is still only stringing the first txtbox and not the others. It is picking the "And Field 1.... but its not followed by anything.

also I was getting an "End If" error so i went and substituted the "End" for "End If". This seem to correct the error but could this be why I'm not getting the desired results?




   Dim strWhere As String
    Dim strSQL As String

    strSQL = "SELECT * FROM Tcounty"
    strWhere = vbNullString


    If txtRange <> vbNullString Then
        If Len(strWhere) > 0 Then
            strWhere = " AND [Range] = '" & txtRange & "'"
        Else
            strWhere = " [Range] = '" & txtRange & "'"
        End If
    End If

    If txtTownship <> vbNullString Then
        If Len(strWhere) > 0 Then
            strWhere = " AND [Township] = '" & txtTownship & "'"
Else strWhere = " [Township] = '" & txtTownship & "'"
        End If
    End If

    If txtSection <> vbNullString Then
        If Len(strWhere) > 0 Then
            strWhere = " AND [Section] = '" & txtSection & "'"
       Else
            strWhere = " [Section] = '" & txtSection & "'"
        End If
    End If



    If Len(strWhere) > 0 Then
    strSQL = strSQL & " WHERE " & strWhere
    End If



I appreciate any thoughts on the matter...

-Dale

I'm sorry I ment to bold all the "End" to indicate that ones i changed to "End If" not the "Else".

DOH!!! In any case where it is including "AND" it needs to be:

strWhere = strWHERE & " AND Field1='" & ...

The End If's would have caused to problems as well.

You may also need to tweak the Outer If statements to say:

If len(trim(txtOne)) > 0 Then

That way if the box contains a space it will still skip it.

Mike

Mike
EchoVue.com

Hey thanks a lot Mike.

I was wondering I corrected the "End If" properly i need some more clarity about that issue.

-Dale

No Worries Dale,

Glad I could help - Yell if you need any further assistance.

Mike



Mike
EchoVue.com

Hey its working so far

SWEET!!!!

Thanks for all your insight....

Much appreciated!!

-Dale