How to Query with User Prompt

Topic

New Reply

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?

Reply | Quote

Replies

Replace the function Now() with angle brackets and a prompt. The general format is .

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

Reply | Quote

What database?

Reply | Quote

The quick way is to use: DateDiff(“yyyy”,[17 Init Date], [Enter Start date in format mm/dd/yyyy])

Reply | Quote

Can you flesh that out a bit Mark? How does your technique differ from mine, and what makes it quicker for example?

Reply | Quote

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.

Reply | Quote