Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query Filter Help (2002)

    Hi,

    I

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    Leesha,
    Which fields will match the combo box "Payment Source"?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Query Filter Help (2002)

    Hi Jeremy,

    The fields are labeled exactly (I hope) as those in cmbPaymentSource and are in qryOutcomeResults and start in column 13.

    Thanks!

    Leesha

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

    Re: Query Filter Help (2002)

    Hi,

    I only converted it to 97 to make the database small enough to upload. Even stripping it down I couldn't get it below the minimum.

    Re the IF/Than statements, just to clarify, am I putting those in the field section of the query or in the criteria? I've never used them in the criteria section before and wasn't sure.

    Thanks!

    Leesha

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    Leesha,
    I don't have Access 97, so I'm hesitant to convert and re-post. However, I will tell you how I would do this...first make a copy of qryOutcomeResults, and name it something like qryOutcomeResultsPayments. Next, in those next fields (Starting at field 13 I guess) I would use an IIF statement, something like IIF(IsNull([forms]![frmOBQIReports]![cmbPaymentSource]),[FIELD NAME HERE],[forms]![frmOBQIReports]![cmbPaymentSource]). This will filter every record to only those with the matching payment type in all of the fields you put the IIF statement in (if you want to find all records where even ONE of the payments was of that type, you must use OR statements). You don't have a command button to open the query yet, but when you do, you can use an IF statement to tell Access which query to open.

    Private Sub cmdButton_Click()

    IF cmbPaymentSource = "All Payments" Then
    'Open the query that does not care what payment source
    DoCmd.OpenQuery "qryOutcomeResults"
    Else
    'Open the query that is filtered by payment source
    DoCmd.OpenQuery "qryOutcomeResultsPayments"
    End If

    End Sub
    Hope this helps. If I'm missing something in your question, please feel free to <img src=/S/poke.gif border=0 alt=poke width=60 height=17> me in the <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15> and let me know <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Edit: I'm suggesting two queries because although I would prefer to do this in code, the query's SQL is too long (it cannot be broken that many times).
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    You would use the IIF statement in the query criteria line.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Query Filter Help (2002)

    Well its pretty cool to be able to filter using if/than statements in the criteria line. I'm 1/2 way to where I want to be. My problem is in the "than" portion of the statement. This is what I have done so far...I changed around tblPaymentSource to have ID numbers so it would be easier to define in the SQL. Each of the 5 fields in the query that contain payment data have either a "y" or a "n" to indicate whether or not the payment source is valid. This is the formula that I put in the first field.

    =IIf([forms]![frmOBQIReports]![cmbPaymentSource]=1,"y","n" Or "y")

    It works fine until the second portion. I get an error that it is too complex. What I'm trying to achieve is a result where - if the first part is not true, than the field does not filter at all. Any ideas?

    I can't use the approach of two different queries since there are so many reports written off of qryOutcomeResults that I really am trying to avoid having to duplicate all of those. Also, since there are 5 different payment fields (I wish the feds had put the payments all in one field!) I need to be sure that if query does not filter in the second section of the statement.

    Thanks,
    Leesha

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    Leesha,
    have you tried this?:

    =IIf([forms]![frmOBQIReports]![cmbPaymentSource]=1,"y","")

    I'm no expert with IIF statements, so perhaps someone else can look in on this...

    >>(I wish the feds had put the payments all in one field!)

    Could you automate the import of this to merge it all into one?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Query Filter Help (2002)

    Hi,

    Yep, I tried the "" at the end and it blew up on me. I also considered changing the table structure but to be honest, there is so much already built based on the present structure, I'd rather see if I can get the query to filter the way I need it to vs redo all the reports and formatting.

    Leesha

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    <img src=/S/puke.gif border=0 alt=puke width=60 height=15>

    I've been in those situations before...sorry I can't help with the IIF statement...I guess this is a learning opportunity for me too....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Query Filter Help (2002)

    Not to worry, I appreciate the help! Hopefully Hans will be able to point me in the right direction and if will be a new learning experience for you as well. I have a feeling we're either very very close or ...........................WAYYYYYYYYYYYYY off base!

    Leesha

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

    Re: Query Filter Help (2002)

    You can use this as criteria for Any_Mediicare:
    <code>
    Like IIf([forms]![frmOBQIReports]![cmbPaymentSource]="Any-Mediicare","y","*")
    </code>
    and similar for the other four fields. Explanation: if the combo box is set to a specific type, you want the corresponding field to be "y", otherwise you don't care.

    Modified version attached (in Access 2000 format)

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    Hans,
    Is there a trick around the limit on the SQL length in the VBA editorso that we could programattically change it?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Query Filter Help (2002)

    I'm sorry, I don't understand. What limit?

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter Help (2002)

    I tried breaking the query SQL down to fit it (visibly) into the VBA Editor, and received an error message that it couldn't be broken that many times (in layman's terms. Thats how I understood it).
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 2 12 LastLast

Posting Permissions

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