Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace 'codes' with specific text (Office 2003)

    I have a field that will contain either "FOC", "FOA" or "FOD". If it is FOC, I would like it to show specific text (if FOC, then show COLLECT,etc.) . Same for the others. How can I do this in my table?

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

    Re: Replace 'codes' with specific text (Office 2003)

    The easiest way is to create a separate table with two text fields: code and description:

    <table border=1><td>Code</td><td>Description</td><td>FOA</td><td>...</td><td>FOC</td><td>Collect</td><td>FOD</td><td>...</td></table>
    In the existing table, you can simply enter FOC, FOA, etc.
    In a form based on the table, create a combo box bound to the code field.
    Set its Row Source to the name of the table you just created.
    Set Column Count to 2 and Column Widths to 0";1".
    The combo box will store the code but display the description.

  3. #3
    New Lounger
    Join Date
    Jan 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace 'codes' with specific text (Office 200

    I never would have figured that out. Thanks. If I'm not using a form however, but a report, would I be able to do the same thing? (So my source table would have the 3 letter code, and the report would show the description based on that code.) I appreciate your help!

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

    Re: Replace 'codes' with specific text (Office 200

    You can do either of the following:

    - Use a combo box on the report, the same way as on the form. The dropdown arrow of the combo box will not be displayed when you preview or print the report.

    or

    - Create a query based on your original table and the small auxiliary table.
    - Join the tables on the code field.
    - Double click the join line and select the option to include ALL records from the original table.
    - Add the fields from the original table to the query grid, plus the description field from the auxiliary table.
    - Save the query and use it as record source for the report.

  5. #5
    New Lounger
    Join Date
    Jan 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace 'codes' with specific text (Office 200

    My apologies, but I can't seem to get it to work. I need to see the three letter code in my work (my tables and queries), but the report that I generate (for third party) can't show that code, just the description, so I don't think a combo box would work as no one is selecting anything. I'm generating reports for many customers by their products (so one report per customer, yet many products). Each product has a specific code (FOC, FOA, etc.). I just want the report to disguise that code with my desired text.

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

    Re: Replace 'codes' with specific text (Office 200

    Have you actually tried it? Create a combo box in the detail section of the report.
    - Control Source = name of code field
    - Row Source = name of auxiliary table
    - Column Count = 2
    - Column Widths = 0";1"
    Although it is bound to the code field, the combo box will display the description.

  7. #7
    New Lounger
    Join Date
    Jan 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace 'codes' with specific text (Office 200

    I did. When I run the report, no text appears in the combo box area.

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

    Re: Replace 'codes' with specific text (Office 200

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    New Lounger
    Join Date
    Jan 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace 'codes' with specific text (Office 200

    This is extremely stripped down.

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

    Re: Replace 'codes' with specific text (Office 200

    You must set the Row Source Type property of the combo box to Table/Query instead of Value List. After all, the Row Source is the INCO table.

    BTW, if you use this method, you can base the report directly on the List and Special Price by Custo table, no need for the Custo Query.
    If you want to use Custo Query, you must join the tables on INCO vs Code instead of INCO vs Description.

  11. #11
    New Lounger
    Join Date
    Jan 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace 'codes' with specific text (Office 200

    That worked perfectly. Thank you.

Posting Permissions

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