VBA date range problem

[Ferenc]

Hi everyone!

First of all I am new to Access and Vba, so please be patient with me

I want to loop through a table of product sales and update a single field and the value of that field depends the date of the transaction. For the update I create an SQL UPDATE command and use WHERE BETWEEN date1 and date2.


I loop through another table where I pick up 3 date periods for every product code.
When I generate the SQL statements for that particular transaction the statements look like this:

sqlStatement = "UPDATE tbl SET something = """ & values & """ WHERE INV_DATE BETWEEN #" & dateFrom & "# AND #"& dateTo &"# AND PRODUCT_CODE = """ & prodCOde & """ "

currentdb.execute sqlStatement

In the debug window the generated statements are:

UPDATE tbl SET something = ""value1"" WHERE INV_DATE BETWEEN #01/01/2014# AND #31/05/2014# AND PRODUCT_CODE = ""00115522""

UPDATE tbl SET something = ""value2"" WHERE INV_DATE BETWEEN #01/06/2014# AND #30/09/2014# AND PRODUCT_CODE = ""00115522""

UPDATE tbl SET something = ""value3"" WHERE INV_DATE BETWEEN #01/10/2014# AND #31/12/2014# AND PRODUCT_CODE = ""00115522""

When it runs the second statement updates all the values that belong to the first date range and the third updates all the values that belong to the first and second date range.
It just doesn't seem logical to me but I am obviously missing something. Is there any chance someone could help with this?

Thanks,

Ferenc

(I have tried to use >= AND <= instead of BETWEEN but the same happened)

 

[namliam]

Gizmo is right, though his sample could be better...
#01/06/2014# is not 1st of June, it is (in US format) Jan 6th, hence the "wrong" update.
same for 1/10 not 1 oct, but 10 Jan

The other (end of the month) dates "magicaly work" simply because the 30/31st month doesnt exist and M$ somehow figures it properly to 30 sep, but neglects then to keep things in one way and breaks your previous dates.

SQL requires US dates unfortunately... a caviat that many people trip over

 

[namliam]

but 1/6 breaks where as 30/09 will not break.

 

[TimW]

Hi All
I use an Allen Browne function. SQLDATE

Works for me...http://allenbrowne.com/ser-36.html