none
Data Extract RRS feed

  • Question

  • Hello,

    I was wondering if someone can help me how to extract certain data from a field thru a query?  I'm trying to extract the amount and Draft Date.  Please see example below:

    ONE-TIME DRAFT   ACTIVE. AMOUNT$ 1407.61 DRAFT DATE 04/21/16 Bk Acct # XXXXX1111 ABA#   XXXXXX111, Confirmation# 00500227060006

    ONE-TIME DRAFT ACTIVE. AMOUNT$ 518.90   DRAFT DATE 04/21/16 Bk Acct # XXX2222 ABA# XXXXXX222, Confirmation#   00500597400003
    Friday, April 22, 2016 5:59 PM

Answers

  • a couple of ways:

    if this data is from a csv file - in your link or import set up you can define the field dividers - and in doing so isolate the fields you seek into their own fields.

    these dividers are part of the launched wizard when you go to link/import a csv file - although it is somewhat obscure and one must follow the wizard closely to realize what to do

    another scenario is if these long strings already exist in a table as a single field...then the question is whether or not their character count is always exactly the same; if so they in a query you can create a calculated field that picks out just the characters you seek.  Check out info for Right, Left, Mid string functions

    Friday, April 22, 2016 8:56 PM
  • Hi,JoeJoeJoe

    According to your description, I suggest that you could use expressions with String function to extract portion of a text string.

    For more information, click here to refer about Sample expressions to extract portion of a text string in Access

    and here to refer about Access Functions (by category)

    Monday, April 25, 2016 8:51 AM

All replies

  • a couple of ways:

    if this data is from a csv file - in your link or import set up you can define the field dividers - and in doing so isolate the fields you seek into their own fields.

    these dividers are part of the launched wizard when you go to link/import a csv file - although it is somewhat obscure and one must follow the wizard closely to realize what to do

    another scenario is if these long strings already exist in a table as a single field...then the question is whether or not their character count is always exactly the same; if so they in a query you can create a calculated field that picks out just the characters you seek.  Check out info for Right, Left, Mid string functions

    Friday, April 22, 2016 8:56 PM
  • You can use an regular expression to get the value. Google for Regular Express.
    Saturday, April 23, 2016 1:54 AM
  • Hi,JoeJoeJoe

    According to your description, I suggest that you could use expressions with String function to extract portion of a text string.

    For more information, click here to refer about Sample expressions to extract portion of a text string in Access

    and here to refer about Access Functions (by category)

    Monday, April 25, 2016 8:51 AM