Results 1 to 6 of 6

Thread: query (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query (2000)

    Hi, having problems getting a query to work. Have a total column with count selected in totals column. Two field provide user slection criteria from a form. One is a date between selection with fields on the foms say; form!frmdate!start and forms!frmdate!end. I need the query to work when no user input has been selected to the query takes the entire unrestricted data. This is when it falls down and returns no results, the IIf([forms]![frmreports]![cmbcoach] Is Not Null,[forms]![frmreports]![cmbcoach],[tblcoaches].[coachid]) helps me find this field when no data is input, it's the date restriction bit where it falls down.

    ANy ideas, thanks Darren.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query (2000)

    My approach would be to alter the query's SQL based on whether or not the user has selected data. Something like:

    If isnull(forms("frmdate").start) then
    currentdb.querydefs("Query Name").SQL = "SELECT... FROM frmreports;"
    else
    currentdb.querydefs("Query Name").SQL = "SELECT... FROM frmreports WHERE myDate between " & _
    "forms('frmdate').start and forms('frmdate').end;"
    end if

    I haven't tested this and you'll need to change the query/field names to the correct ones, but hopefully this gives you an idea of a possible approach. Sorry it's a bit vague.

    Cheers,
    Waggers
    If at first you do succeed, you've probably missed something.

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

    Re: query (2000)

    Another approach, without VBA, is as follows:
    - Remove the criteria for the date field (I'll call it DateField for illustration purposes).
    - In the first empty column in the query grid, enter

    [Forms]![frmDate]![Start]

    - Clear its Show check box.
    - In the Criteria line, enter

    <=[DateField] Or Is Null

    - In the next empty column, enter

    [Forms]![frmDate]![End]

    - Clear its Show check box.
    - In the Criteria line, enter

    >=[DateField] Or Is Null

    This will work with all combinations of blank and non-blank Start and End values.

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query (2000)

    To both of you thanks, I used Hans approach, prefer the easier life! It works fine with one exception, when I specify the dates it lists the details i want in count order i.e.

    User 1/Product A/ count of 4 User2/ProductB/ Count of 5. Howevr when I don't specify a date range the results becone something like User1/Product A/ count 1 (for ie. 01/07/05, User 1/product A//count 7 (i.e. date 02/07/05). I just need overall count totals when no date range is selected rather than count totals for each date.

    I attach a word doc with a screen print of my query for reference.
    Thanks again.

    Regards Darren.

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

    Re: query (2000)

    Set the Total option for both added columns to Where.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query (2000)

    Thanks again.

Posting Permissions

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