Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Won't accept dates correctly (XP, 2000)

    I have some tables with date fields formatted as MM/YY. I have forms for these tables and the date fields have a MM/YY mask (format?). Problem is, when I enter a date such as 02/03, it automatically changes to 02/04. Any idea why? or what I need to change to prevent this? It seems to only happen when the year I enter is 01, 02, or 03.

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

    Re: Won't accept dates correctly (XP, 2000)

    You can't enter dates in MM/YY format. Access, or rather Windows, interprets 02/03 as the 3rd of February in the current year (assuming US system settings), so the actual date stored is 02/03/2004, which is displayed as 02/04. Either enter Feb-03, or 02/01/03.
    If this is unacceptable, you would need to use an unbound text box, convert the text entered by the user to a date in the After Update event, and store it in the appropriate field.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Thanks Hans. I will change my date fields to be MM/DD/YY. However, just out of curiosity, how would I do what you alternatively suggested? I know how to make an unbound text box but how would I convert that to a date, and more importantly, how would I place that value into a field? I am thinking to simply use the after update event and say date=textbox (or something like that). Is going in the right direction?

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

    Re: Won't accept dates correctly (XP, 2000)

    Say that the text box is named txtDateEntry and the field is named DateField

    Private Sub txtDateEntry_AfterUpdate()
    Dim strDate As String
    strDate = Left(Me.txtDateEntry, 2) & "/01" & Mid(txtDateEntry, 3)
    If IsDate(strDate) Then
    DateField = DateValue(strDate)
    Else
    MsgBox "There is something wrong, dude."
    End If
    End Sub

    You could add more error checking than just IsDate.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Okay, I need some more help... I can't figure out how to do it. I need for the user to be able to enter a date in any of the following formats:
    MM/DD/YY or MM/YY or MM/DD/YYYY and I would like for MS Access to recognize the format and store the date accordingly... even if it means storing 10/01/02 when you enter 10/02. In other words, MS Access should recognize this as MM/YY.

    I can't figure out how to write code that will examine the text entry, parse it correctly, then enter the appropriate date in a date field. Thanks in advance.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    The following code suppose you have a field in the table TestDate and it is on the form, eventually invisible.
    The ubound textbox Text6 is used to enter the date.<pre>Private Sub Text6_BeforeUpdate(Cancel As Integer)
    Select Case Len(Me.Text6)
    Case 5
    Me.TestDate = DateSerial(Right(Me.Text6, 2), Left(Me.Text6, 2), 1)
    Case 8
    Me.TestDate = DateSerial(Right(Me.Text6, 2), Left(Me.Text6, 2), Mid(Me.Text6, 4, 2))
    Case 10
    Me.TestDate = DateSerial(Right(Me.Text6, 4), Left(Me.Text6, 2), Mid(Me.Text6, 4, 2))
    Case Else
    MsgBox "There is a problem with this date !"
    Cancel = True
    End Select
    End Sub</pre>

    Francois

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Thank you - I am getting much closer to my solution. As I was working with my dates, I realized that I had not considered all the variations I might expect so I am trying to modify the code to recognize any combination of M,MM,D,DD,Y,YY,YYYY.

    Does MS Access offer a function like the FIND function so that I can determine the position of "/" in a string? I want to look at the text entered and determine if it is any combination of the following:
    M/Y
    M/D/Y
    MM/YY
    M/D/YY
    M/DD/Y
    M/YYYY
    MM/D/Y
    M/DD/YY
    MM/D/YY
    MM/DD/Y
    MM/YYYY
    M/D/YYYY
    MM/DD/YY
    M/DD/YYYY
    MM/D/YYYY
    MM/DD/YYYY

    Then, apply the correct date. In MS Excel, I can use FIND and SEARCH to locate specific characters in a string but how do I do that in MS Access?

    If MS Access has such a function then I could (in the case of MM/D/YY) use
    LEN (TextField) = 7 AND MID (TextField,3) = "/" AND MID (TextField,5) ="/" to identify the date format used and use
    Me.DateField= DateSerial(Right(Me.TextField, 2), Left(Me.TextField, 2), Mid(Me.TextField, 4,1)) to store the correct date.

    Am I correct in my thinking?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    INSTR is the function you want:

    <pre>Instr("MY","/") Returns 0
    Instr("M/Y"),"/") Returns 2
    Instr("M/D/Y") Returns 2
    </pre>


    etc.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    If it was my program, I would just use an input mask on the field and train my users to enter the date like it have to.
    Don't forget that you also have to test if there numbers are correct. I mean months between 1 and 12, days between 1 and 28, 30, 31 according the month (don't forget 29 for the leap years) ...
    A lot of work for you, a little for the user to learn to type a date as should.
    Only my opinion.
    Francois

  10. #10
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Good point. However, doesn't the ISDATE function verify that automatically if I use it?

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Yes, but you can only use the isdate after you have analyze the string they entered.
    Francois

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Another problem with isdate :
    If someone enter a date as 13/01/2004 and you transform the string with the date serial function you'll ended with a date 01/01/2005. That's a valid date but not what you want I suppose.
    Write a program isn't difficult. An idiot-proof program, that an other matter of thing.
    Francois

  13. #13
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    Okay, I agree about idiot proof. For the sake of learning... how would I test the value of MM to be sure it is 1 - 12?

    Also, just to be sure I understand correctly - ISDATE only checks to see if the value can be converted to a date... ISDATE does not check if it is a legitimate date. In other words, I could enter 1/35/04 and ISDATE would give TRUE but the converted date would be 2/4/04. Is this right?

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Won't accept dates correctly (XP, 2000)

    It is not the isdate that is a problem, it is the dateserial.
    A string 1/35/04 with the formula I give you in the previous post will convert to 02/04/04. An Isdate will give you a true date. But what was the intention of the user when he enter 1/35/04 ? 1/05/04, 1/15/04, 1/25/04
    You could now say, ok I use Isdate on the 1/35/04, this will give an error. OK. But if another user enter 01/04 and you use Isdate on that then it will also give an error. So you have to transform first, and use isdate but in the case of 1/35/04 that give the result of 02/04/04, it will not give an error.
    You could eventually check if and the month an the day is valid before you transform, but it will be a lot of programming probably different for each possible format.

    To test a MM, use
    <pre>If Left(Me.text6,2) < 1 Or Left(Me.Text6,2) > 12 Then
    MsgBox "I'm a stupid PC and I know only 12 months. Can you correct it please?"
    End If</pre>

    Francois

Posting Permissions

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