Concatenate string problem

[DblDogDare]

I used dhookom's FAQ which worked great, however, I am having a problem. I have a table called tblLocations. It has 3 columns (Location ID, City, State). I have another table called tblTripDetails. In it is a field that uses a "look up" combobox to tblLocations. My concatenation query {Locations: Concatenate("Select Location from tblTripDetails WHERE TripID=" &[TripID])}is giving me the following

TripID Locations
1 12; 20; 36
2 13; 17; 22

What I want is

TripID Locations
1 Haymarket, Virginia; Nashville, Tennessee; etc.

It appears to be picking up the LocationID vs the already concatenated city, State. Is it possible to us "AS" or to reference an already concatenated field within a concatenation. I hope I am clear in my problem.

 

[dhookom]

Create a query based on tblTripDetails and tblLocations joined on the Location ID. You can then use this query rather than your tblTripDetails in the Concatenate.

Locations: Concatenate("Select City & ', ' & State FROM qNewQuery WHERE TripID = " & [TripID],";")

If you really did use lookup fields in your table design, I would seriously consider removing them. Read this

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DblDogDare]

dhookom, thanks for the insight. Do you believe the lookups in the tables are causing my concatenation problem? I am early enough in my db design that I could change this. I suppose the alternative are queries as lookups in the forms? Am I thinking along the proper lines or can you reccommend any other alternative? I will check out other posts and see what other reccommendations are out there. Thanks again.

 

[lespaul]

If it's early enough to change the design you should. Here's why The Evils of Lookup Fields in Tables, here's a couple more must reads , The Ten Commandments of Access and The Fundamentals of Relational Database Design

If you have any other questions or want some advice on your design, feel free to post back. We much prefer helping someone get a good design because that makes extracting data much easier in the long run!

HTH


Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[DblDogDare]

lespaul, dhookom, Thanks for your input. I was able to redo my database and get rid of the lookups in the tables. Everything seems to work great including the concatenations. Now, for a little twist to the concatenation. I don't know if this is do-able or not. In my example above, my table is such, with Lead being a checkbox.

TripID City State Lead
1 Haymarket Virginia Yes
1 Nashville Tennessee No

There is only one lead for each trip. I would like to have the the lead Bold in the concatenation as in my sample below, within a form and reports.

TripID Locations
1 Haymarket, Virginia; Nashville, Tennessee; etc.


Is this do-able or am I wanting to do more then capable?

 

[dhookom]

You can't have more than one format for a single text box.

http://www.lebans.com

has some code for RTF controls but I doubt this will help you. I you really need bold, you may want to separate the value into two text boxes so you can bold one.

There might be a code method that uses the Print method of the report to divide and print with different font weights.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RoyVidar]

I've had a short look at one of the samples at Lebans, Mix Bold Plain, which seems to do that. The db I had a short look at, was the Lady.mdb, and within the report, check out the details on print event. The "tbLady" control, seems to be an ordinary text control. Should be possible to adapt to this report.

Roy-Vidar