Pass two parameters in query

Hello,
I am new to SQL Server 2000 but proficient in Access, and the switch is
giving me a challenge. I am trying to do a simple parameter query, which I
believe is now called a View in SQLServer. In Access, I would have entered
in Criteria of RecordEntry this statement: Between [BeginningDate] and
[EndingDate]. Even when I view the query in Access' "SQL View", it is not
correct in correct code for SQL Server.
The field I want to have the user populate is UsageLog.RecordEntry, a
datetime field, so they can view only those records between the dates they
enter in the pop up window when the query is ran.
Here is the code:
SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
FROM dbo.ACFTSchedule INNER JOIN
dbo.UsageLog ON dbo.ACFTSchedule.ACFTSched =
dbo.UsageLog.FKACFTSched INNER JOIN
dbo.AirspaceArea ON dbo.UsageLog.FKAirspace =
dbo.AirspaceArea.AirspaceArea INNER JOIN
dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD =
dbo.BaseSQD.BaseSQD INNER JOIN
dbo.ControlAgency ON dbo.UsageLog.FKControlType =
dbo.ControlAgency.ControlAgency INNER JOIN
dbo.MissionType ON dbo.UsageLog.FKMissionType =
dbo.MissionType.MissionType INNER JOIN
dbo.PlaneType ON dbo.UsageLog.FKPlaneType =
dbo.PlaneType.PlaneType
WHERE UsageLog.dbo.RecordType @.BeginningDate AND @.EndingDate
GO
The current error I am getting is saying that "Error in WHERE clause near
'UsageLog'. Unable to parse query text." Any assistance will be greatly
appreciated!
Thank you,
MaryMary, change the WHERE clause:
WHERE dbo.UsageLog.RecordType >= @.BeginningDate
AND dbo.UsageLog.RecordType <= @.EndingDate
OR
WHERE dbo.UsageLog.RecordType BETWEEN @.BeginningDate AND @.EndingDate
Cordially,
Richard_SQL
"Mary" wrote:

> Hello,
> I am new to SQL Server 2000 but proficient in Access, and the switch is
> giving me a challenge. I am trying to do a simple parameter query, which
I
> believe is now called a View in SQLServer. In Access, I would have entere
d
> in Criteria of RecordEntry this statement: Between [BeginningDate] and
> [EndingDate]. Even when I view the query in Access' "SQL View", it is not
> correct in correct code for SQL Server.
> The field I want to have the user populate is UsageLog.RecordEntry, a
> datetime field, so they can view only those records between the dates they
> enter in the pop up window when the query is ran.
> Here is the code:
> SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
> FROM dbo.ACFTSchedule INNER JOIN
> dbo.UsageLog ON dbo.ACFTSchedule.ACFTSched =
> dbo.UsageLog.FKACFTSched INNER JOIN
> dbo.AirspaceArea ON dbo.UsageLog.FKAirspace =
> dbo.AirspaceArea.AirspaceArea INNER JOIN
> dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD =
> dbo.BaseSQD.BaseSQD INNER JOIN
> dbo.ControlAgency ON dbo.UsageLog.FKControlType =
> dbo.ControlAgency.ControlAgency INNER JOIN
> dbo.MissionType ON dbo.UsageLog.FKMissionType =
> dbo.MissionType.MissionType INNER JOIN
> dbo.PlaneType ON dbo.UsageLog.FKPlaneType =
> dbo.PlaneType.PlaneType
> WHERE UsageLog.dbo.RecordType @.BeginningDate AND @.EndingDate
> GO
> The current error I am getting is saying that "Error in WHERE clause near
> 'UsageLog'. Unable to parse query text." Any assistance will be greatly
> appreciated!
> Thank you,
> Mary|||Richard,
Thank you very much for your quick response. Should I be in the Views
window? I am getting an error saying that this variable @.BeginningDate need
s
to be declared.
In Access I would declare it with a Dim statement, but I am in
SQLServer.
Or should this be in a stored procedure? I want to be able to call this
query from a command button when we make the web front.
Thank you again,
Mary
"Richard" wrote:
> Mary, change the WHERE clause:
> WHERE dbo.UsageLog.RecordType >= @.BeginningDate
> AND dbo.UsageLog.RecordType <= @.EndingDate
> OR
> WHERE dbo.UsageLog.RecordType BETWEEN @.BeginningDate AND @.EndingDate
>
> Cordially,
> Richard_SQL
>
> "Mary" wrote:
>|||Richard,
The code you gave me for the WHERE statement worked great in a stored proc.
Thank you. But how do I run it to prompt me for the 2 inputs, so I can make
sure it works correctly?
The new stored procedure (below) and the syntax has been cleared by
SQLServer and it is visible in Query Analyzer and ran with the sp_help syste
m
stored proc.
CREATE PROCEDURE dbo.DateParameter
@.BDate datetime, @.EDate datetime
AS
SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
FROM dbo.ACFTSchedule INNER JOIN dbo.UsageLog ON
dbo.ACFTSchedule.ACFTSched = dbo.UsageLog.FKACFTSched INNER JOIN
dbo.AirspaceArea ON dbo.UsageLog.FKAirspace = dbo.AirspaceArea.AirspaceArea
INNER JOIN dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD = dbo.BaseSQD.BaseSQD INNER
JOIN dbo.ControlAgency ON dbo.UsageLog.FKControlType =
dbo.ControlAgency.ControlAgency INNER JOIN dbo.MissionType ON
dbo.UsageLog.FKMissionType = dbo.MissionType.MissionType INNER JOIN
dbo.PlaneType ON dbo.UsageLog.FKPlaneType = dbo.PlaneType.PlaneType
WHERE dbo.UsageLog.RecordEntry >= @.BDate AND dbo.UsageLog.RecordEntry <=
@.EDate
GO
Thank you,
Mary :)
"Mary" wrote:
> Richard,
> Thank you very much for your quick response. Should I be in the Views
> window? I am getting an error saying that this variable @.BeginningDate ne
eds
> to be declared.
> In Access I would declare it with a Dim statement, but I am in
> SQLServer.
> Or should this be in a stored procedure? I want to be able to call this
> query from a command button when we make the web front.
> Thank you again,
> Mary
> "Richard" wrote:
>|||Mary,
You're welcome... Run this query:
DECLARE @.StartDate DATETIME,
@.EndDate DATETIME
SET @.StartDate = '2006-01-01' --YYYY-MM-DD
SET @.EndDate = '2006-03-22'
EXEC dbo.DateParameter @.BDate = @.StartDate, @.EDate = @.EndDate
go
Richard
"Mary" wrote:
> Richard,
> The code you gave me for the WHERE statement worked great in a stored proc
.
> Thank you. But how do I run it to prompt me for the 2 inputs, so I can ma
ke
> sure it works correctly?
> The new stored procedure (below) and the syntax has been cleared by
> SQLServer and it is visible in Query Analyzer and ran with the sp_help sys
tem
> stored proc.
> CREATE PROCEDURE dbo.DateParameter
> @.BDate datetime, @.EDate datetime
> AS
> SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
> FROM dbo.ACFTSchedule INNER JOIN dbo.UsageLog ON
> dbo.ACFTSchedule.ACFTSched = dbo.UsageLog.FKACFTSched INNER JOIN
> dbo.AirspaceArea ON dbo.UsageLog.FKAirspace = dbo.AirspaceArea.AirspaceAre
a
> INNER JOIN dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD = dbo.BaseSQD.BaseSQD INN
ER
> JOIN dbo.ControlAgency ON dbo.UsageLog.FKControlType =
> dbo.ControlAgency.ControlAgency INNER JOIN dbo.MissionType ON
> dbo.UsageLog.FKMissionType = dbo.MissionType.MissionType INNER JOIN
> dbo.PlaneType ON dbo.UsageLog.FKPlaneType = dbo.PlaneType.PlaneType
> WHERE dbo.UsageLog.RecordEntry >= @.BDate AND dbo.UsageLog.RecordEntry <=
> @.EDate
> GO
> Thank you,
> Mary :)
>
> "Mary" wrote:
>