Report to display "Name" instead of primary key ID with proper spacing?

[Neil Chelo]

(Using Access 2003)

I had a report with the following text box that combined three fields.

=[city] & " " & [state] & " " & [postalcode]

I recently changed my table structure so [state] is now [fkStateID] which is
a lookup from tblStates.

When I changed my text box to =[city] & " " & [fkstateid] & " " &
[postalcode] the report displayed the primary key for [fkStateID] instead of
the lookup abbreviation.

Report Example: (33 is pk for NY)
"New York 33 10022" Instead of "New York NY 10022"

If I simply add fkStateID to the report it works fine sinc it is a combo
box. So I amended by text box into three seperate pieces as follows.
[city][fkStateID][postalcode]

This worked , but created another problem of spacing. So if the city name
is short it creates a large space between the city name and the state.
Anyway to fix this spacing problem and show the state abbreviation instead
of the primary key for the state abbreviation?

 

[tina]

hmmm, this might work:

=[city] & " " & [fkstateid].[Column](1) & " " & [postalcode]

columns in combo boxes are zero-based, meaning the first column is 0, the
second is 1, etc. i made the assumption that the first column (hidden) of
the combo box is the primary key field, and the second column is the state
name - hence the (1).
also, my syntax may be off. if above doesn't work, try building the
expression in the builder dialog before you give up on it.

also, fyi - if you changed the state field to a Lookup field *in your
table*, bad idea. <cringe>. see the link below for more info:

http://www.mvps.org/access/lookupfields.htm

hth

 

[Neil Chelo]

Thanks for the suggestion but no luck.

getting "#Name?" for a results on my report
tried it as textbox =[fkStateID].[Column](1)
also changed text box to combo box but still no luck

Any other suggestions?

hmmm, this might work:

=[city] & " " & [fkstateid].[Column](1) & " " & [postalcode]

columns in combo boxes are zero-based, meaning the first column is 0, the
second is 1, etc. i made the assumption that the first column (hidden) of
the combo box is the primary key field, and the second column is the state
name - hence the (1).
also, my syntax may be off. if above doesn't work, try building the
expression in the builder dialog before you give up on it.

also, fyi - if you changed the state field to a Lookup field *in your
table*, bad idea. <cringe>. see the link below for more info:

http://www.mvps.org/access/lookupfields.htm

hth

(Using Access 2003)

I had a report with the following text box that combined three fields.

=[city] & " " & [state] & " " & [postalcode]

I recently changed my table structure so [state] is now [fkStateID]

which

is
a lookup from tblStates.

When I changed my text box to =[city] & " " & [fkstateid] & " " &
[postalcode] the report displayed the primary key for [fkStateID]

instead

of
the lookup abbreviation.

Report Example: (33 is pk for NY)
"New York 33 10022" Instead of "New York NY 10022"

If I simply add fkStateID to the report it works fine sinc it is a combo
box. So I amended by text box into three seperate pieces as follows.
[city][fkStateID][postalcode]

This worked , but created another problem of spacing. So if the city name
is short it creates a large space between the city name and the state.
Anyway to fix this spacing problem and show the state abbreviation instead
of the primary key for the state abbreviation?

 

[Guest]

If the report is run off the table, you will need to change that so the report is run off a query. In the query, you would have to link the table to the lookup table using fkStateID as the link. Put all the fields you need into the query. Instead of putting fkStateID as the field, use the state name field from the lookup table. You might also have to play with the joins a little.

 

[tina]

well, you'd have to include the combo box in the report design, with the
Visible property set to No. if you didn't, my guess is that's why it didn't
work.

other than that, no suggestions. following the standard rule of "no lookup
fields in tables", i wouldn't build a report like that anyway, so i've no
real experience in making it work.

good luck.

Thanks for the suggestion but no luck.

getting "#Name?" for a results on my report
tried it as textbox =[fkStateID].[Column](1)
also changed text box to combo box but still no luck

Any other suggestions?

hmmm, this might work:

=[city] & " " & [fkstateid].[Column](1) & " " & [postalcode]

columns in combo boxes are zero-based, meaning the first column is 0, the
second is 1, etc. i made the assumption that the first column (hidden) of
the combo box is the primary key field, and the second column is the state
name - hence the (1).
also, my syntax may be off. if above doesn't work, try building the
expression in the builder dialog before you give up on it.

also, fyi - if you changed the state field to a Lookup field *in your
table*, bad idea. <cringe>. see the link below for more info:

http://www.mvps.org/access/lookupfields.htm

hth

(Using Access 2003)

I had a report with the following text box that combined three fields.

=[city] & " " & [state] & " " & [postalcode]

I recently changed my table structure so [state] is now [fkStateID]

which

is
a lookup from tblStates.

When I changed my text box to =[city] & " " & [fkstateid] & " " &
[postalcode] the report displayed the primary key for [fkStateID]

instead

of
the lookup abbreviation.

Report Example: (33 is pk for NY)
"New York 33 10022" Instead of "New York NY 10022"

If I simply add fkStateID to the report it works fine sinc it is a combo
box. So I amended by text box into three seperate pieces as follows.
[city][fkStateID][postalcode]

This worked , but created another problem of spacing. So if the city name
is short it creates a large space between the city name and the state.
Anyway to fix this spacing problem and show the state abbreviation instead
of the primary key for the state abbreviation?

 

[Neil Chelo]

That worked !!!!

Thank you very much.

well, you'd have to include the combo box in the report design, with the
Visible property set to No. if you didn't, my guess is that's why it didn't
work.

other than that, no suggestions. following the standard rule of "no lookup
fields in tables", i wouldn't build a report like that anyway, so i've no
real experience in making it work.

good luck.

Thanks for the suggestion but no luck.

getting "#Name?" for a results on my report
tried it as textbox =[fkStateID].[Column](1)
also changed text box to combo box but still no luck

Any other suggestions?

hmmm, this might work:

=[city] & " " & [fkstateid].[Column](1) & " " & [postalcode]

columns in combo boxes are zero-based, meaning the first column is 0, the
second is 1, etc. i made the assumption that the first column (hidden) of
the combo box is the primary key field, and the second column is the state
name - hence the (1).
also, my syntax may be off. if above doesn't work, try building the
expression in the builder dialog before you give up on it.

also, fyi - if you changed the state field to a Lookup field *in your
table*, bad idea. <cringe>. see the link below for more info:

http://www.mvps.org/access/lookupfields.htm

hth


(Using Access 2003)

I had a report with the following text box that combined three fields.

=[city] & " " & [state] & " " & [postalcode]

I recently changed my table structure so [state] is now [fkStateID] which
is
a lookup from tblStates.

When I changed my text box to =[city] & " " & [fkstateid] & " " &
[postalcode] the report displayed the primary key for [fkStateID] instead
of
the lookup abbreviation.

Report Example: (33 is pk for NY)
"New York 33 10022" Instead of "New York NY 10022"

If I simply add fkStateID to the report it works fine sinc it is a combo
box. So I amended by text box into three seperate pieces as follows.
[city][fkStateID][postalcode]

This worked , but created another problem of spacing. So if the

city
name

is short it creates a large space between the city name and the state.
Anyway to fix this spacing problem and show the state abbreviation instead
of the primary key for the state abbreviation?