Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter Problems (2002)

    Hi,

    I'm attaching a VERY stripped down version of a billing database that I took over and have struggled with for months. It's been working for the past two months and I was about to breath a sigh of relief and then wham! When you open the database it will open to a form that is used to enter in the month and year to be billed (I've only inlcuded the months that are present in the table). The query is set up to only show the payments that were made prior to the most recent billing cycle (so that past bills may be replicated without payments being added in that were applied months after that billing). On the form you will see [txtFilter] which is use to filter the payments made after the invoice being printed. The filter is derived from the formula =[cmbmonth] & "" & [txtyear]. This process has worked fine until payments that were made in October were applied. For whatever reason, the October payments aren't being filtered out and I have no idea why. Their next billing cycle begins Nov 1st and I've got to get this fixed before then. Any help/suggestions would be greatly appreciated.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter Problems (2002)

    The problem is that by concatenating month and year, you create a text value. Text values are compared in alphanumeric (dictionary) order, even if they represent numbers. So "102005" is less than "92005", because 1 comes before 9, even though the numeric value 102005 is greater than 92005. Try this, to use a numeric comparison:

    1) Change the definition of filter in the query to

    filter: 100*Year([payment date])+Month([payment date])

    2) Change the control source of txtFilter on the form to

    =100*[txtyear]+[cmbmonth]

    September 2005 will become 200509 and October 2005 will become 200510. This will also be correct next year: January 2006 becomes 200601, and that is larger than 200512.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter Problems (2002)

    Oh God Hans I was praying you'd be on line! That worked perfectly and the explanation makes so much sense. I can't tell you what a nightmare this database has been. It was just a freak thing that I stopped to install an update with a report feature they needed and totally by mistake noted the problem that was about to present itself with the October billing!!

    Thank you so much!
    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •