CODEWITHSUNDEEP
sqlexcelvbams-access
JosephD
JosephD

Reputation: 47

SQL in VBA WHERE Clause

I have been trying to add additional filter criteria to my WHERE clause in sql VBA. The previous select statement works fine, but I can't seem to get the updated WHERE clause to work. Here's what I have:

WHERE tblretirements.Applicationcancelled = 'No' 
    AND tblretirements.FirstPayDate IS NULL 
    OR tblretiremetns.FirstPayDate BETWEEN 'now()' & 'Beginning of Prior Fiscal Year'

I am not that familiar with the BETWEEN statement and am positive that I am messing that up. I need to have the code dynamically reference today's date and the beginning of the prior fiscal year, which is 6/1/2017 right now. Can someone please help? Thank you.

Upvotes: 0

Views: 472

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

The sytax of BETWEEN statement is field_name BETWEEN aaa AND bbb,
NOT field_name BETWEEN aaa & bbb.

modify your code

tblretiremetns.FirstPayDate BETWEEN 'now()' & 'Beginning of Prior Fiscal Year'

to

tblretiremetns.FirstPayDate BETWEEN date() AND dateserial(year(date()) - 1, 6, 1)

Which now() function returns date and time, and date() function returns date only.

Upvotes: 5

Related Questions