Type mismatch problem on filtering

[dominicall]

Grrrrrrrr.........

Datebase = SQL
Date column formats = datetime

Anyone see this.... have a table containing availability dates for holiday boats - with weekly availability and am having a bit of trouble finding the problem...

Select string = "SELECT * FROM tbl_Prices WHERE WeekStart > " & DATE & " AND BoatID=" & BoatID & " ORDER BY WeekStart ASC;"That all works fine and selects the data...

Am then trying to filter the recordset so it displays prices by year using....'Now get the prices and filter by year
Dim NowYear, ThenYear

NowYear = DatePart("yyyy",DATE)
ThenYear = DatePart("yyyy",DateAdd("yyyy",2,DATE))

Dim strCriteria, intYear

For intYear = NowYear To ThenYear
strCriteria = "WeekStart = '*" & intYear & "*'"

rsGetPrices.Filter = strCriteria
But whenever it hits the filter it throws a type mismatch.... have tried various definitions of strCriteria - without success.

Am on point of using computer as a football - grrrr - LOL

dominicall

[glenngv]

is the datatype of WeekStart varchar/char?

strCriteria = "WeekStart LIKE '*" & intYear & "*'"

or maybe % instead of * ?

strCriteria = "WeekStart LIKE '%" & intYear & "%'"

but i think it's numeric as the name suggests. so it's should be:

strCriteria = "WeekStart = " & intYear

[dominicall]

UPDATE

Fixed it.... change the initial SELECT statement to...SELECT *, Year(WeekStart) AS Year FROM tbl_Prices WHERE WeekStart > " & DATE & " AND BoatID=" & BoatID & " ORDER BY WeekStart ASC;and the filter string tostrCriteria = "Year = " & intYear and it all works fine....

Seems so obvious can't believe I didn't see it before - oops...

end of update


Already tried both those options... no joy...

Format of WeekStart column is datetime...

Am attempting to filter on just the year element of the date in the datetime field...

Bizarre????

dominicall :confused:

[whammy]

Hey, if it works, I only have one thing to say... Yay!!!

;)