Sql

[Lord Zarm]

I am having a problem with my SQL statement...

stry = "Select * from TabCheckHistory where ClientId = '" & CID & "'"
stry = stry & " and Format(CheckDate,'YYYY') = " & LastYear

I guess I can't use the format function in SQL????

[Twodogs]

Currently your sql statement reads like this:

stry = "Select * from TabCheckHistory where ClientId = '12345' and 2003 = 2003

I think what you want is

stry = "Select * from TabCheckHistory where ClientId = '" & CID & "'"
stry = stry & " and DatePart('yyyy', checkdate) = '" & LastYear & "'"

[buddhi]

Hi,

DatePart function returns 'int' as its' output type. So the above posting should change like this...

stry = "Select * from TabCheckHistory where ClientId = '" & CID & "'"
stry = stry & " and DatePart('yyyy', checkdate) =" & LastYear

Also note that this SQL statement can only be used with SQL Server and Access.

[Twodogs]

Very true - I added the single quotes in because sql server (and access) will do an implicit conversion (yes...I can be a bad programmer from time to time...) and this way you wouldn't have to cast the text of your input box.

Buddhi's way is clearly more correct...and I'm lazy

[buddhi]

Thanks two dogs....

Anyway to improve my post you have given me the basis..