Enter Parameter Value

Hey in Access MDB you can create a query that when you run it, it will ask you to Enter the Paramer Value, User could enter in a year or Team Member Number or however you have the query set up. is this possible in SQL ADP?No, not in SQL. Prompting the user for input is the responsibility of the interface, not the database engine. Access has an interface packaged with the engine. SQL Server is purely a database engine.

Your ASP page will need to check the stored procedure to determine which parameters are required and provide a means for the user to enter them.|||Thank you Blindman, wasnt sure|||How have you been??|||I actually think that an ADP (Access Data Project) can do interactive prompting, but I'm not sure how to make that happen. Since all of the ADP code actually runs on the client (either via the web page or within the project itself), I don't see any problem...

I'll have to experiment and see if I can finger out how to make this fly, but it won't be today.

-PatP|||Yeah I was hoping there would be a way of doing that, I'll have to do some investigating. It would make things a little easier for me though|||Even if you could find a way to automate this at the ASP layer, wouldn't it be limited to prompting for the often cryptic parameter names specified by the procedure developer?

Not very user friendly...|||I'm not sure what ADP projects to ASP. I think that Desiree is looking for a pure ADP configuration at least for now. I'll investigate both ADP and how it projects into ASP though.

-PatP|||ADP ASP ADP ASP ADP ASP ADP ASP...

Oh. aDp!

<Mild embarrasment./>|||I've never had that happen, but I read about it in this book once... ;)

-PatP|||When I open up a stored procedure in an Access Data Project it does prompt me for the parameter.

Enter Month and year option

Hi everyone me again, I have an MDB that I need to Change into an ADP, SQL server is giving me fits on this and I need to make it so that when the users open this report it gives them the option to enter in the month and year in this format "January 2007" and then get the results on the report. The person who created the MDB gave that option with the code below how do I interpret that from Jet SQL to SQL Server?

Code Snippet

SELECT DISTINCTROW Inspectors.[Last Name], Inspectors.[First Name], Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], Count(*) AS [Count Of Main Table]
FROM Inspectors INNER JOIN [Main Table] ON Inspectors.ID = [Main Table].Inspector
GROUP BY Inspectors.[Last Name], Inspectors.[First Name], Format$([Main Table].Date,'mmmm yyyy'), Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and Year]));

This is what I got so far but its giving me fits

Code Snippet

SELECT Inspector, 'Date: Year([Main Table]).Date,mm yyyy)' AS [Date by Month], COUNT('Count of [Main Table]:Count(*)') AS [Count], YEAR(Date)
* 12 + DATEPART('m', Date) - 1 AS Year, 'Date:([Main Table].[Date],mmmm yyyy)' AS [Enter Month and Year]
FROM dbo.[Main Table]
GROUP BY Inspector, 'Date: Year([Main Table]).Date,mm yyyy)', YEAR(Date) * 12 + DATEPART('m', Date) - 1
HAVING ('Date:([Main Table].[Date],mmmm yyyy)' = @.Enter_Month AND 'Date:([Main Table].[Date],mmmm yyyy)' = @.Enter_Year)

You need to use the DATEPART function to get different pieces from your dates.

This documentation on MSDN should get you through it.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx

The way you are calling your functions is syntactically incorrect. Simply use the function name and a column alias if neccessary.

Code Snippet

SELECT DATEPART(yyyy, t.date) AS Year

,COUNT(*) AS Count

FROM table t

GROUP BY t.Date

|||

Is this what you want the output to look like:

Inspector Date by Month Year Enter Month and Year
-- - -- --
1 3 2007 3 2007

or maybe something like:

declare @.enter_year integer set @.enter_year = 2007
declare @.enter_month integer set @.enter_month = 3

insert into dbo.[main table]
select 1, '3/15/7'

SELECT Inspector,
convert(varchar(2), datepart(mm, date)) as [Date by Month],
YEAR(Date) AS Year,
count(*) as [count],
convert(varchar(2), datepart(mm, date)) + ' '
+ convert (varchar(4), year(date)) as [Enter Month and Year]
FROM dbo.[Main Table]
where year(date) = @.enter_year
and month(date) = @.enter_month
group by inspector,
convert(varchar(2), datepart(mm, date)),
YEAR(Date),
convert(varchar(2), datepart(mm, date)) + ' '
+ convert (varchar(4), year(date))

-- Inspector Date by Month Year count Enter Month and Year
-- -- - -- -- --
-- 1 3 2007 1 3 2007

|||Well not exaclty see when they execute the query it asks them to enter in a date [Month and Year] then you get the results. It basically tells the users how many reports each inspector did for that month and year and they it gives the over all total|||

I think that this is basically the query you want.

Code Snippet

SELECT I.[Last Name], I.[First Name],
[Date by Month] = DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]),
[Count Of Main Table] = count(*)
FROM Inspectors I
INNER JOIN [Main Table] MT
ON (I.ID = MT.Inspector)
GROUP BY I.[Last Name], I.[First Name],
DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE])
WHERE (MT.[Date] >= CONVERT(datetime, @.EnterMonthYear)) AND
(MT.[Date] < DATEADD(month, 1, CONVERT(datetime, @.EnterMonthYear)))

@.EnterMonthYear is a varchar variable containing the input date in the form 'mmmm yyyy'. This SQL will error if the text in the variable cannot be converted to a date.

I removed the "DATEPART(year, MT.[Date]) * 12 + DATEPART(month, MT.[Date]) - 1" expression as it simply seemed to have a 1:1 correspondance with the Date by Month expression (so not changing the records generated by the GROUP BY) and was not used anywhere else. It seems to be generating some kind of serial month number.

I simplified the selection condition to work before the grouping and use the datetime fields directly. The one effect of this is that if they input a date with a day number on it (5 January 2007 for instance) they will get the data from 5 January 2007 to 4 February 2007 and will get up to 2 rows per inspector labelled January 2007 and February 2007 grouping this data appropriately.

Rather than using this SQL directly you might like to consider putting it in a stored procedure and calling that if possible. This will allow handling of the bad date problems (and the forcing of the input date to first of the month etc.). Also it will allow any later maintenance to be performed in the datebase rather than application (as long as the stored procedure calling convention/arguments do not change).