Help with DateSerial Criteria (1 Viewer)

HeelNGville

Registered User.
Local time
Today, 08:27
Joined
Apr 13, 2004
Messages
71
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

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

Thanks in advance!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:27
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 08:27
Joined
Apr 13, 2004
Messages
71
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

Super Moderator
Staff member
Local time
Tomorrow, 00:27
Joined
Jan 20, 2009
Messages
12,849
So first day of previous month is
FirstDayOfPerviousMonth = DateSerial(year(lastDayOfPreviousMonth),month(lastdayOfPreviousMonth),1)

Or simply:
Code:
 FirstDayOfPreviousMonth = DateSerial(Year(somedate), Month(somedate) - 1, 1)
 

HeelNGville

Registered User.
Local time
Today, 08:27
Joined
Apr 13, 2004
Messages
71
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’m here to help
Staff member
Local time
Today, 06:27
Joined
Oct 29, 2018
Messages
21,357
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.
Code:
If Day(Date())=1 Then
    Me.txtClick1=DateAdd(...)
Else
    Me.txtClick1=DateSerial(...)
End If
Hope that helps...
 

HeelNGville

Registered User.
Local time
Today, 08:27
Joined
Apr 13, 2004
Messages
71
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()

Code:
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

The Mailman - AWF VIP
Local time
Today, 14:27
Joined
Aug 11, 2003
Messages
11,696
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.
 

Users who are viewing this thread

Top Bottom