Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Treble Filter (Access 2000)

    On my form i need to perfrom a trebled filter.
    First i must filter the field HouseID according to the choice made in the
    option box Houses of the form:
    Me.FilterOn = True
    Me!HouseID = Me!Houses
    But it doesnt filter all the records.I somehow i must tie it up with the reccord source.

    The record source thus obtaineed i must filter further to the the YesNo field TurnDown
    depending wheter i want TurnDown Yes or TurnDown No.
    Also i need to filter the record source according to whether offerid Is Null or not


    Unfortunatelky i could reach any sensible solution

    My record source is :


    SELECT TblClients.ClientID, TblOffers.offerid, TblClients.TurnDown, TblClients.HouseID
    FROM TblClients LEFT JOIN TblOffers ON TblClients.ClientID = TblOffers.Clientid;

    Could you help me ?

    I think i have succeeded in filtering the HouseID by clicking on the option box with ther following code:
    Dim SQL As String
    SQL = " SELECT TblClients.ClientID, TblOffers.offerid, TblOffers.offerdate, TblClients.TurnDown, TblClients.HouseID " & _
    "FROM TblClients LEFT JOIN TblOffers ON TblClients.ClientID = TblOffers.Clientid"
    Dim StrHouse As String
    StrOffice = " WHERE (((TblClients.HouseID)=" & Me.House & "));"
    Me.RecordSource = SQL & StrHouse
    i still cannot find a way to filter further down with the following possibilities:

    a. clients with the field TurnDown = False
    b. clients with field TurnDown = True
    c. clients with field TurnDown = False and field OfferId = IsNull
    d. clients with field TurnDown = False and field OfferId is Not Null
    e. clients with Field TurnDown = True and field OfferId IsNull
    f clients with field TurnDown = True and field orderid Is Not Null

    It is formidable task for me but i think Access should possess some filtering capacity to solve my problems
    easier than i imagine

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

    Re: Treble Filter (Access 2000)

    You could try using a bass filter instead <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    But seriously, you say you have an option box Houses on your form in which the user can select an HouseID. How can the user indicate whether he/she wants records with TurnDown = Yes or TurnDown = No? And how can the user indicate whether he/she wants records where OfferID is Null or not?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Treble Filter (Access 2000)

    I do not understand what bass filter is.I have described before just what i want to do,because somebody might offer me an approved solution.
    Oitherwise i imagine that by clicking a button on the form and somehow filtering the form,and somehow combining the two basic conditions,
    that is to say Turndown and offerid = 0 i might reach my solution.I know there are a lot of conditions and that is the reason that i just described
    them.
    Thank you for the attention.
    regards
    Johm

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

    Re: Treble Filter (Access 2000)

    First, you wrote that you wanted to filter on OfferID is Null or not Null. Now you mention OfferID = 0. That is not the same - Null means blank, empty, missing. What exactly do you want?

    The bass filter was just a stupid joke - a treble filter is a high tones filter in audio equipment, and a bass filter is a low tones filter. You meant triple filter.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Treble Filter (Access 2000)

    I mean Offerid IsNull. That is when no offer is beine made.Please do apologize me for my uncorrect expressions


    regards

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

    Re: Treble Filter (Access 2000)

    Put an unbound check box chkTurnDown on your form, labeled Turn Down.
    Put an unbound check box chkOfferID on your form, labeled Offer ID.
    Put a command button cmdFilter on your form, labeled Filter Data. Create an On Click event procedure for the command button:

    Pritvate Sub cmdFilter_Click()
    Dim strFilter As String
    strFilter = "tblClients.HouseID = " & Me.Houses & " AND " & _
    "tblClients.TurnDown = " & Me.chkTurnDown & " AND " & _
    "tblOffers.OfferID Is "
    If Me.chkOfferID Then
    strFilter = strFilter & "Not "
    End If
    strFilter = strFilter & "Null"
    Me.Filter = strFilter
    Me.FilterOn = True
    End Sub

    You can also want to put a command button cmdNoFilter on your form, labeled Filter Off, with the following On Click event procedure:

    Private Sub cmdNoFilter_Click()
    Me.Filter = ""
    Me.FilterOn = False
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Treble Filter (Access 2000)

    I am a bit awed by the power of Access and also how did you understand me even though i am not much articulate.I think
    the form is great and the code is formidable.
    I have some additional questions that might improve the flexibility of my form.

    The button ChkTurnDown i have named "refused"
    The button ChkOfferid i have named "Available Offer"

    If i cllick both buttons then the filter is Ok.
    There remain the folloiwng possibilities


    1. If i click only Refused and then Filter,i get "invalid use of Null".
    I need the category "Refused" for those clients, who have refused,regardless whether they have received an offer or not.
    2. If i click only AvailableOffer,and then Filter, i get "invalid use of Null".
    I need this category for those clients,who have received an offer,regardless whether they have refused or not.
    How can i improve on that ?I amsending
    the attachment.

    Thank you once again.

    regards

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

    Re: Treble Filter (Access 2000)

    Both check boxes have no value initially, so they are Null. To remedy this, set the Default Value property of the check boxes to No (in design view). This means that both check boxes will be "off" when the form is opened.

Posting Permissions

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