Results 1 to 6 of 6

Macro with 4 criteria, All or one can apply.

  1. #1
    Join Date
    May 2019
    Posts
    44

    Macro with 4 criteria, All or one can apply.

    Hi,
    Currently I have a query where 5 control boxes all the user to select different criteria for a selection of records. This scenario is used with a split form. On the selection part of the form I can enter text in any one or all of the control boxes and the records matching that criteria display in the bottom part of the form. On bottom form (a list), the tempvars as criteria use this format:

    Field - SName

    Criteria - Like "*" & [ShelterCounty] & "*".

    Again, five fields have the same format.

    Everything works fine.

    Now, I've tried the same with a report. I use 4 combo boxes and tempvars and use the same Like statement, Like "*" & [TempVars]![TempCounty] & "*" as above for the report. However, if I leave one combobox without a value, I get no records. If everything has a value, it properly selects the correct records.



    As I'm writing this I'm wondering if the control box vs. a oombo box is an issue, or is something built into the split form that I am missing.

    Any help would be appreciated.

    Maybe this would be better said as follows:
    I'm generating a report. The selection criteria for the report are four items. City, State, County and Facility Owner. Only one is necessary for the report to run, however I could also select values for all for items. I want to do this from a macro, however I'm open to VBA code.

    Jeff

    Jeff

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    443
    Maybe try
    Like "*" & Nz([TempVars]![TempCounty], "") & "*"

    Otherwise you could build your filter in vba and not add filters for criteria that are null: if not isnull(combobox) then my_fltr = my_fltr & combobox so on and so forth.

  3. #3
    Join Date
    May 2019
    Posts
    44

    Queries with null values

    Things have changed. I have included a snapshot of both the split form in design mode and a partial of my query assigned to the bottom split screen. The top section are search fields, for instance, varCLFacilityOwnerName. The bottom form has the table fields, CLFacilityOwnerName. Using your suggestion, when I search, if the table field is empty, then it doesn't show the record. Right now I only have two records. One with values in all fields and one with a null - CLFacilityOwnerName.

    Could you give me a short example of setting up a filter in VBA? It looks like the attachments are on a separate reply. I'm submitting them both.
    Thank you.



    Quote Originally Posted by kd2017 View Post
    Maybe try
    Like "*" & Nz([TempVars]![TempCounty], "") & "*"

    Otherwise you could build your filter in vba and not add filters for criteria that are null: if not isnull(combobox) then my_fltr = my_fltr & combobox so on and so forth.
    Attached Thumbnails Attached Thumbnails frmCallLogMulti - partial query.png   CallLog- Split Screen.png  

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    443
    I can't take a close look at your particular situation at this moment but MS Access hall of famer Allen Browne has an example posted here: http://allenbrowne.com/ser-62.html

  5. #5
    Join Date
    May 2019
    Posts
    44
    Quote Originally Posted by kd2017 View Post
    I can't take a close look at your particular situation at this moment but MS Access hall of famer Allen Browne has an example posted here: http://allenbrowne.com/ser-62.html
    Thank you I will look at his post.

  6. #6
    Join Date
    May 2019
    Posts
    44
    Thank you very much. The information Allen Browne provided was very helpful and resolved many issues for me.
    Thanks Again,
    Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create a macro to apply a filter sort
    By dave1962 in forum Access
    Replies: 3
    Last Post: 10-18-2018, 06:20 AM
  2. Replies: 5
    Last Post: 12-01-2016, 02:22 PM
  3. Replies: 4
    Last Post: 07-11-2014, 05:51 AM
  4. Selectively Apply Criteria
    By ztpkng in forum Queries
    Replies: 6
    Last Post: 06-24-2014, 11:10 AM
  5. Macro to apply filter to form
    By mattzkn in forum Macros
    Replies: 3
    Last Post: 12-11-2013, 12:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums