Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2017
    Posts
    6

    Answered: Replacing text in a report

    G'day

    I have a form that has a combobox on it, which has locations stored in its underlying table. Example: Kitchen, Office, Cabin 1, Cabin 2, Cabin3 etc.

    When entering data everything works fine, but it is a pita when selecting one of the cabins (there are 18 of them.) Dropping down the list and selecting the cabin is too slow and it's not much faster to type the cabin name in, because the first six characters are the same and you have to type to the 7th character before entering the number of the cabin you want.

    So, I went back into the table and changed all of the cabin names to C1, C2, C3...etc. Much easier and quicker to type when entering data via the form.

    This creates a new problem. I won't be the person reading the reports that are based on this data. To them C1, C2, C3 means nothing. They will be looking for the long-form cabin 1, cabin 2 etc. I need to be able to generate a report which looks for the C1, C2 etc references in the "Location" field only, and changes them to "Cabin 1"or "Cabin 2" and so on. It is important that only this field gets searched and replaced, because there are other fields on the same form/report that may well have the letter "C"with a number after it. Example: A Television set with the model number KT-214C2.

    I've got an idea gelling but I'm not exactly sure how to execute it. I was going to create another table with the long-form names in it and then have the query that the report is based on pull from the long-form table, rather than the original table (for the "Location" field only) - but I'm not quite sure how to implement it.

    My great friend "Google"has let me down on this one. Anyone out there able to help?

    Cheers

    Rusty

  2. Best Answer
    Posted by weejas

    "A translation table is exactly the way to go. Put your abbreviations in one column and the full version in another.

    Base your report on a query that uses the main data table joined to the translation table. In place of the abbreviated location code from the data table, use the full version column from the translation table."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    A translation table is exactly the way to go. Put your abbreviations in one column and the full version in another.

    Base your report on a query that uses the main data table joined to the translation table. In place of the abbreviated location code from the data table, use the full version column from the translation table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Jul 2017
    Posts
    6
    Quote Originally Posted by weejas View Post
    A translation table is exactly the way to go. Put your abbreviations in one column and the full version in another.

    Base your report on a query that uses the main data table joined to the translation table. In place of the abbreviated location code from the data table, use the full version column from the translation table.
    Thanks weejas. It's nice to know that I was on the right track. I had a play in my "change and try" version of the database and the translation table will work fine.

    Cheers

    Rusty

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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