Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    23

    Unanswered: memo field - manipulating data

    I have a memo field from which I would like to extract a date. Sample text is:
    sent on 02032007 from US

    I want to create a parameter query, the date entered by the user should be used to filter the records. In other words, that date should be used to match against the date used in the manipulation above.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    All I can suggest is that you use a combimation of
    Mid()
    Left()
    Right()
    Functions to chop the string up and convert into a readable format (with /'s)
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Memo fields, as their name suggests, are intended to hold memos/narrative type data. They should never hold data that will need to be manipulated, as this can cause a number of problems, primary of which is truncation of the field.

    From a reference field I have named MemoFieldAngst:

    Crosstab queries, Summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates.

    Also, if you have specified a format in the field's Format property, this will often truncate the data to 255 characters as well.

    If Unique Value Property is set to Yes, Access has to compare the values and once again Memo Field values are truncated.
    HAving said that, trying to cull data out of a memo field is always very problematic. In this case you don't actually have a date in the memo field, but rather a text representation of a date. Georgev's suggestions may be of use, and you might also think about using Instr to identify memo fields that have your "date" in them. Some other questions to think about; are there other numeric characters in the memo filed (like quantities, etc) and do the "dates" always appear in the exact format you quoted (02032007) or will it sometimes appear as 02/02/2007 or 2/3/07?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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