Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates parameter to add (Access2000-03)

    Hi again!
    I've got wonderfull suggestion from Hans on parameters.
    I am using [Forms]![Switchboard]![ID_Name] or [Forms]![Switchboard]![ID_Name] is Null
    and it works great!

    Now I've gotten request for Date parameter From - To.
    I have Date field added to the queries.
    Now I need to have it same way - if blank - print for ALL Dates - or if user typed in dates - then read it and filter data.

    First Parameter is still present.

    Any suggestions? Thanks so much!

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

    Re: Dates parameter to add (Access2000-03)

    - Add the date field to the query grid.
    - Clear the Show check box.
    - Enter the following expression in the Criteria line, substituting the correct name for the form and text box:

    >=[Forms]![Switchboard]![StartDate] Or [Forms]![Switchboard]![StartDate] Is Null

    - Add the date field another time to the query grid (yes, again!)
    - Clear the Show check box.
    - Enter the following expression in the Criteria line, substituting the correct name for the form and text box:

    <=[Forms]![Switchboard]![EndDate] Or [Forms]![Switchboard]![EndDate] Is Null

    If the user leaves both text boxes blank, all dates will be returned.
    If the user leaves StartDate blank but provides an EndDate, all dates on or before EndDate will be returned.
    If the user provides a StartDate but leaves EndDate blank, all dates on or after StartDate will be returned.
    If the user provides both, all dates between and including the two will be returned.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates parameter to add (Access2000-03)

    Thanks so much!
    I was sort of thinking of adding it twice but I was hoping to avoid it - just lazy I guess! :-)) Kidding!

    Thanks so much and have a good day!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates parameter to add (Access2000-03)

    I though I had done everything you had suggested but dates doesn't filter.
    Here is the query...does it look ok?

    SELECT RSA.NAME_DISPLAY, RSA.RECRUITER_ID, RSA.[Total Of DESCR1], RSA.[Review], RSA.[Linked
    FROM RSA
    WHERE
    (((RSA.RECRUITER_ID)=[Forms]![Switchboard]![Recruiter_ID_Name]) AND ((RSA.HRS_STATUS_DT)=[Forms]![Switchboard]![Date_From] And (RSA.HRS_STATUS_DT)=[Forms]![Switchboard]![Date_To])) OR (((RSA.RECRUITER_ID)=[Forms]![Switchboard]![Recruiter_ID_Name]) AND ((RSA.HRS_STATUS_DT)=[Forms]![Switchboard]![Date_To]) AND (([Forms]![Switchboard]![Date_From]) Is Null)) OR (((RSA.RECRUITER_ID)=[Forms]![Switchboard]![Recruiter_ID_Name]) AND ((RSA.HRS_STATUS_DT)=[Forms]![Switchboard]![Date_From]) AND (([Forms]![Switchboard]![Date_To]) Is Null)) OR (((RSA.RECRUITER_ID)=[Forms]![Switchboard]![Recruiter_ID_Name]) AND (([Forms]![Switchboard]![Date_From]) Is Null) AND (([Forms]![Switchboard]![Date_To]) Is Null)) OR ((([Forms]![Switchboard]![Recruiter_ID_Name]) Is Null));

    THANK YOU

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

    Re: Dates parameter to add (Access2000-03)

    Please look at my previous reply again. You should use >= and <= in the criteria as indicated there, not =.
    Note: the criteria may be displayed incorrectly in the e-mail notification of my reply, if you got that. The browser should display them correctly.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates parameter to add (Access2000-03)

    Oh, thanks! I would never guess...
    However I am having another issue.
    Now my Totals would not react to the Date Parameters.

    I have my Report based on a table and because of Parameters - there is a query that selects everything from that table where parameters are[ ]...
    Data is at Group Header.
    Totals located at the Report Footer and datatype is Text.
    Could that be the problem?

    Thanks as always

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

    Re: Dates parameter to add (Access2000-03)

    What is the control source of Totals? A field in the record source of the report, or a formula beginning with = ? If it is a formula, what does it look like?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates parameter to add (Access2000-03)

    What is the control source of Totals?

    Group Header Field1
    Report Footer =Sum([Field1])

    Thanks

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

    Re: Dates parameter to add (Access2000-03)

    I don't understand why that wouldn't take the filter into account. Without seeing the database it's impossible to say more about it.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates parameter to add (Access2000-03)

    OK, I will try again! If you do not see any problems with this approach - I must be doing aomething wrong on my own - as always! :-)

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates parameter to add (Access2000-03)

    Hans!
    You are the best!
    It works! I twicked it a bit. I needed to place SUMs at both levels - Group and Report footer because of the Dates addition factor.

    You gotta start charging me in USD! :-)
    Thanks a million times!!!!!!!

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

    Re: Dates parameter to add (Access2000-03)

    Glad you were able to solve it. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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