Access Date format: month start, month end (1 Viewer)

[kplatt]

Can someone help me with this Access/Visual basics question. I need syntax that will allow me to show the start date of each month and the last day of each month that a particular date falls into. For example if my record date was in 04/16/09, I need the month start date and month end date to show on separate cells through expression builder. Example is below

04 01 09 and 04 30 09​

Thanks​

Kevin​

 

[boblarson]

You can use DateSerial to get a specific date based on parts:

Beginning of the current month: DateSerial(Year(Date()), Month(Date()), 1)

End of the Current Month: DateSerial(Year(Date()), Month(Date())+1, 0)

 

[kplatt]

Thanks bob, but what if the date is for example 04/16/09 and not the current date. The control source that would be affected is "SDate". Can you put that in an equation and get results as the start of april and end of april? Thanks

 

[boblarson]

Thanks bob, but what if the date is for example 04/16/09 and not the current date. The control source that would be affected is "SDate". Can you put that in an equation and get results as the start of april and end of april? Thanks

Beginning of the month: DateSerial(Year([SDate]), Month([SDate]),1)

End of the month: DateSerial(Year([SDate]), Month([SDate]+1, 0)

And if it was displayed on a form in a text box's control source:

="The Start of " & Format([SDate],"mmmm") & " is " & DateSerial(Year([SDate]), Month([SDate]),1) & " And the end of " & Format([SDate],"mmmm") & " is " & DateSerial(Year([SDate]), Month([SDate]+1, 0)

 

[kplatt]

Thanks you're awesome. Can you do that with quarters also? Also I need to
format the date at as 04 16 09. Would you know how to put it in
that format? The reason is because I am putting the data into an already
formed report template (basically an image that I pasted to the report)
Thanks
Kevin

 

[boblarson]

You can format the date any way you want:

Format([DateField],"mm dd yy")

or quarter

Format([DateField], "q")

 

[kplatt]

But can you get the start date and end date of a quarter? Can you show me how it would look in the text box with the formating mm dd yy. Sorry for so many questions but this is a great help.

 

[boblarson]

But can you get the start date and end date of a quarter? Can you show me how it would look in the text box with the formating mm dd yy. Sorry for so many questions but this is a great help.

Got a few meetings so it may be a while.

 

[boblarson]

I couldn't figure out how to do this without a couple of custom functions (put these in a STANDARD module and name the module something like modDateFunctions):

Function ReturnQStartDate(intQuarter As Integer, intYear As Integer) As Date
    Select Case intQuarter
    Case 1
        ReturnQStartDate = DateSerial(intYear, 1, 1)
    Case 2
        ReturnQStartDate = DateSerial(intYear, 4, 1)
    Case 3
        ReturnQStartDate = DateSerial(intYear, 7, 1)
    Case 4
        ReturnQStartDate = DateSerial(intYear, 10, 1)
    End Select
End Function

Function ReturnQEndDate(intQuarter As Integer, intYear As Integer) As Date
    Select Case intQuarter
    Case 1
        ReturnQEndDate = DateSerial(intYear, 3, 31)
    Case 2
        ReturnQEndDate = DateSerial(intYear, 6, 30)
    Case 3
        ReturnQEndDate = DateSerial(intYear, 9, 30)
    Case 4
        ReturnQEndDate = DateSerial(intYear, 12, 31)
    End Select
End Function

then you can get the quarter start date by passing:

Me.txtStartDate = ReturnQStartDate(2,Year())

And end of quarter

Me.txtEndDate = ReturnQEndDate(2, Year())

and if you want controls to be able to select (like in a query):

Criteria:

Between ReturnQStartDate([Forms]![YourFormName]![YourQuarter],[Forms]![YourFormName]![YourYearControl]) And ReturnQEndDate([Forms]![YourFormName]![YourQuarter],[Forms]![YourFormName]![YourYearControl])

I hope that helps.

 

[kplatt]

Bob, this gets me the st and end date for a month when i put it in a report cell...=DateSerial(Year([SDate]),Month([SDate]),1)

Can I do the same with quarter in the name to get the st and end dates for a quarter that the SDate lands in?

 

[boblarson]

Bob, this gets me the st and end date for a month when i put it in a report cell...=DateSerial(Year([SDate]),Month([SDate]),1)

Can I do the same with quarter in the name to get the st and end dates for a quarter that the SDate lands in?

Did you not read my last post? I just gave you two functions which you need to put into a Standard Module and then call according to my instructions which will give you the start date for a quarter and then the end date for a quarter.

 

[kplatt]

Bob, can you tell me how to make this happen on a report. I put all the syntax tent that you gave me and put it into a module, but I do not know how to pull the module information into a report. I woul assume using a macro, but I basically do not know what to put into the report cell to make the mth end and start date show.

Thanks