Search code examples
sqlms-accessjet

Access SQL Issue

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))
Solution

  • 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)