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

    Query doesn't return desired results (2002)

    OK, after 3 hours with no success and a night of no sleep due to trying to fix this in my head, I'm turning to the experts.

    I have a query based on [tblClientList] that contains field [referral_2]. I need to be able to manipulate this data in a variety of ways, which I am able to set up. My problem occurs because I need to know the actual count of the [referral_2]. Since this field is a text field I added another field [referralcount] which assigns numbers to [referral_2] and allows me to get totals by [referral_2]. This works fine. Where I run into trouble is when I try to filter by date range or year. If I enter [entrydate] into the query, the counts are listed by the # of [referral_2] per [entry_dt].

    My ulitmate goal is to be able to filter the report by either dates or year on frmStrategic planning, but I can't even get it to do what I want without using the form. I will at some point need to set it up as a crosstab query (which I experimented with but the problem was the same) with [referral_2] as a row and Months as a column header and the value as the total count of [referral_2]. This will need to be filtered by year which will be entered on frmStrategicPlanning. I'm pretty sure I can set that up once the filtering by entry_dt issue is fixed.

    Conceeding defeat,
    Leesha

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

    Re: Query doesn't return desired results (2002)

    You can add entry_date to the query and set its Total option to Where. This will automatically clear the Show check box, so that entry_date will not be in the displayed result of the query. Set the criteria for entry_date to

    Between [Forms]![frmStrategicPlanning]![txtStartDate] And [Forms]![frmStrategicPlanning]![txtEndDate]

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

    Re: Query doesn't return desired results (2002)

    WOW!! I didn't try that before because I figured for sure it would require something I wouldn't know how to do. That is awesome.

    Thanks Hans,
    Leesha

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

    Re: Query doesn't return desired results (2002)

    Hi,

    If I want to make enter the text "unknown" to [referral_2] in blank rows, how would I could it? I tried doing an iif and used nz, but of course didn't get the syntax correct.
    Thanks,
    Leesha

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query doesn't return desired results (2002)

    Use :
    NewReferral:IIf(IsNull([referral_2]),"Unknow",[referral_2])
    Note that you'll have to change referral_2 to NewReferral in all queries,forms and reports based on that query
    Francois

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

    Re: Query doesn't return desired results (2002)

    That worked great! Believe it or not I tried experimenting with isnull but got all sorts of errors (again my syntax) and so tried nz with no result. Sometimes my experimenting works but most times not!

    Thanks,
    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
  •