Help with DateSerial Criteria

[HeelNGville]

Preface this with thanks for any assistance in resolving or guidance, as I am sure this is simple but my VBA is limited at best.

Basically, I have a DB form with a calendar date picker for a user to select a beginning date (txtClick1) and ending date (txtClick2), with the on current properties set to default to the 1st of the month for the beginning date and the ending date set to default to current date -1 (this value is always ok) for MTD reporting purposes.

What I need assistance with is, when Day =1 (first of each month), I would like for the txtClick1 value to default to the 1st day of the previous month. Ex. On 4/1/2020, a user opens the DB and forms loads with txtClick 1 as 3/1/2020 and txtClick2 would be 3/31/2020 . On 4/2, the txtClick1 value would be 4/1/2020. Only on day 1 would the code need to revert to previous month.

Below is the quick snippet of the

Private Sub Form_Current()
Me.txtClick1.Value = DateSerial(Year(Date), Month(Date), 1)
Me.txtClick2.Value = Date - 1
End Sub

Thanks in advance!

 

[MajP]

first day of previous month is
lastDayOfPreviousMonth = dateSerial(year(someDate),month(someDate),0)
So first day of previous month is
FirstDayOfPerviousMonth = DateSerial(year(lastDayOfPreviousMonth),month(lastdayOfPreviousMonth),1)

 

[HeelNGville]

Thanks for the reply. I get that. However, how do I get that value to default as my date picker .txtClick1 (beginning date) when calendar day of month number = 1.

Basically below gives me the 1st day of the previous month, however only need that value to appear when the DAY value = 1

So if DAY is 1 then: DateAdd("m", -1, DateSerial(Year(Date), Month(Date), 1))
otherwise: DateSerial(Year(Date), Month(Date), 1)

Just having trouble piecing this information together in VBA. Apologies, VBA not really my area, but trying to assist. Thanks again.

 

[Galaxiom]

So first day of previous month is
FirstDayOfPerviousMonth = DateSerial(year(lastDayOfPreviousMonth),month(lastdayOfPreviousMonth),1)

Or simply:

 FirstDayOfPreviousMonth = DateSerial(Year(somedate), Month(somedate) - 1, 1)

 

[HeelNGville]

I understand how to retrieve the first day of the current month and previous month. The challenge I am having is, when the calendar date is on the 1st of each month (i.e. 4/1/2020), I need to revert to the first day of the previous month.

So, on my form, I have a date picker calendar, with the beginning date control named: txtClick1

On my form current

Me.txtClick1.Value = DateSerial(Year(Date), Month(Date), 1) 'which gives me the first day of the current month, which is what I need unless the day of the month = 01.

If 1st of month, then I know that DateAdd("m", -1, DateSerial(Year(Date), Month(Date), 1)) will provide me with the 1st day of the previous month

I am simply not certain how to string these 2 together.

So something like:

Me.txtClick1.Value = if date(day)=01 then 1st day of previous month else 1st day of current month

Hopefully that helps. Thanks again.

 

[theDBguy]

I understand how to retrieve the first day of the current month and previous month. The challenge I am having is, when the calendar date is on the 1st of each month (i.e. 4/1/2020), I need to revert to the first day of the previous month.

So, on my form, I have a date picker calendar, with the beginning date control named: txtClick1

On my form current

Me.txtClick1.Value = DateSerial(Year(Date), Month(Date), 1) 'which gives me the first day of the current month, which is what I need unless the day of the month = 01.

If 1st of month, then I know that DateAdd("m", -1, DateSerial(Year(Date), Month(Date), 1)) will provide me with the 1st day of the previous month

I am simply not certain how to string these 2 together.

So something like:

Me.txtClick1.Value = if date(day)=01 then 1st day of previous month else 1st day of current month

Hopefully that helps. Thanks again.

Hi. You almost got it.

If Day(Date())=1 Then
    Me.txtClick1=DateAdd(...)
Else
    Me.txtClick1=DateSerial(...)
End If

Hope that helps...

 

[HeelNGville]

Thanks DBGuy! Based on your suggestion, I put together the following, which appears to be working. Would you confirm that I have correct syntax here?

Private Sub Form_Current()

If Day(Date) = 1 Then
    Me.txtClick1 = DateAdd("m", -1, DateSerial(Year(Date), Month(Date), 1))  'if 1st of month, default begin date is 1st of previous month
Else
    Me.txtClick1 = DateSerial(Year(Date), Month(Date), 1)    'anything else would default to a begin date of current month
End If
     Me.txtClick2.Value = Date - 1 'end date will always be current date -1
 
End Sub

Again, thanks so much for the feedback and assistance!

 

[namliam]

txtClick1 ? not a very obvious object name for a date picker

Strongly suggest you use legable, usable names instead of these generic names, will help your maintenance down the line.

 

[HeelNGville]

txtClick1 ? not a very obvious object name for a date picker

Thanks, that is not the actual named value, just an alias used in this post. Appreciate the suggestion though.