Results 1 to 4 of 4
  1. #1
    LightKnight is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7

    Filter returns no value

    The issue I have is a bit complex (for me anyway) and I am looking for a right direction nudge.
    I have a table with a list of contracts for my business. I have a working filter which filters out on the status, the legal adviser and the division. Filter works fine until it returns no records. The form then moves to expecting a new record and due to Required fields in the table, I can't refilter. I tried shutting the form off to additions but this has not solved the issue. I then thought that I should get a recordset before turning on the filter and based on the number of records returned evaluate whether to turn the filter on or not. I have never really had to work with recordsets and I am not sure if I have the right idea or not.
    The process would be:
    1. Open a recordset the same as as the filter will produce when it is turned on. I am assuming that if it returns 0 this will not give the issue with the form moving to a new record that I currently have.


    2. Evaluate the number of records in the recordset (or look at EOF)
    3. If the number of records is 0 don't turn the filter on and tell the user there are no records to return.
    4. If the number of records is more than 0 turn the filter on.

    Does this sound logical? I can work out the actual programming I think if the logical path seems right - probably with a lot of trial and error.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you could use the DCount() function to test for the number of records
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    LightKnight is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Ah, OK thanks. That might be a lot easier.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Domain aggregate functions are not known for their speed so if that becomes a problem you may need to look at recordsets again.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. IIF returns same for all conditions
    By Larryg in forum Queries
    Replies: 12
    Last Post: 07-18-2016, 06:51 AM
  2. Replies: 5
    Last Post: 06-15-2014, 07:36 PM
  3. Dcount() only returns 0 (zero)
    By pploum in forum Access
    Replies: 6
    Last Post: 02-20-2014, 02:48 PM
  4. Replies: 6
    Last Post: 07-21-2013, 10:43 PM
  5. Replies: 1
    Last Post: 04-12-2012, 11:34 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
  •  
Other Forums: Microsoft Office Forums