CODEWITHSUNDEEP
vbaexcelms-accessdatediff
Gustavo Souza
Gustavo Souza

Reputation: 23

Datediff not working on VBA query to access in Excel

I am trying to make this basic select query on vba in excel:

"SELECT * FROM Tabela1 WHERE DateDiff('yyyy', [Vencimento], " & filter & ") = 0"

So I want take all results where the year on "Vencimento" match the desired year. But this Datediff returns -111 (or something like this) when "filter = 2016" and "Vencimento = 01/11/2016".

[Vencimento] is a column on a access table, and filter is in a cell on excel. I guess excel are not working with [Vencimento] as a date even I configuring in access this column as date and I have no idea how make this.

Upvotes: 1

Views: 312

Answers (1)

CLR
CLR

Reputation: 12289

The following should work in T-SQL and MS Access:

SELECT * FROM Tabela1 WHERE YEAR( [Vencimento] ) = 2016

So if you're looking to build that from the value held in a cell called filter then:

SQL$ = "SELECT * FROM Tabela1 WHERE YEAR( [Vencimento] ) = " & range("filter").value

But if your filter cell contains a date, but is formatted to only show the year then you'll need to extract the Year value from that like so:

SQL$ = "SELECT * FROM Tabela1 WHERE YEAR( [Vencimento] ) = " & Year(range("filter").value)

Upvotes: 1

Related Questions