Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting a date from text (2000)

    A text field can include a date as part of the text. What formula could I use to extract the date? It could occur anywhere within the text and in any date format. Please note that I need to do this without VBA as much as possible. Thanks, Andy.

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

    Re: Extracting a date from text (2000)

    That will be tough. Could you provide some examples of what to expect?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting a date from text (2000)

    Actually, I'm aware that it would be extremely difficult without VBA, depending on the date format.

    What about a VBA function solution if the date is contained in something like this: 'Dated:25 Oct 2004 and each week'. So it can start with text and the date is always in this format. Andy.

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

    Re: Extracting a date from text (2000)

    I have attached a text file with a VBA function ExtractDate (sorry, don't know how to do this without VBA). You can copy the code to a standard module. You can use the function in a query:

    DateField: ExtractDate([TextFieldWithDate])

    and in the control source of a text box on a form or report:

    =ExtractDate([TextFieldWithDate])

    where TextFieldWithDate is the name of the text field.

    Note: the date must consist of a number, followed by some text, then a number again, and result in a valid date according to your system settings. Examples of valid strings:

    Ordered on 24-Apr-04
    From 31 December 1999 onwards
    3 July 98 and before

    Examples of invalid strings:

    Ordered on Apr 24
    From 31/12/1999 onwards
    Jul 3, 1998 and before

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting a date from text (2000)

    Hi Hans

    I tried the function as follows and received error message

    ?ExtractDate("Dated:25 Oct 2004")

    Compile error - Expected variable or procedure, not project

    What am I missing?

    Thabks, John

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

    Re: Extracting a date from text (2000)

    Did you name the module or database ExtractDate as well? That would confuse VBA.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting a date from text (2000)

    I see says the blind man!

    db name was ExtracttDate.mdb

    When I changed it to ExtractDateFromText.mdb, same error occured

    I had to delete db and recreate as ExtractDateFromText.mdb for it to work.

    Thanks, John

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

    Re: Extracting a date from text (2000)

    When you create a new database, the file name (sans .mdb) is used as the VBA Project name. Renaming the database later on won't change the project name. You can see and modify the project name by selecting Tools | <project name> Properties in the Visual Basic Editor.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting a date from text (2000)

    Thank you

Posting Permissions

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