MS Access Date Format

GMax64 96 Reputation points
2021-02-04T09:40:02.637+00:00

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.
63965-access.jpg

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
830 questions
0 comments No comments
{count} votes

Accepted answer
  1. GMax64 96 Reputation points
    2021-02-04T10:04:18.35+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful