Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing / delimited dates (2003 SP2)

    I am needing to take a date field in mm/dd/yyyy format and make it three separate fields. How can I do this in access?

    Thank you.

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

    Re: Parsing / delimited dates (2003 SP2)

    I assume the date field is in a table. Let's say the field is named TheDate.
    Create a query based on the table, add the fields you need and create three calculated fields:

    TheYear: Year([TheDate])

    TheMonth: Month([theDate])

    TheDay: Day([TheDate])

    You can change TheYear etc. to the names you want to use, but I'd avoid naming the calculated fields Year, Month etc. because those are the names of built-in functions. It might confuse Access.
    You can use the query as basis for forms and reports.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing / delimited dates (2003 SP2)

    Hans, thank you for your response. I don't understand the calculation. Not that this is relevant, but my field is entitled Accrual Date. It's downloaded with other data from a table I've imported from my accounting system. I'm needing to join that table (GL_Detail) with another table (GL_Balance). GL_Balance has a field entitled FISCAL YEAR that I need to match with data from the GL_Detail table, so I need to parse the Accrual Date field and rename the year (once I get it separated from the month and the day) field FISCAL YEAR.

    I don't mean to supply too much information here, but what I don't understand about your response is the part about the calculated fields. If I were in Excel (which I can't be because the database is too big), I would use the "text to columns" feature to move slash delimited data into separate fields. I don't see how access can tell, from the example you provided, which part of (TheDate) to bring into each field.

    BTW, these are not calculated fields.

    Thank you, Hans. I apologize for being a total idiot, but I am one of those total idiots who doesn't use access much more than to deal with data that's too big for Excel.

    Rachel

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

    Re: Parsing / delimited dates (2003 SP2)

    Can you open the GL_Detail table in design view and check what the data type of the Accrual Date field is? I'd like to know whether it is a date/time field or a text field. Thanks.

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing / delimited dates (2003 SP2)

    It is a date time field (short date format).

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

    Re: Parsing / delimited dates (2003 SP2)

    A date/time field is actually stored in the database as a number (the number of days since 31 December 1899). The Year function can be used to extract the year from the date, and similar for the month and day.

    What is the definition of the fiscal year (sorry for my ignorance)? It's probably possible to calculate it in one go from the Accrual Date.

  7. #7
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing / delimited dates (2003 SP2)

    Ah, Hans, you are far from the ignorant one here. Fiscal Year is simply the yyyy in mm/dd/yyyy (for example - 2007).

    Rachel

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

    Re: Parsing / delimited dates (2003 SP2)

    Oh, OK. Then it's simple:

    - Create a query in design view.
    - Add the GL_Detail table, then close the Add Table dialog.
    - Add GL_Detail.* from the field list to the query grid.
    - In the second column, enter

    FISCAL YEAR: Year([Accrual Date])

    - Switch to datasheet view to see the new FISCAL YEAR column to the right of the colums from the table, filled with the year belonging to the Accrual Date.
    - Close and save the query as (for example) qryDetail.
    - You can now create a new query in design view based on the qrDetail query and on the GL_BALANCE table.
    - Join the query and table on the FISCAL YEAR field, etc.

  9. #9
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing / delimited dates (2003 SP2)

    Thank you, Hans! As an old boss used to say: "May you receive your reward in your next life."

    Well, I hope it comes sooner than that.

    Rachel

Posting Permissions

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