Comparing dates in sql insert statement

Question

I am trying to insert records to a table from another using sql in access through vba. everything works but I want a field that contains a date to be greater than the first of the year that the user inputs. When I added that command into my statement no records are inserted and I'm pretty sure it is because I am not comparing the date right. I am rather new to sql so any help on this issue and how to fix it would be greatly appreciated! I have posted the statement below.

verifHPMS = "INSERT INTO HPMSVerified([CLIENT ID], [CLIENT NAME], [MARKET SEGMENT], [FORMULARY ID], [FORMULARY NAME], [FORMULARY VERSION],[HPMS EXPORTED],[HPMS EXPORT DATE],[APPROVED DATE]) " & _
            "SELECT DD.[CLIENT ID],DD.[CLIENT NAME],DD.[MARKET SEGMENT],DD.[FORMULARY ID],DD.[FORMULARY NAME],DD.[FORMULARY VERSION],DD.[HPMS EXPORTED],DD.[HPMS EXPORT DATE],DD.[APPROVED DATE] " & _
            "FROM " & tableName & " as DD " & _
            "Where DD.[HPMS EXPORTED] IN (""Yes"") " & _
            "AND DD.[HPMS EXPORT DATE] >= [APPROVED DATE] " & _
            "AND DD.[CLIENT ID] NOT IN(SELECT Exclusions.[Client ID] FROM Exclusions)" & _
            "AND DD.[APPROVED DATE]>01/01/" & year

Are the fields with the date value also of type date? Otherwise it should work — Roy Wasse
– Roy Wasse, Commented Dec 4, 2014 at 21:19

Tom Galvin · Accepted Answer · 2014-12-04 21:24:14Z

1

Try replacing the final line with this:

"AND YEAR(DD.[APPROVED DATE]) >= " & year & " AND MONTH(DD.[APPROVED DATE]) > 1 AND DAY(DD.[APPROVED DATE]) > 1"

This compares each component of the date individually, to avoid issues with date literals and your manual method of string interpolation.

answered Dec 4, 2014 at 21:24

Tom Galvin

9017 silver badges12 bronze badges

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

Comments

Tom Robinson · Accepted Answer · 2014-12-05 21:23:07Z

0

Replace the final line with:

"AND DD.[APPROVED DATE] > #01/01/" & year & "#"

Note that VBA and "Jet" SQL always uses #m/d/y# format for date literals, regardless of regional settings.

answered Dec 5, 2014 at 21:23

Tom Robinson

1,9501 gold badge15 silver badges15 bronze badges

Collectives™ on Stack Overflow

Comparing dates in sql insert statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related