Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    North Carolina, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems with custom date formats (Access 2000/9.0.3821 SR-1)

    I am attempting to make a custom date format in one of my tables. I have set the type to Date/Time and then set the format to "ddhhnn mmm yy", eg. 201041 NOV 02 = 20 Nov 02 at 1041am. I have also set the input mask to "000000 >LLL 00;0;_". The format and input mask saves fine, but as soon as I attempt to add a date and time it will not let me leave the field. I get the following error;

    The value you entered isn't valid for this field. (For example, you may have entered text in a numeric field or a number that is larger that the FieldSize setting permits.)

    Any ideas out there?

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

    Re: Problems with custom date formats (Access 2000/9.0.3821 SR-1)

    You can't input a date in this form - Access (or rather Windows) won't recognize it as a valid date/time. One solution is to remove the input mask and enter the date and time in one of the traditional formats - as soon as you leave the text box, it will be displayed according to the custom format. Another solution is to use two text boxes - one for entering the date and time, and one for displaying it. You can set an input mask on the first one, and you must use code in the AfterUpdate event to parse the text and convert it to something Windows understands, then display it according to the custom format in the second text box.

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

    Re: Problems with custom date formats (Access 2000/9.0.3821 SR-1)

    The code could look like this - substitute your own control names for txtInput and txtDisplay.

    Private Sub txtInput_AfterUpdate()
    Dim sMinute As String
    Dim sHour As String
    Dim sDay As String
    Dim sMonth As String
    Dim sYear As String
    If Not IsNull([txtInput]) Then
    sMinute = Mid([txtInput], 5, 2)
    sHour = Mid([txtInput], 3, 2)
    sDay = Left([txtInput], 2)
    sMonth = Mid([txtInput], 8, 3)
    sYear = Right([txtInput], 2)
    txtDisplay = DateValue(sDay & " " & sMonth & " " & sYear) + TimeSerial(sHour, sMinute, 0)
    Else
    txtDisplay = Null
    End If
    End Sub

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Location
    North Carolina, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with custom date formats (Access 2000/9.0.3821 SR-1)

    Thanks for the input.

    Any ideas on how I could code the input mask and format to make it work? I'm thinking that I shouldn't have to use two (2) fields.

    I've been out of date on coding for a while now.

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Location
    North Carolina, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with custom date formats (Access 2000/9.0.3821 SR-1)

    I'm not sure if that code is going to do what I need. Maybe this will explain it better.

    My users need to be able to enter the date and time as shown above. The date and time will also need to be displayed the same way. I understand from what you told me, Access will not understand that format. So, how can I write some code that allows my users to enter the date and time the way we need and display it the same way, BUT have the computer understand it as a Date and Time?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Problems with custom date formats (Access 2000/9.0.3821 SR-1)

    You can use your existing input mask, but attach it to an inbound text box.
    Then use the afterupdate event to convert the text entered into a date, and put the value into a hidden control bound to the date/time field in the table.
    Here is some code that works for me:
    <pre>Dim newdate As Date
    Dim datetext As String
    datetext = (Left([DateEntered], 2) & "/" & Mid([DateEntered], 8, 3) & "/" & Right([DateEntered], 2))
    'MsgBox (datetext)
    newdate = DateValue(datetext)

    newdate = newdate + TimeSerial(Mid([DateEntered], 3, 2), Mid([DateEntered], 5, 2), 0)

    Me!mydate = newdate
    </pre>

    Regards
    John



Posting Permissions

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