Access SQL Issue

Question

I am curious if there are any experts in Access' version of SQL that could help me decode the below? I am not great in Access and try use SSMS but I am taking over an already built report.

Thanks!

LT CRD: IIf(Day(Date()+[IAM_MAN_LEAD_TIME]) Between 1 And 15,DateSerial(Year(Date()+[IAM_MAN_LEAD_TIME]),Month(Date()+[IAM_MAN_LEAD_TIME]),15),DateSerial(Year(Date()+[IAM_MAN_LEAD_TIME]),Month(Date()+[IAM_MAN_LEAD_TIME])+1,0))

I am trying to understand what that snippet of Access SQL (Jet SQL?) is trying to say. I am not well versed in Access specific SQL — user3496218, Oct 29 '18 at 23:43

Lee Mac · Accepted Answer · 2018-10-29T23:53:11.847

In words, the code is saying

"If the current date + [IAM_MAN_LEAD_TIME] results in a date in the first 15 days of a month, then return the 15th of that month; else, return the date of the last day of the month."

For reference -

Date() returns the current date
Day() returns the day part of a date, e.g. Day(#2018-10-29#) = 29
DateSerial() returns a date given a year, month & day argument.
Year() returns the year part of a date, e.g. Year(#2018-10-29#) = 2018
Month() returns the month part of a date, e.g. Month(#2018-10-29#) = 10

Also note that DateSerial(Year, Month, 0) will return the last day in the previous month i.e. the day before DateSerial(Year, Month, 1)

Access SQL Issue

1 Answers1