Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: Is there a way to retrieve data from FOX PRO with a MEMO field?

    I am trying to extract data or link to a FOX PRO table to use the MEMO data field but unable too. The column just goes blank after importing or linking to the table. So, is there a way to retrieve the data from FOX PRO that is a MEMO field?

    Thanks,

    Don

  2. #2
    Join Date
    Aug 2002
    Posts
    33
    Create an ODBC connection using File DSN to the Fox Pro Database.
    In Microsoft Excel, Goto Data, Get External Data, New Database Query.
    Select the Datasource, then ok, then pick the fields you want to import into the spreadsheet.

    You can then import the spreadsheet into an access table. As far as reading it on the fly, not sure of a way.

  3. #3
    Join Date
    Apr 2003
    Posts
    280

    How to retrieve certain information in a memo field?

    There is a BUYER: and a PURCHASER:, which is use to set off the names in the memo field. Both is in the same memo field and on their on own line in the memo field (when user click the enter key on the keyboard). Now I have to extract it and split it up into different field names for a new database.

    Is there a way to read a specific line in the memo field? I have use the INSTR function and hoping it would work but it didn't. Maybe there is another way to do it using the INSTR function, I don't know.

    Thanks,

    Don

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Does the data always appear the same way within the memo field? Does it always say:

    BUYER: Buyer name, whatever

    PURCHASER: Purchaser name, whatever

    If it does and the words Buyer and Purchaser always preceeds it, you can extract the info for each.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    Sometimes, Purchaser would display before Buyer or just Purchaser shown in the memo field but 95% of the time there should be Buyer and Purchaser in front of the other words.

    How would I extract the data proceeding those words?

  6. #6
    Join Date
    Apr 2003
    Posts
    280
    Ok, I got it to work now. I was using the wrong ODBC driver for the dbf file(fox pro table). I can access this table straight from access on the fly. Thanks.

  7. #7
    Join Date
    Apr 2003
    Posts
    280
    Quote Originally Posted by lansing
    Ok, I got it to work now. I was using the wrong ODBC driver for the dbf file(fox pro table). I can access this table straight from access on the fly. Thanks.
    Oops, I post this on the wrong forum..

  8. #8
    Join Date
    Apr 2003
    Posts
    280
    Quote Originally Posted by Missinglinq
    Does the data always appear the same way within the memo field? Does it always say:

    BUYER: Buyer name, whatever

    PURCHASER: Purchaser name, whatever

    If it does and the words Buyer and Purchaser always preceeds it, you can extract the info for each.
    How would you going about doing this? I would to learn how you do this.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As I said, this is based on the assumption that the data always appear the same way within the memo field, i.e.

    BUYER: Buyer name, whatever

    PURCHASER: Purchaser name, whatever

    Code:

    PurchaseStart = InStr(OriginalMemoBox, "Purchaser:") - 1
    MemoLen = Len(Trim$(OriginalMemoBox))

    Purchaser.Value = right$(OriginalMemoBox, MemoLen - PurchaseStart)

    Buyer.Value = left$(OriginalMemoBox, PurchaseStart)

    If the OriginalMemoBox entry is :

    BUYER: Buyer name, yada, yada!
    PURCHASER: Purchaser name, yada, yada!

    this code will yield:

    Purchaser.Value = BUYER: Buyer name, yada, yada!

    Buyer.Value = PURCHASER: Purchaser name, yada, yada!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Apr 2003
    Posts
    280
    Thanks very much for your help. I also modified the code a bit to check if there is the word PURCHASER or BUYER in the memo before before running any other code.

Posting Permissions

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