Search code examples
sqlexcelvbasharepoint-2010

VBA 'Datatype Mismatch in query expression' when querying SharePoint List

I am able to successfully query my sharepoint list using VBA. However, I get a datatype mismatch error when adding a date condition. I am trying to query all dates that are greater than or equal to the current year.

The sharepoint list has a date column and formatted as "short date" and the VBA code is below.

Appreciate someone helping me with the correct syntax.

I have tried the following queries that don't work:

'r = DateSerial(Year(Date), 1, 1)
sSQL = "SELECT [fullname] FROM [listname] WHERE [Holiday Date] >= '" & Format(DateValue(r), "m/d/yyy") & "'"

sSQL = "SELECT [fullname] FROM [listname] WHERE [Holiday Date] >= '" & DateValue("1/1/2019")") & "'"

sSQL = "SELECT [fullname] FROM [listname] WHERE [Holiday Date] >= '" & DateSerial(Year(Date), 1, 1) & "'"
Solution

  • This should work :)

    sSQL = "SELECT [fullname] FROM [listname] WHERE [Holiday Date] >="&"1/1/"& Year(Now);