Results 1 to 3 of 3

Thread: Field in Query Expression Displaying Numbe

  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Field in Query Expression Displaying Numbe

    I have a field, "state" on a table "invoice".

    Have a data entry form "invoices" which has a combo box field "states" that points to the state table giving the user a list to choose from.

    I have a report that generates an invoice. This report is based on a query that pulls data from 2 tables, "contract" and "invoice".

    In the query I have an expression that concatenates a series of fields to make a sort of address block. This expression works much like the address block feature in Word, if a field is blank then access adjusts and does not show blanks lines, groups city state zip etc.

    However, I have one small problem. If I add the "state" field to the report via the wizard or in design mode manually, the actual state name specific to the current record, displays.

    But the same "states" field that is in my query expression displays a number when in print preview mode in the report.

    It appears to me that when adding a field to a report in Access 2007 access automatically sets the following to ensure that the state name appears and not the number being stored:

    bound column 1
    columns 2
    column width 0, 1

    but i am guessing this is not happening in the expression and i have no idea how to write these types of instructions in my query so that instead of the number the expression will display the actual state name.

    below is my current expression:

    Expr1: IIf(IsNull([fnom]),"",[fnom] & " " & [lnom] & Chr(13)+Chr(10)) & IIf(IsNull([dept]),"",[dept] & Chr(13)+Chr(10)) & IIf(IsNull([luorg]),"",[luorg] & Chr(13)+Chr(10)) & IIf(IsNull([add1]),"",[add1] & Chr(13)+Chr(10)) & IIf(IsNull([add2]),"",[add2] & Chr(13)+Chr(10)) & [city] & ", " & [lustate] & " " & [zip] & Chr(13)+Chr(10) & [ctry]

    I would really appreciated any help on this.

  2. #2
    Join Date
    Jan 2009
    Location
    Portland, Oregon
    Posts
    21
    Sounds like you have Lookups defined at table level. You should get rid of them (see here for why).
    Bob Larson
    Former Access MVP
    2008-2010

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    Quote Originally Posted by boblarson View Post
    Sounds like you have Lookups defined at table level. You should get rid of them (see here for why).
    you are correct however, i created a new field that was a text field at the table level and then a combo box on the data entry form and go the same problem, but only when the field is in an expression on a query, no where else. in all other objects the actual name displays.

Posting Permissions

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