Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook 2003 Custom Forms - VB Script and dates

    Hi,

    I'm tearing my hair out with this, hopefully someone can help.

    I'm using Windows XP with Office 2003. I have a custom form in Outlook with a text box that users enter a date into.

    The following code in the form validates this date to make sure it's not in the future or more than 28 days in the past :

    If Date() - CDate(txtDate) > 28 Then
    MsgBox "More than 28 days in the past"
    Else
    If Date() < CDate(txtDate) Then
    MsgBox "Future Date"
    End If
    End IF

    Date() returns in the format mm/dd/yyyy for all users...

    Users are inputting the date in UK format (dd/mm/yyyy) which for the vast majority of users CDate(txtDate) returns the date in US format (mm/dd/yyyy). This is exactly what I need so that the date comparison in the code will work.

    However, for a small number of users CDate(txtDate) returns the date still in UK format (dd/mm/yyyy), in this case the validation fails depending on the date input.

    I've examined the regional settings on one of the problem machines and all seems to be identical to mine which works fine. Similar procedures written in Excel and Access VBA don't cause the same problems, it seems to be specific to Outlook.

    Any help would be greatly appreciated.

    Thanks.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    One thought would be to convert the text to a date yourself so you are not dependent on the vagaries of the system. Maybe something along these lines:

    Code:
    Dim arrDate
    arrDate = Split(txtDate, "/")
    If UBound(arrDate) = 2 Then
        Dim intM, intD, intY, dtUser, dtToday
        intM = CInt(arrDate(1))
        intD = CInt(arrDate(0))
        intY = CInt(arrDate(2))
        If intM < 1 Or intM > 12 Or intD < 1 Or intD > 31 Or intY < 2010 Or intY > 2999 Then
            MsgBox "Malformed date (try dd/mm/yyyy)"
        Else
            dtUser = DateSerial(CInt(strY), CInt(strM), CInt(strD))
            dtToday = DateSerial(Year(Date), Month(Date), Day(Date))
            If dtToday - dtUser > 28 Then
                MsgBox "More than 28 days in the past"
            ElseIf dtToday < dtUser Then
                MsgBox "Future Date"
            Else
                'Cool
            End If
        End If
    Else
        MsgBox "Malformed date (try dd/mm/yyyy)"
    End If
    There are likely to be problems if someone enters, for example, 31/02/2011, but I'm not sure it's worth coding for that...

  3. #3
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for this, it does exactly what I needed.

Posting Permissions

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