Results 1 to 9 of 9
  1. #1
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47

    Criteria Search to filter results from within an Access Form


    Hi there - I am trying to design and utilise a form that will help with my reporting functionality of my db.

    Ideally, I am also trying to make this form user friendly so that other end-users would be able to navigate the form in a sensible manner.

    I would like to have a multi-criteria search function through the utilisation of text and comboboxes so that the results can be narrowed down. I think I would like it to look something like this... https://www.youtube.com/watch?v=3SYXsdj_1Og&t=264s

    How would this best be achieved - and/or is there a better alternative?

    Cheers

    Boost

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there, assuming you have a "reporting" form in your db you can use something similar to what I developed for some of my applications:
    Click image for larger version. 

Name:	customfilter1.png 
Views:	41 
Size:	46.4 KB 
ID:	34764
    Hopefully you will find it easy to understand how to customize it for your own needs, if stuck please feel free to ask.

    Cheers,
    Vlad
    Attached Files Attached Files

  3. #3
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Gicu - thanks so much! That is pretty much what I am seeking - have had a go at modifying it to the exact specifications that I require and I think it's working so thanks very much!

    One thing I have been having an issue with is adding more search fields/criteria options. I need perhaps 10 in total. Is this even possible? Or has there been an integer lock to 6 variables?

    Also, how did you instruct the preview-query to show 'X' columns from the subset - can this be expanded?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can add more fields/controls - just follow the logic with the naming of the controls and modify the code to add the new controls. As for the preview-query listbox you can add more columns, simply modify its Column Count property (I believe it is set to 20 now) and the Column Widths property (to add the new columns).

    Cheers,
    Vlad

  5. #5
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Hi Gicu, I have had a look into adding more fields - and think I'll eventually get there by following your very clear naming conventions etc.

    I was wondering if you could confirm the validity of the "LIKE" function in the criteria boxes (=, <,> etc.).

    Does it allow for just a partial match in results? IE. Ideally I'd like to narrow down the query off of the birth year (IE> 1996) as opposed to an exact date such as 06/07/1993.

    Cheers!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Boost!

    Yes. LIKE will give you partial results (https://support.office.com/en-gb/art...9-eef18358e931) but it is meant for string operations (will not work on a date field). So in your case if you wish to filter on the birth year using like you could add a calculated field in your query such as DOB_TEXT: Format([DOB],"DD-MMM-YYYY") where DOB is your properly defined date field for date of birth and in the filter form you could use [IS]LIKE [VALUE] *1996. Or you could use the filter form to restrict the actual birth date (date field) as <= 1/1/1996 AND >=12/31/1996 (you would need to use two lines with the AND option and same "expression" number because they reference the same field).

    Cheers,
    Vlad



    Cheers,
    Vlad

  7. #7
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Morning, I have been attempting to utilise the "Like" function - but no matter what I do it doesn't return *Like criteria fields.

    Do I have to add a wildcard into the VBA elsewhere in the code - or alternatively is there for some reason, a default applied to the Like cboprep?

    Cheers

    Boost

    POST EDIT:

    Have figured the relationship between the wildcard and search field so problem sorted.

    However, what I am attempting to achieve is the ability to adjust the fields that appear in the sub-query preview of frmRunQueries?

    Cheers

    Boost
    Last edited by Boost; 07-22-2018 at 05:19 PM.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    By default in the sample I uploaded the first 20 fields of the query would show up. Usually for "preview" this should be enough; to add more increase the column count property to your desired number and add the corresponding values to the Column Widths property. If your problem is not this please explain in a bit more details.

    Cheers,
    Vlad

  9. #9
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Hi Gicu, it's been some time, but I was wondering whether or not you've had any experience in constructing an open report command/button on your criteria search?

    I'm trying to give it a go, but I can't manage to pass the filter details through to the report.

    Any ideas?

    Boost

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

Similar Threads

  1. Replies: 14
    Last Post: 06-27-2017, 04:52 PM
  2. Replies: 3
    Last Post: 09-12-2016, 11:49 AM
  3. Search Filter Criteria Help
    By aamer in forum Access
    Replies: 3
    Last Post: 10-31-2014, 06:45 PM
  4. Replies: 7
    Last Post: 09-27-2014, 09:11 PM
  5. Replies: 1
    Last Post: 01-30-2012, 12:12 PM

Tags for this Thread

Posting Permissions

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