We help IT Professionals succeed at work.

IIF date = month end then add a day and format expression builder access

kmoyer9717
kmoyer9717 used Ask the Experts™
on
OK  I am having a syntax problem with expression builder in Access. I am creating a field that looks at a date field and if it is a monthend date it gives me the next month and year formatted like  the following:

Format([Action_Date],"mmm")+Format([Action_Date]," yyyy")

If it is not a monthend date it just gives me:

Format([Action_Date],"mmm")+Format([Action_Date]," yyyy")

I am not sure how to accomplish the IIf it is a month end part.

Help is appreciated.

Thanks,

Kelly
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
IIf(Month([Action_Date]) = Month([Action_Date] + 1), Format([Action_Date], "mmm yyyy"), Format([Action_Date] + 1, "mmm yyyy"))
Top Expert 2016

Commented:


iif([datefield]= dateserial(year(date()),month(date()),0),format([datefield] + 1,"mmm yyyy",date())
Top Expert 2010

Commented:
Or this way:Format([Action_Date] + IIf(Month([Action_Date]) = Month([Action_Date] + 1), 0, 1), "mmm yyyy")
Top Expert 2016

Commented:
this part
[datefield]= dateserial(year(date()),month(date()),0)

will check if it is the end of the  month..
Top Expert 2010
Commented:
Actually, it's even easier than any of those:Format([Action_Date] + 1, "mmm yyyy")If the date is not a month end, then adding a day still keeps it in the same month.If it is a month end, adding a day bumps the month :)
Hamed NasrRetired IT Professional

Commented:
Give an example of two conditions and expected output

Commented:
Private Sub Command0_Click()  
    Dim monthEnd As Integer    

    Action_date = "7/7/2010"  ' example
    monthEnd = Format(Now(), "M")
   
    If Month(Action_date) = monthEnd Then
        Action_date = DateAdd("d", 1, Action_date)
        Debug.Print Action_date
    End If
End Sub
Top Expert 2016

Commented:
i read it as, if it is  the end of the month.. then

did not specify what happens if it is not the end of the month

Author

Commented:
Thanks to everyone that answered. I used MathewsPatricks solution
Top Expert 2016

Commented:
you should have ust said, i need to add a day on the current date. finish. no IIF testing for the end of the month