Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Verify Date = First or Last day of month

Posted on 2011-03-15
13
Medium Priority
?
684 Views
Last Modified: 2013-11-28
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?
0
Comment
Question by:PSIUnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
13 Comments
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 35144005
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
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35144029
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.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 35144717
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

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 40

Expert Comment

by:als315
ID: 35145002
You can ask user enter only month and year and generate first and last day with proposed functions
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35145833
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

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35145853
And here's one I made earlier!  :-)
Q26887751.mdb
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 35146118
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
0
 

Author Comment

by:PSIUnit
ID: 35146462
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 ...
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 35146761
@cactus_data   had never thought of using day=0 in a dateserial to get the last day of prev month but certainly works.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35146792
It does. DateSerial is a great function.

/gustav
0
 

Author Closing Comment

by:PSIUnit
ID: 35148145
That was Great! Thank all of you for your help.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35148194
You are welcome!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question