Results 1 to 9 of 9

Thread: DATE (A2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DATE (A2K)

    Afternoon Everyone!!

    How can I do the following in 1 simple step?

    Current DATE field appears like this:
    20070720

    I want the DATE field to appear like this:
    07/20/2007
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: DATE (A2K)

    Is it (a) a text field or ([img]/forums/images/smilies/cool.gif[/img] a number field?
    If you convert the value, the field will still be a text or number field, not a Date/Time field, so you cannot use it directly for date comparisons and calculations.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATE (A2K)

    This is a DOB field and when the data comes via ftp from the government, it comes in as a text field and appears 20072007. Since it is the DOB for the member it does appear on the form. This helps the user verify they have selected the correct member. Because of it's appearance, others have problems reading it. So I want to have it appear as a DATE and in the correct order.

    It won't need it for any comparisons/calculations as we use the members ID or MedcaidID not the DOB.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: DATE (A2K)

    If you keep it as a text field and change 20070720 to 07/20/2007, it will liik like a date, but it will not be a date.
    The advantage of the 20070720 format is that you can sort the dates easily. If you try to sort values such as 07/20/2007 in a text field, the result will be nonsense.
    But if you still want to go ahead, you can create a query based on the table.
    Add the DOB field to the query grid.
    Set the Criteria for this field to Is Not Null
    Select Query | Update Query to change the query to an update query.
    Enter the following expression in the Update to line, replacing DOB with the actual name of the field:
    <code>
    Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4)
    </code>
    Select Query | Run or click the Run button on the toolbar.
    Warnings:
    1) Make sure that the "DOB" field in the table has length at least 10.
    2) Test the query on a copy of the table first.
    3) You can run the query only once - if you run it again when the values have already been changed, you'll get meaningless results.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATE (A2K)

    Thanks Hans,

    FYI: This is government data and can't be modified. Basically I'm making a psuedo Date field (modDOB) in the table. I'm, only updating the modDOB to show the (DOB) in [DATE] view for the form.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: DATE (A2K)

    In that case, I would make the modDOB field a real date/time field, and set it to

    DateValue(Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4))

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DATE (A2K)

    Again thanks Hans, works beautifully.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: DATE (A2K)

    Could you also use the DateSerial function?

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

    Re: DATE (A2K)

    Of course:

    DateSerial(Left([DOB],4),Mid([DOB],5,2),Right([DOB],2))

    This has the advantage of being independent of regional settings. The DateSerial expression assumed US date format.

Posting Permissions

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