Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract part data from a field (2003)

    Hi,

    I am try to make a query that uses two tables. One table contains a field called CRN the other table has one called SPELL they contain the same data except 'SPELL' has an addition number at the end. I want to link the two fields together but only by the first 6 numbers in the table containing field 'SPELL'

    Example:
    CRN SPELL
    123456 123456-1
    123456 123456-2
    122546 122546-1
    Etc

    How do I tell the query to look at the first 6 digits and the the whole data

    Many Thanks
    Kevin
    Regards
    Gerbil (AKA Kevin)

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

    Re: Extract part data from a field (2003)

    Create a query based on the table that contains SPELL.
    Add the fields that you need (or *).
    Create a calculated column:

    SPELL6: Left([SPELL],6)

    Save and close this query.

    Next, create a query based on the table containing CRN and on the query.
    Join them on CRN vs SPELL6.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract part data from a field (2003)

    Genius, thank you

    I had the right code Left([SPELL],6) but try to use criteria

    Many thanks
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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