Field in Query Expression Displaying Number on Report

[ssaucedo]

Using Access 2007

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.

 

[Ron2006]

Most probably the solution to your problem is:


In design mode on the query, add the state table and link the lustate
of the main table to the ID field in your state table.

Then in the location in your expr1 code change the reference to
[lustate] to instead point to the field name of the state table that
actually contains the state name. (In essence that is what the
following is doing:

bound column 1
columns 2
column width 0, 1

The above says to save field 1 but show field 2 on your form.

Ron

 

[Ron2006]

The same code will be necessary wherever in any query you wish to show
the state name.

Ron

 

[ssaucedo]

i am getting mismatched type expression?

 

[KARL DEWEY]

Post the complete SQL of your query to include the the 'state' table.

--
Build a little, test a little.

i am getting mismatched type expression?

Most probably the solution to your problem is:

In design mode on the query, add the state table and link the lustate
of the main table to the ID field in your state table.

Then in the location in your expr1 code change the reference to
[lustate] to instead point to the field name of the state table that
actually contains the state name. (In essence that is what the
following is doing:

bound column 1
columns 2
column width 0, 1

The above says to save field 1 but show field 2 on your form.

Ron

 

[John W. Vinson]

Using Access 2007

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.

This is the infamous "Lookup Field" misfeature. See
http://www.mvps.org/access/lookupfields.htm
for a critique. Basically, what you see is NOT what is there! What's stored in
the table is a number. This fact is concealed from your view in the table, and
in SOME forms and in SOME reports and in SOME queries. You can't count on it
though.

Base your report, not directly on the table, but on a Query joining the table
to the States lookup table. Select the state name from the states table for
display.