MS Access Date Format

Question

Hello everybody,

I have a simple selection query with just two fields: 1) Date (dd/mm/yyyy format), 2) # of Items (net daily increase) and I need to add a third field with the cumulate # of items up to that date.

Using the DSum() function I thought I found a simple solution, but looking at the query output, it looks like Access does not always (yikes!) interpret correctly the date, i.e. swapping dd with mm.

This is the function I use to compute the aggregate # of items
SigmaNodi: DSum("[TotNodi]";"qtemp";"[Data] <= #" & [Data] & "#")

And these are the first rows of the query output.

For example, looking at row 3, it seems that Access interprets the date as Jan-07 2015 (instead of Jul-01, 2015) older than the first date in the list and therefore the cumulate # of items up to that date is correctly 0. The same behaviour shows up here and there in the following rows...

Am I missing something?

Thanks,
GMax

Answer 1

Solved!

It seems that SQL only accepts date in mm/dd/yyyy format, therefore I had to change the arguments of the DSum() function. It is a little bit clumsy, but it works:

SigmaNodi: DSum("[TotNodi]";"qtemp";"[Data] <= #" & Month([Data]) & "/" & Day([Data]) & "/" & Year([Data]) & "#")

I am curious to find if there is a more elegant answer ;-)

HTH