Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Texas, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last Day of Month Data Validation on Form (2000,SR1)

    I've searched through the archives, but can't find an answer to my problem.

    On several data entry forms, I need to make sure that the date entered in one of the fields is either the 15th or the last day of any month. Validating for the 15th is easy, but I can't figure out how to validate the last day of the month. Hopefully someone can give me some advice on this.

    Thanks,
    Elaine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    To get the last day of a certain month in a certain year, use

    DateSerial(year, month + 1, 0)

    If the date entered by the user is in a text box named txtDate,

    DateSerial(Year([txtDate]), Month([txtDate]) + 1, 0)

    will return the last day of the month txtDate is in. You could check if this is equal to txtDate.

    If you want to know the number of the last day of the month in txtDate, use

    Day(DateSerial(Year([txtDate]), Month([txtDate]) + 1, 0))

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    The usual trick here is to calculate the zeroth day of the next month, which gives the last day of the current month. I typically use the DateSerial function in conjunction with the DateAdd function to do this. If you need an exact expression, do a search on "Last Day of Month" - you will also find a couple of other methods. (Looks like Hans gave you one solutions while I was typing.)
    Wendell

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Texas, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    Thanks, Hans and Wendell for replying. I guess I should have stressed that I'm not looking for the last day of a GIVEN, specific month, I need it to be the last day of ANY month.

    Let me explain what I'm doing:
    If an employee was absent on Nov. 20th, and he just informed the timekeeper (TK) today, the pay period has already passed for this event. I already have a procedure that would calculate the payroll date as Nov. 30th if the date hasn't passed, or if the date has passed a message that the calculated payroll date has passed and a manual entry has to be made. In this case, the TK would need to enter the payroll date as December 31st in order for it to show up on the next payroll report. I need to prevent the TK from entering an incorrect date.

    I'm thinking that perhaps if I had a table of valid dates, I could use this somehow for validation, but I'm not sure how.

    Thanks,
    Elaine

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    If you mean that it always has to be the last day of the current month, the TK shouldn't be allowed to enter it. You can use the expression form my previous reply with Date as argument:

    =DateSerial(Year(Date()), Month(Date()) + 1, 0)

    will result in the last day of the current month (whatever that month is).

    If you want the TK to be able to enter a date, and slap him/her if it's not the last day in that month, you can check in the BeforeUpdate event of the text box - see the example using a text box txtDate in my previous reply. Something like:

    Private Sub txtDate_BeforeUpdate(Cancel As Integer)
    If CDate(txtDate) <> DateSerial(Year(txtDate), Month(txtDate) + 1, 0) Then
    MsgBox "You Stupid Fool", vbCritical
    Cancel = True
    End If
    End Sub

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Texas, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    Hans, this solution is just what I needed. I added another If statement to include the 15th since the 15th and last day are valid entries.

    Thanks for all your help in solving my problem!
    Elaine

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    Hans,
    Is that msgbox error message your way of getting back at the users?
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    The I had a terrible time trying to find the last day of the month, until someone explain to me a very simple way of finding it!

    I was told the best way is to find the first day of the next month and then take one off. This will always give you the last day of the preceding month whether it's the 28th or 29th of February or the 30th or 31st of any other month.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Day of Month Data Validation on Form (2000,SR1)

    Just for Christmas; I use another version the rest of the year...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •