CODEWITHSUNDEEP
sqldatems-access
user3496218
user3496218

Reputation: 209

Access date calculation

I am pretty new to access SQL and trying to figure out what this date calculation means: 

>Date() And <DateAdd("d",
                     -1,
                     DateAdd("m",
                              8,
                              DateAdd("d",
                                       -(Day(Date())-1),
                                       Date())
                            )
                     )

Any help interpreting would be great.

Thanks.

Upvotes: 0

Views: 82

Answers (2)

Gustav
Gustav

Reputation: 56026

It might be simpler to use DateSerial, at least easier and faster to comprehend:

>DateSerial(Year(Date()), Month(Date()) + 8, 0)

Also, I guess it really should read >=, thus your criteria could read:

>Date() And <=DateSerial(Year(Date()),Month(Date())+8,0)

Upvotes: 0

Dai
Dai

Reputation: 155698

Let's look at the last expression:

DateAdd("d",-1,DateAdd("m",8,DateAdd("d",-(Day(Date())-1),Date())))

It's easier to read if you separate out the function arguments to their own line:

DateAdd(
    "d",
    -1,
    DateAdd(
        "m",
        8,
        DateAdd(
            "d",
            - ( Day( Date() ) - 1 ),
            Date()
        )
    )
)
  • DateAdd( "d", a, b ) Adds a-many days to b.
  • DateAdd( "m", a, b ) Adds a-many months to b.
  • ( Day( Date() ) - 1 ) is today's day-of-month, minus 1 (Date() is 2017-02-07, so the result is 6.
  • DateAdd( "d", -6, Date() ) will subtract 6 days from today's date, so the result is 2017-02-01.
  • DateAdd( "m", 8, (2017-02-01) ) will add 8 months to 2017-02-01, so the result is 2017-10-01.
  • DateAdd( "d", -1, (2017-10-01) ) will subtract 1 day from 2017-10-01, so the result is 2017-09-30.

So the expression your originally posted then becomes: > Date() AND < (2017-09-30). This is not valid SQL because the comparison operators <, >, and = need two operands each. It's possible the author thinks they're used like the BETWEEN operator, but they're wrong.

Out of curiosity, I turned these DateAdd operations into a function and produced a plot of the function against the range of dates in 2017:

enter image description here

It looks like its a function to get the date of the last-day of the month that is 8 months from the present, so any Date() in January 2017 (01-31) results in 2017-08-31.

Upvotes: 4

Related Questions