Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date conversion from text (access 2003)

    I get a file from Monarch into Access where a projection by month gives me the month as AUG-07 for instance. In Excel, I convert that to a monthend by =Eomonth(Datevalue("1-"&B2),0) and get what I need. In Access, is there a similar formula to transform the text? I see the Datevalue functon in Help, but that looks like a VBA expression. Thanks.

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

    Re: Date conversion from text (access 2003)

    You can use DateValue in expressions too, in queries and in the control source of text boxes etc. For example in a query:

    RealDate: DateValue("1-" & [TextDate])

    where TextDate is the name of the field containing "AUG-07".

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date conversion from text (access 2003)

    Thanks, that worked. In Excel, I have this within an EOMONTH function. Is that possible? I now get JUL-07 translated to 7/1/2007, which is "fine" but I can't seem to format it to JUL07 as in Excel, so perhpas I can get 7/31/07 as an alternative.

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

    Re: Date conversion from text (access 2003)

    You should be able to set the format of the column (or text box, depending on how you use it) to mmm yy
    Or do you have a very specific reason for wanting to return the last day of the month?

Posting Permissions

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