SQL query using DateDiff - Error - anyone see why ?

VBKid04
04-14-2004, 07:56 PM
I have a SQL query that is using the DateDiff function, but the system does not like it...

Here is how I am using it:

Set newTaskRS = New ADODB.Recordset

newTaskRS.Open "Select * FROM Query WHERE UserID='" & txtUserID & "'
OR AdminID='" & txtAdminID & "' AND DateDiff('d', ProblemDate, Now) <" &
Val(txtUserInputDate.Text), db, adOpenStatic, adLockOptimistic


But the system keeps throwing up the error:
Run time error -2147217900 (80040e14)
Syntax error (missing operator) in query expression.


Can anyone please help correct it?

Thanks

NOTE: There are no line breaks like my above code, I have just done that to make it easier to read here

wengwashere
04-15-2004, 12:35 AM
Set newTaskRS = New ADODB.Recordset

newTaskRS.Open "Select * FROM Query WHERE UserID='" & txtUserID & "'
OR AdminID='" & txtAdminID & "' AND DateDiff('d', ProblemDate, Now) <" &
Val(txtUserInputDate.Text), db, adOpenStatic, adLockOptimistic


try this :

newTaskRS.Open "Select * FROM Query WHERE UserID='" & txtUserID & "'
OR AdminID='" & txtAdminID & "' AND (DateDiff(d, ProblemDate, Now) <" &
Val(txtUserInputDate.Text)), db, adOpenStatic, adLockOptimistic

i removed the '' in the d parameter of datediff, i also placed a ( ) between the Datediff for better viewing

LaVolpe
04-15-2004, 09:21 AM
If the above doesn't work, you can also try this...Changes shown in bold.

newTaskRS.Open "Select * FROM Query WHERE UserID='" & txtUserID & "'
OR AdminID='" & txtAdminID & "' AND " & DateDiff("d", ProblemDate, Now) & "<" & Val(txtUserInputDate.Text), db, adOpenStatic, adLockOptimistic

MKoslof
04-15-2004, 09:24 AM
You DateDiff functions should look like this:



DateDiff("d", Now, TheDate)



D with a quote

wengwashere
04-15-2004, 11:32 PM
.without the double quote if youre executing it in SQL server as a SQL Statement

.with double quote if youre executing it in VB

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum