Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Query with User Prompt

    I have the following hard coded query that returns all the members that have 25 years of service


    Field: Years: DateDiff("yyyy",[17 Init Date],Now())
    Criteria: 25


    How can I change it so the query will prompt the user to enter the date (Now) I want to calculate from?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    565
    Thanks
    51
    Thanked 70 Times in 68 Posts
    Replace the function Now() with angle brackets and a prompt. The general format is <parameter, description, default value>.

    When the user runs the query they must press [Control-Shift-M] or select SSMS | Query | Specify Values for Template Parameters. This activates the template replacement system; SSMS will not do this for you.

    Note that this only works with Microsoft SQL Server and SSMS.

    For more information see:

    Introduction to Using the Template Explorer in SQL Server Management Studio

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    What database?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    The quick way is to use: DateDiff("yyyy",[17 Init Date], [Enter Start date in format mm/dd/yyyy])
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    565
    Thanks
    51
    Thanked 70 Times in 68 Posts
    Can you flesh that out a bit Mark? How does your technique differ from mine, and what makes it quicker for example?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    His example is for Access - your post was for SQL Server, but the original question was for Access, although the poster didn't indicate. But he's been around as long as Mark and I have, and we know he is working with Access, not SQL Server. Hope that clears things up.
    Wendell

  7. The Following User Says Thank You to WendellB For This Useful Post:

    BHarder (2016-11-01)

Posting Permissions

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