Using the Lookup

[jo15765]

If I have set a table to Look up, and pull from a list box, and set the Table I want it to look up info from.....I have a separate table with the city in one column and the state in another. I want it displayed this way, when you look up, but it is combining the two into one when it is set as the row source. Is it possible to add two fields to my TBL_Information, so that when I lookup using my TBL_Locatoin the city and the state are displayed in a different column?

[ssanfu]

I'm a little confused on what you want to do.

Would you provide:
table names
form name
list box name and
list box record source (SQL).

Is there any event code for the list box?

Where do you want the city name and state name to be displayed? Form? Text boxes?

[jo15765]

I have no event code set for this. I am wanting the city, and state to be displayed as city,state on my form, but it is not displaying. I am attaching a .zip of my database and see if that will provide further insight.

[ssanfu]

I changed the form record source and the combo box row source to queries.
While you can use tables as the sources, you cannot filter or sort the records.

I added the state abbreviations and added four records.......

[jo15765]

That is exactly what I was wanting to do! So the only way to combine the City & State Field if they are in seperate columns is through a SQL statement like:

SELECT TBL_Location.id, [City] & ", " & [State] AS CitySt
FROM TBL_Location

[ssanfu]

While concatenating the two fields in the query is probably the better/easier way, you could also concatenate the two fields using a control (text box) on a form/report.

If the form/report record source has two separate columns you want to combine, in the Control Source you would use "= [City] & ", " & [State] " (without the quotes)