select statement

I have a select statemnt in an ASP page I have been using without any problems when connecting to Access. I have now upgraded my database to SQL Server and I'm getting a syntax error with the "isnull" function.


Erorr Message:


Microsoft OLE DB Provider for SQL Server error '80040e14'

The isnull function requires 2 arguments.

/WorkRequest/RequestViews.asp, line 469


<code>

'fldAssignedTo
If strUser = "All" Then
strUser = " (fldAssignedTo Like '%' or isNull(fldAssignedTo)) "
ElseIF strUser = "Unassigned" Then
strUser = " (isNull(fldAssignedTo)) "
Else
strUser = " (fldAssignedTo = '" & strUser & "') "
End If

'Complet SQL statement
strSql = "Select fldReqNum, fldDescription, fldAssignedTo, fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where " & strUser

set objRS=server.CreateObject("ADODB.RecordSet")
objRS.Open strSql, objConn, adOpenKeySet

</code>

Here is the syntax I use for Nulls.

SELECT * FROM MyTable WHERE MyField IS NULL

or

SELECT * FROM MyTable WHERE MyField IS NOT NULL

I hope this helps.

Thanks. The "Is Null" worked. I have another similar question. I have another select statemnent that doesn't seem to be working after switching to SQL Server from Access.

Error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '#'.

/WorkRequest/RequestViews.asp, line 475


<code>

strSql = "Select fldReqNum, fldDescription, fldAssignedTo, fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where ((fldReqDate >= #12/31/69#) and (fldReqDate <= #1/2/2099#))"

</code>

Try


strSql = "Select fldReqNum, fldDescription, fldAssignedTo, fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where ((fldReqDate >= '12/31/69') and (fldReqDate <= '1/2/2099'))"

Thanks. That worked perfectly. I don't know why I haven't heard of that. I thought # signs were the standard for dates.

"#" sign is in access because access isn't true SQL. It borrows a ton of VBA functions, such as Trim(), Format() and mixes them together with its weaker sql engine.

Also, if your doing a ton of statements like this for date ranges use between to save you from cutting, pasting, and editing everything. At least thats what do, could be that I'm just overly lazy.
strSql = "Select fldReqNum, fldDescription, fldAssignedTo,
fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where fldReqDate BETWEEN '12/31/69' and '1/2/2099'"