Query building from form data. I could use some advice on what would be the best way here...

Question

Private Sub startsearch_Click()

If Me.BusinessName.Value = "" And _
Me.Suburb.Value = "" And _
Me.Postcode.Value = "" And _
Me.PostcodeMin.Value = "" And _
Me.PostcodeMax.Value = "" And _
Me.Areacode.Value = "" And _
Me.Phonenumber.Value = "" And _
Me.Areacode.Value = "" And _
Me.Faxnumber.Value = "" And _
Me.siccode.Value = "" And _
Me.anzisicode.Value = "" Then
MsgBox ("Enter some valid input.")
End If

End Sub

I'm just starting with something very basic that I posted above. Based off of these input values I could come up with so many different queries and I see that potentially the logic to handle all of these could easily become overly complex and difficult to manage. I am wondering if it might be possible for me to build one long query and just have that query just ignore the value if it is "" (No Value). I can see how I might structure something like "where [Postcode] = Me.Areacode.Value" but I have never been faced with having to skip over it if there is no value.

Kind Regards,

Sepoto

Answer 1

The use of a switch statement does come to mind but that would be a lot of cases still to handle. What would be better is if I could use the query in a way so that those parts of the query are ignored if Me..Value turns out to be null.

Answer 2

Actually I could build it like:

If Me.anzisicode.Value <> "" Then
SQL = SQL & "WHERE [ANZI Sicode] = """ & Me.anzisicode.Value & """"
End If

Can something like that be done directly with the SQL? If the (If Me..Value) could somehow be handled inside the query it would amount to slightly less code I think. Somehow I believe that it would be possible...

Answer 3

I am seeing some syntax something like:

CASE WHEN @Value1 IS NULL THEN -999 ELSE Value1 END = CASE WHEN @Value1 IS NULL THEN 0 ELSE @Value1 END AND
CASE WHEN @Value2 IS NULL THEN -999 ELSE Value2 END = CASE WHEN @Value2 IS NULL THEN 0 ELSE @Value2 END

I'm wondering if something like this could be done with ADO.

This also looks like maybe it might work:

SELECT
  SUM(IIF(A.FACILITY = "This"
        and B.CT_INS_TYPE = "Commercial", 1, 0)) as Count_of_ID
  FROM Table1 A INNER JOIN Table2 B
      ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID

This actually looks the most promising:

SELECT @par_strSquen AS strSquen,
s.strAcctID, 
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS cenStep) AS strScenStep 
FROM Schedule AS s ... 

I'm not sure if or how it would apply to my code yet though.

Answer 4

If you have control of the database that you are working with and can name the tables the same as the textbox name it might be of some help. Problem would be spaces, cause you can not have a space in the textbox name.

Just a thought.

If Me.ANZISicode.Value <> "" Then
SQL = SQL & "WHERE " & ANZISicode.Name & " = """ & Me.ANZISicode.Value & """"
End If

Answer 5

Good suggestion. What I am really trying to do though is have SQL handle the code instead of VBA and just do a:

""" & Me..Value & """

seems like it would be more streamlined.

Answer 6

Are these textboxes on a form or worksheet?

Answer 7

Answer 8

I was thinkin some thing like this

 Dim ctr As Control
    For Each ctr In Me.Controls
        If TypeName(ctr) = "TextBox" Then
            If ctr.Value <> vbNullString Then
                Sql = Sql & "WHERE " & ctr.Name & " = """ & ctr.Value & """"
                MsgBox Sql
            End If
        End If
        Sql = ""
    Next

Answer 9

I know that will work very well and I think it really is a good answer. Maybe what I am asking is a more Access'y type of question. So essentially I am wondering if the same thing can be accomplished by using IIF() inside the SQL statement.

Answer 10

What do what to happen if it is blank?

Answer 11

So IIF(""" & Me..Value Equal & """ To null) Then don't process that WHERE clause and skip to the next WHERE clause. More SQL'y. Cleaner.

Answer 12

Are just wanting to build the Where clause?

Answer 13

IIF(""" & Me..Value & "" Equal To null) Then don't build out that WHERE clause.

Answer 14

SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

I believe this is the key to the whole thing. I really think that I can write a working query off of this. The answer you have though is so good I have to mark it as the answer. I took this code from the MSDN Library documentation, search: "IIf". Thanks for the code.

Answer 15

Ok but If I'm thinking right this could work for you. It will build your sql statement for you IF the value is greater then nothing. To test put into a userform and add a few textboxes. If the textbox value is greater then nothing then it will add it to the query.

Private Type SqlBuilder
  aWhereTableName As Variant
  aWhereTableValue As Variant
  aSelect As String
End Type

Private Sub CommandButton1_Click()
 Dim ctr As Control
 Dim SqlSelect, SqlWhere
 Dim QueryBuilder() As SqlBuilder
 Dim x
 
    ReDim QueryBuilder(1 To 1)
    For Each ctr In Me.Controls
        If TypeName(ctr) = "TextBox" Then
            If Trim(ctr.Value) <> vbNullString Then
                QueryBuilder(UBound(QueryBuilder)).aWhereTableName = ctr.Name
                QueryBuilder(UBound(QueryBuilder)).aWhereTableValue = ctr.Value
                QueryBuilder(UBound(QueryBuilder)).aSelect = ctr.Name
                ReDim Preserve QueryBuilder(1 To UBound(QueryBuilder) + 1)
            End If
        End If
    Next
    
    On Error GoTo ErrHandler
    ReDim Preserve QueryBuilder(1 To UBound(QueryBuilder) - 1)
    For x = LBound(QueryBuilder) To UBound(QueryBuilder)
        SqlWhere = SqlWhere & "((" & QueryBuilder(x).aWhereTableName & ")=" & QueryBuilder(x).aWhereTableValue & ") And "
        SqlSelect = SqlSelect & QueryBuilder(x).aWhereTableName & " ,"
    Next
    SqlSelect = "Select " & Mid(SqlSelect, 1, InStrRev(SqlSelect, " ,") - 1)
    SqlWhere = " Where(" & Mid(SqlWhere, 1, InStrRev(SqlWhere, " And") - 1) & ");"
    MsgBox SqlSelect & " From TableName " & SqlWhere
ErrHandler:
    
End Sub

Answer 16

There is a SqlBuilder there.

Answer 17

You lost me. Where is there a SqlBuilder? I thought you are wanting to build a query based of values in textboxes or comboboxes if the values are great then nothing?

Answer 18

I am remarking that there is a SqlBuilder there that I did not know existed before you said that.

Answer 19

O my bad.. No there is not a SqlBuilder, it is a User Defined Type (UDTs) and are a convenient way to store related data in one variable.

I think the code that I gave with some modification, you can get it to work if you dont have any luck with the approch your going with now.

But My example counts on the UserForm control name being the SAME as the Access Table field name.

Answer 20

I see that now it looks like really good code.

Answer 21

Private Type SqlBuilder
  aWhereTableName As Variant
  aWhereTableValue As Variant
  aSelect As String
End Type

Private Sub CommandButton1_Click()
 Dim ctr As Control
 Dim SqlSelect, SqlWhere
 Dim QueryBuilder() As SqlBuilder
 Dim x
 
    ' actually create the array
    ReDim QueryBuilder(1 To 1)
    For Each ctr In Me.Controls
        If TypeName(ctr) = "TextBox" Then
            If Trim(ctr.Value) <> vbNullString Then
                QueryBuilder(UBound(QueryBuilder)).aWhereTableName = ctr.Name
                QueryBuilder(UBound(QueryBuilder)).aWhereTableValue = ctr.Value
                QueryBuilder(UBound(QueryBuilder)).aSelect = ctr.Name
                'make room for next possible entry
                ReDim Preserve QueryBuilder(1 To UBound(QueryBuilder) + 1)
            End If
        End If
    Next
    
    On Error GoTo ErrHandler
    'array QueryBuilder() will have an empty element, remove it
    ReDim Preserve QueryBuilder(1 To UBound(QueryBuilder) - 1)
    'now loop through the QueryBuilder and build the Select and Where Clause
    For x = LBound(QueryBuilder) To UBound(QueryBuilder)
        SqlSelect = SqlSelect & QueryBuilder(x).aWhereTableName & ", "
        SqlWhere = SqlWhere & "((" & QueryBuilder(x).aWhereTableName & ")=" & QueryBuilder(x).aWhereTableValue & ") And "
    Next
    'Remove the ", " from SqlSelect at end of the String
    SqlSelect = "Select " & Mid(SqlSelect, 1, InStrRev(SqlSelect, ", ") - 1)
    'Remove the " And" from SqlWhere at end of the String
    SqlWhere = " Where(" & Mid(SqlWhere, 1, InStrRev(SqlWhere, " And") - 1) & ");"
    MsgBox SqlSelect & " From TableName " & SqlWhere
   Exit Sub
ErrHandler:
    'If all textboxes are empty
End Sub

Added some small comments and a little error so here's a newer code. One thing that you will have to deal with is the data type of the access table field.