Query by form with checkboxes to set criteria

[Jaron]

Hi,
I am trying to write a query to return results based on a search form. Query will have the following fields:
1. City
2. Physical Address
3. PO Box
4. Telephone
5. Confirmed?
6. Follow-up?

The search form has the following:
1. a text box to enter the city
2. 3 check boxes (yes or no value) for:
a. Physical address only
b. PO Box only
c. Telephone only

I need my query to return only results in which "Confirmed" and "Follow-up" are Null. Those results need to be filtered by the search form as:
=[Forms]![Searchform]![City] or is null (so that the user can leave blank for all)
Return only physical addresses where the telephone and PO Box fields are null (yes/no)
Return only PO Boxes where the Physical address and Telephone fields are null (yes/no)
Return only Telephone where the Physical address and PO Box fields are null (yes/no)

If the user leaves the textbox blank and unchecks all three boxes, the query should return all records where the "confirmed" and "follow-up" fields are Null.

I've got the city filter from the textbox working right; it's just the checkboxes I'm having trouble with. Any ideas on how to write the criteria?

[Dal Jeanis]

You'll use code almost exactly like what's on this thread -> https://www.accessforums.net/queries...orm-36100.html

The SQL WHERE clause will be "(fieldname IS NULL)". You'll vary the SQL that you build based upon your actual checkboxes.

Now, first, since those three options are mutually exclusive, you might want to use a four-way radio button instead of three checkboxes.

Or, you may want to reverse the checkboxes to have the first box mean "address is NULL", the second to mean "PO BOX is NULL", etc.

Either one of those options makes slightly more intuitive sense, and might be easier to code.

Is that enough for you to go on, or do you need more?

[Jaron]

Ok, so let me see if I understand. I'll have a search form with a textbox. The user can filter by entering a city name, or leave blank for all. Then, regardless of that filter, the records returned will be further filtered with 4 radio buttons that say:
1. Return all results, OR
2. Return results only where the Physical address is not null, OR
3. Return results only where the Telephone field is not null AND the Physical and PO Box fields are null, OR
4. Return results only where the PO Box field is not null AND the Physical and Telephone fields are null.
Regardless of what options are chosen, the results returned will be only those where the "Confirmed" field is null OR equals "no" (forget the "follow-up"; it's redundant and implied by "confirmed" being null)

The user will then have 3 command buttons to choose from:
1. cancel: closes the search form
2. report: opens a report based on the query which takes it's criteria from the search form, then closes the search form
3. edit: opens a split form based on the query which takes it's criteria from the search form, then closes the search form

From what I read in the linked thread, are you saying that there is no way to write one query with all that criteria, but rather I need to write VBA code that writes the SQL anew each time based on the search box options that are chosen? Since the search box is opened fresh for each search, is there a need to requery?

[Dal Jeanis]

1) Yes, you can do it that way, more or less.

2) No, I didn't say there is no way to do it, I said that the way I would do it is to build it all in one place using VBA. You could also build a single base query and add filters. I wouldn't do that for this application, though.

3) The way that I code that kind of situation is this: When the form initially loads, and every time one of the controls that is supposed to change the filter is altered, I call a single procedure that will build the SQL based upon the states of ALL the different controls. That way, my code is all in one place and can be reviewed and verified without looking anywhere else (as long as the control names are appropriate).

4) Since the only options your user is clicking are a single set of radio buttons, there is only a single place to code. That will be in the frame that is around the radio buttons. The radio buttons will each have a value - perhaps 0 thru 3. When one button (say the one with value 2) is clicked, it sends a message to the frame that the frame's new value is 2. In the AfterChange event of the frame, you have a select case statement to build the SQL, in this case for value 2 (telephone is not Null and Physical and PO are Null). Then the frame control asks for the Requery.

5) If you're loading the SQL to a control, then the requery is automatic. If you are loading it to a form or subform, then you should requery.

6) The way to code the solution is always the way you thoroughly understand. You're the one who has to support it later.

[Jaron]

1)
6) The way to code the solution is always the way you thoroughly understand. You're the one who has to support it later.

The way you describe sounds like the best, and most professional way to do it. Unfortunately, your point #6 is a big caveat. I'm new to Access and relying heavily on the SQL builder in the query in design view. I did manage to accomplish my goal that way, using radio buttons as you suggested (a whole lot of "give me this and this and not this, or not this and this and this, or...). Just no VBA. I think it works. If not, I may have to come back and study up on your solution a little more. Thanks for your help.

[Dal Jeanis]

If it works, great. Don't build a cathedral when you need a bungalow.

If it doesn't work, then here's a code skeleton you can use to build the query. You'll have to fix all the names, add your specific return fields, and adjust the cases to the values being returned to the frame by your radio buttons.

When testing this, uncomment the message box that shows you the SQL. Even if you're not yet proficient at understanding the SQL at a glance, a human can see the missing spaces when the words run together, and that's the most common error I make in coding a section like this.

And don't be afraid to ask questions.

Private Sub BuildQuery()
Dim WhereDone As Boolean
Dim strListSQL As String

  ' the select fields are static
  WhereDone = False
  strListSQL = "SELECT [Field1], [Field2], Field3] " & _
                "FROM [MyTable] " 

  ' always test for confirmed = False or Null
  ' confirmed <> True is a single test to get the same thing
  strListSQL = strListSQL & "WHERE ( " & _
                "([Confirmed] <> True) "
  Wheredone = True 
  
  ' I'm leaving the test in this code for wheredone = True 
  ' even though it will always be True in this usage.  
  '
  ' That way this code can be cloned and adapted without 
  ' forgetting to put the test back in
  '
  Select Case F1frame.Value
    Case 0
       ' do nothing
    
    Case 1
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
       "( ([Address] IS NOT Null) " & _
       "AND ([Phone] IS Null) " & _
       "AND ([POBox] IS Null) )"
    
    Case 2
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
       "( ([Address] IS Null) " & _
       "AND ([Phone] IS NOT Null) " & _
       "AND ([POBox] IS Null) )"
    
    Case 3
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
       "( ([Address] IS Null) " & _
       "AND ([Phone] IS Null) " & _
       "AND ([POBox] IS NOT Null) )"
  
  End Select
      
  ' close the where if there is one
  If WhereDone Then
     strListSQL = strListSQL & ") "
  End If

  ' Add the order by clause if needed
  ' strListSQL = strListSQL & _
  '   "ORDER BY [Field1], [Field2], [Field3] "

  ' Add the semicolon to complete the SQL
   strListSQL = strListSQL &  ";"
     
  ' for testing, show sql
  ' MsgBox strListSQL
 
  ' load sql to form
   Me.[Record Source] = strListSQL
   Me.Requery

   ' OR load SQL to a list box
   ' lstLeft.RowSource = ""
   ' lstLeft.RowSourceType = "Table/Query"
   ' lstLeft.RowSource = strListSQL
   ' Me.lstLeft.Requery

End Sub

[Jaron]

Actually, I do have a couple of questions...
"Wheredone"...is that a recognized VBA command, or is it something made up that refers to a state of a control?
And on implementing...I am assuming I put this in it's own module, then call it from the control on the split form with the table data, as in an after update event on the radio button frame to run a macro? Then what happens? Does the VBA actually build a query based on the table and set it as the new record source for the form, or does it re-write the SQL for the existing query?

[Dal Jeanis]

HINT: The first line of the subroutine is

 Dim WhereDone As Boolean

So, I suppose for form's sake the variable should have a name starting with "bol". It's just a true/false variable that is tracking whether I have added a WHERE clause to the SQL yet.

If I have five different controls, and the user might decide to use any or all or none of them, then I have to be able to detect in my code the first control that is used. The first one selected by the user is responsible for putting "WHERE (" on at the beginning, before its own test, every succeeding one will put "AND " before its own test. At the end, if any of them have put a WHERE clause, I have to add one close paren ") ".

Later on, there's a spot for ORDER BY, and then eventually a semicolon to finish up the SQL.

USAGE:

1) This SQL is supposed to be called by each control that is modified by the user. Where all the controls are on a single form, this would be a module level subroutine, not in its own module. If it's put in a separate module, you might have to futz with the code to make sure it could address all the controls.

2) In your case, since the only changes are the radio button, you could actually just incorporate all that code into the AfterUpdate event of the frame. On general principles, I'd keep it separate, though in the same module. After all, you might decide later to add a checkbox that modifies the SQL more.

3) My practice is to call this kind of code when the form loads, then AfterUpdate of the frame. This builds the only SQL that there is - there's no saved query at all. If you wanted, you could change the format to where there was a query and this was building and loading a filter instead, but that's your call.

4) If you really prefer macros, then you could put it into a module of its own and call it from a macro. I prefer just putting the VBA behind the form, because that way I only have to look one place. Your Mileage May Vary.

[Jaron]

Ok, so you're saying, I want you to test for a state, and I'm calling the state "Wheredone". I want you to interpret the state as 'false' if the string looks like this...., and as 'true' if it looks like this.....
For each case, I want you to add the "Where" if the "Wheredone" state is false.

is that about right?

[Dal Jeanis]

Exactly right.

I start out with the WhereDone variable set as False, and the first control that has to add a test, adds "WHERE ( " into the SQL and sets the WhereDone variable to true. Each control after that will know by testing the Wheredone variable that there is already a "WHERE" back there somewhere in the SQL, and the control will then add "AND " instead of adding "WHERE (". At the end, if no control has ever added "WHERE (", then I don't need to close the parens, and if anyone has, then I do need to close the parens.

Since there's only one control (the frame) in your current design, the code is a little more robust than you really need. The great thing is that you can add each new control right after the SELECT for the frame, and your SQL can put as many different ANDs as you want, without changing the structure at all. It's not really as fancy as it looks, but I like it.

The killer is when the combination of controls might need to mix ANDs and ORs. That code gets ugly. Ooops. Forget I said anything. That's way above your pay grade, and not needed for your application...

[Jaron]

All this is above my pay grade. But actually, I do have that textbox I mentioned earlier-where the user can enter a city or leave blank for all. Also I have the requirement that the "Confirmed" field be Null. But I'm guessing I could just hard code that into the SQL at the Wheredone true state, and everything else is an AND after that.

[Dal Jeanis]

1) This code will allow them to enter part of a city name as well as the entire city name. "San J" would get them "San Juan" and "San Jose".

   if txtCity <> "" Then
      If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
       "([City] IS LIKE  '*" &  txtCity & "*')"
   End If

2) If [confirmed] is Null, then it is not True, so the test for "[Confirmed] <> True" catches the Nulls.

3) If you want the City textbox and the radio/frame to both have an effect on the SQL, I would recommend that you put the BuildQuery procedure in VBA behind the form, and have each control's AfterUpdate call the BuildQuery procedure.

Admittedly, that's only because I don't do enough macros to know what problems you will or won't encounter if you do it in a macro.

That there be the country of some t'other guide, so iff'n ye would go there, ye'd best be about findin' him.

[Jaron]

Oops I missed the confirmed test in the original code. Thanks for all your help... This looks fairly easy to implement.

[Dal Jeanis]

You're welcome. When you're sure you've got what you need, please mark the thread "solved". top of page, under "thread tools".