Comparing dates MS SQL Server

[emozley]

Hi,

I am trying to pull records where a date is equal to or greater than a date entered in a form. So far I have:

StartDate=Request.Form("StartDate")
If StartDate="" Then StartDate=Date()
StartDate=DatePart("yyyy", StartDate) & "/" & DatePart("m", StartDate) & "/" & DatePart("d", StartDate)

If Request.Form("cTranID")<>"0" Then
TBL.Open "SELECT Transcript.cFirstName, Transcript.cLastName FROM Transcript WHERE cTranID='" & Request.Form("cTranID") & "'", DB
If Not TBL.EOF Then
cFirstName=TBL("cFirstName")
cLastName=TBL("cLastName")
End IF
TBL.Close

Response.Write(cFirstName) & " " & cLastName & " has been given "
TBL.Open "SELECT COUNT(*) AS SecretaryTotalJobs FROM Jobs WHERE cTranID='" & Request.Form("cTranID") & "' AND dtTranEnd >= #" & StartDate & "#", DB
If Not TBL.EOF Then
SecretaryTotalJobs=TBL("SecretaryTotalJobs")
Else
SecretaryTotalJobs=0
End If
TBL.Close

I am getting an error in my query

TBL.Open "SELECT COUNT(*) AS SecretaryTotalJobs FROM Jobs WHERE cTranID='" & Request.Form("cTranID") & "' AND dtTranEnd >= #" & StartDate & "#", DB

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '#'.

I'm not sure whether I am doing the wrong thing by converting the date entered into yyyy/mm/dd or if I should be using FormatDateTime or something else.

Any ideas?

Thanks very much

Ed

 

[gmmastros]

In Access, you surround dates with # signs. Since it appears as though you are using sql server, you will need to change the pound sign # with apostrophes.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[emozley]

Perfect!

Thanks very much

Ed