Verify Date = First or Last day of month

I have 2 Formatted Short Date Fields. Start Date and End Date. The Users can pick any Year or any Month but the Start Day must be the first day of the Month and the End Day must be the Last day of the Month.

And the End Date has to be after the start date.  (I can handle this)

The Tables are SQL but the Form that the Users edit is Access. I was thinking of adding code to the Close button to verify this data before allowing them to Close. If it is not correct I will display an error message. I can handle the error message but how do I do the validations for what day of the month they entered?
PSIUnitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
Do you know how to use .NET in Access?  If yes, this function will do exactly what you need:
http://msdn.microsoft.com/en-us/library/system.datetime.daysinmonth.aspx

Or you can do the following in VBA

Function dhDaysInMonth(Optional dtmDate As Date = 0) As Integer
    ' Return the number of days in the specified month.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use the current date.
        dtmDate = Date
    End If
    dhDaysInMonth = DateSerial(Year(dtmDate), _
     Month(dtmDate) + 1, 1) - _
     DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function
aikimarkCommented:
A better design would be to have the user select a starting year and month and end year and month.  Then your front-end calculates the day-of-month for starting and ending dates.
Robberbaron (robr)Commented:
I have attached 2 functions that return the start and end dates of the selected month.

so you could either just use those as the data posted to SQLS, or do a check in the front end to see if the input matches the required date and throw an alert if not.
Function MonthStart(datein As Date) As Date

    MonthStart = DateSerial(Year(datein), Month(datein), 1)
    
End Function

Function MonthEnd(datein As Date) As Date

    MonthEnd = DateSerial(Year(datein), Month(datein) + 1, 1) - 1
    
End Function

Sub button_Close()
    Dim erx As Boolean
    erx = False
    If datepicker1.Date <> MonthStart(datepicker1.Date) Then
        MsgBox "Start date needs to be first of the month"
        erx = True
    End If
    If datepicker2.Date <> MonthEnd(datepicker2.Date) Then
        MsgBox "End date must be the last day of the month"
        erx = True
    End If
    
    If erx = False Then
        'process the date
     Else
       'return to entry for,
    End If
End Sub

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

als315Commented:
You can ask user enter only month and year and generate first and last day with proposed functions
JezWaltersCommented:
If you want to validate the start/end dates in your form, then it's probably best to use the Before Update event handlers of the corresponding controls, like this:
Option Explicit
Option Compare Database

Private Sub txtEndDate_BeforeUpdate(pintCancel As Integer)
    If DatePart("d", txtEndDate + 1) <> 1 Then
        MsgBox "End date must be last day of the month"
        pintCancel = True
    End If
End Sub

Private Sub txtStartDate_BeforeUpdate(pintCancel As Integer)
    If DatePart("d", txtStartDate) <> 1 Then
        MsgBox "Start date must be first day of the month"
        pintCancel = True
    End If
End Sub

Open in new window

JezWaltersCommented:
And here's one I made earlier!  :-)
Q26887751.mdb
Gustav BrockCIOCommented:
Let the user pick any date he/she wants, adjust the date on the fly, and skip the messagebox - users hate stupid applications:

Private Sub txtDateStart_AfterUpdate()
  Dim datDate As Date
  datDate = txtDateStart.Value
  txtDateStart.Value = DateSerial(Year(datDate), Month(datDate), 1)
End Sub

Private Sub txtDateEnd_AfterUpdate()
  Dim datDate As Date
  datDate = txtDateEnd.Value
  txtDateEnd.Value = DateSerial(Year(datDate), Month(datDate) + 1, 0)
End Sub

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PSIUnitAuthor Commented:
WOW!!! That is a whole Lot of Information! Thank you! Let me sift thru it today and figure out which ones work best for this.. I had 2 separate fields originally for the date but the users did not like it that way they want to enter a date even though they have no control over the day ..(go figure).. so i went with them enterring the date and me fixing it... i will let you know how it works out.. Thank you very much ...
Robberbaron (robr)Commented:
@cactus_data   had never thought of using day=0 in a dateserial to get the last day of prev month but certainly works.
Gustav BrockCIOCommented:
It does. DateSerial is a great function.

/gustav
PSIUnitAuthor Commented:
That was Great! Thank all of you for your help.
Gustav BrockCIOCommented:
You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.