Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse Access field to do lookup (Access 2003)

    I have a Master Table in access that has a field for an UPC code. The particular codes I am puttin in represent comic books and identify three things from the following format:

    59606 01772 18011

    The first set of numbers are the company (here, Marvel).
    The second set of numbers identify the Comic Title (X-Men)
    The first three numbers in the last set are the issue # (180).

    I have two other tables listing the various Company codes and Title codes.

    I want to do two things:

    1) Take the third set of numbers in the string and parse it into a field within the same Master Table called "IssueNumber". Here I would have to parse the sting to get the last set and then also only extract the first three digits. How do I parse part of a string to an existing field?

    2) I am going to want to do queries that use the existing UPC string to lookup the text identifiers for Company and Title (like "Marvel"). How do I specify within an access query to take only the first part of the string and bring back the identifier from another table? Can I define a specific critieria?

    Any help is appreciated.

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

    Re: Parse Access field to do lookup (Access 2003)

    Make a backup copy of the database first, just in case things go wrong.

    1. To extract the issue number:
    <UL><LI>Create a query based on the master table.
    <LI>Select Query | Update Query to make it into an update query.
    <LI>Add the IssueNumber field to the query grid.
    <LI>Enter Is Null in the Criteria row.
    <LI>Enter the following in the Update to row:
    <code>
    Val(Mid([UPC],InStrRev([UPC]," ")+1,3))
    </code>
    where UPC is the name of the UPC code field.
    <LI>Save the query so that you can reuse it later.
    <LI>Select Query | Run (or click the Run button on the toolbar)[/list]2. To look up the text descriptions of Company and Title:
    <UL><LI>Create a query based on the Master Table.
    <LI>Add the fields you need (or *) to the query grid.
    <LI>Create a calculated field:
    <code>
    SpacePos: InStr([UPC]," ")
    </code>
    <LI>Add a calculated field:
    <code>
    CompanyID: Val([Left([UPC],[SpacePos]-1))
    </code>
    <LI>Add a calculated field:
    <code>
    TitleID: Val(Mid([UPC],[SpacePos]+1,InStrRev([UPC]," ")-[SpacePos]-1))
    </code>
    <LI>Save this query, say as qryMaster.
    <LI>Create a new query, and add qryMaster, the table with Companies and the table with Titles.
    <LI>Join qryMaster to the Companies table on the CompanyID field.
    <LI>Join qryMaster to the Titles table on the TitleID field.
    <LI>Add qryMaster.* to the query grid, and also the Company name from the Companies table and the Title description from the Titles table.
    <LI>Use this query as record source for forms and reports.[/list]

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Access field to do lookup (Access 2003)

    Thanks- that worked great!

  4. #4
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Access field to do lookup (Access 2003)

    Hans, two follow up questions:

    I decided to just put the Issue Number, Company Name, and Title into the new query as you provided details for below. which I called qryAllData. Then I created the qryMaster to retrieve the text specific details from the numbers.

    However, once I try to link the new query with the existing tables I get Type Mismatches. Because I wasnt going to do calculations on the UPC numbers, I set them as text in the tables. Now with the queries that have the parsed data, it seems to want them to be numbers. Can I set the Data Type for these new query entries to text after they are parsed?

    Two, I noticed the query fields are now dropping any leading zeroes after being parsed, so I suspect i may run into future relationship issues. If I can set the Data Type, will that fix the problem, or is there another step?

    Your assistance has been amazing. I thank you. I attached a copy so you cans ee where I am so far.

    This is stretching my Access kniowledge/capabilites a bit (especially with Many-to-Many) , but I am learning a lot. Is this a scenario where I have to declare the new variable in the Parameters some how?

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

    Re: Parse Access field to do lookup (Access 2003)

    I had assumed that the ID's were numbers. The function Val(...) in the expressions converts text values to numbers, you can simply omit it:

    TitleID: Mid([UPCNumber],[SpacePos]+1,InStrRev([UPCNumber]," ")-[SpacePos]-1)

    CompanyID: Left([UPCNumber],[SpacePos]-1)

    This will also preserve leading zeros.

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Access field to do lookup (Access 2003)

    From the Access help file:

    "VAL
    Returns the numbers contained in a string as a numeric value of appropriate type.
    Syntax
    Val(string)"

    That was pretty lazy of me- I guess I just got excited. Thanks again!

Posting Permissions

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