SQL in VBA WHERE Clause

Question

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.

PaichengWu · Accepted Answer · 2018-09-14 03:58:55Z

5

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.

edited Sep 14, 2018 at 3:58

answered Sep 14, 2018 at 1:05

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL in VBA WHERE Clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related