Concontenate (should be easy)? 4

[jlnewbie]

I want to concatenate two fields as follows
=[Field1] & ", " & [Field2].
However, Field1 is from a lookup field in a related table. The result is a combination that looks like this, number , field2.
I know that that number value is coming from the combobox id number but
my bound column is set to 1 and yet another text box bound to field1 gives me the correct info. I get the same effect even my query expression builder.
What to do?
Thanks


[sig]<p>JLopez<br><a href=mailto:Lopez31@ATC-Enviro.com>Lopez31@ATC-Enviro.com</a><br>Environmental Services @ Your Service[/sig]

 

[Robertd]

Hi Jlopez,

Ok there realy isn't enough info to get to the bottom of this.

this might just be a typo thing but you don't have a period at the end of the statement do you?

as a rough guide the value returned from the combo box is the bound field, even if it displays some other data. what you are doing should work value1 & &quot;, &quot; value2 will give you

-> &quot;value1, value2&quot; as the string.

a quick method to debug this is to put a msgbox statement in the code just after the concatenation

like

msgbox &quot;[field1] = &quot; & me.[field1] & &quot; [field2] = &quot; & me.[field2] & vbcrlf & &quot;yourstringvar = &quot; & yourstringvar

this allows you to see what is happenning as the code runs until it's all sorted out.

HTH

[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[WP]

You can specifically reference the combo column. That is, if your combo box has 2 columns then you can reference the second column as combo.columns(1). Remember that for combo and list boxes the first column is (0) [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[jlnewbie]

Thanks Robertd & WP for responding to my post.

Let me more specific about my problem. My two fields are [Asbestos Type] and [Percentage 1]. I want to concatenate in a query expression or even a report these two fields.
I use the expression

=([Asbestos Type] & &quot; , &quot; & [Percentage 1])

and I get the following string

< 1, 10-15%>
not what it should be

Chrysotile, 10-15%

My field [Asbestos Type] is from a lookup field (combobox?). I've changed my bound colunm values but that only changes the value i see in my table, the query expression still gives me the same result. The number 1 is the value column (0) has ie ID number

What do i need to do so that the expression returns the right value?

I hope this clear, I am aware that field names with spaces are a no no, but I set the table up back when I did'nt know any better

&quot;A somewhat newbie&quot;
Julio
[sig]<p>JLopez<br><a href=mailto:Lopez31@ATC-Enviro.com>Lopez31@ATC-Enviro.com</a><br>Environmental Services @ Your Service[/sig]

 

[jendendav]

Also a newbie. However, I believe what Bill is saying is your solution. Instead of [Asbestos type] reference the combo box and as he also points out, multiple columns can be specifically identified. [sig][/sig]

 

[Robertd]

Hi Julio,

ok this just puts it all together

if [Asbestos Type] is the combo box name & etc

then

=me.[Asbestos Type].column(1) & &quot; , &quot; &
me.[Percentage 1].column(1)
should do it, oh the pain of disjointed object names ;-)

HTH




Customers.Column(1, 4) [sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[jlnewbie]

jendendave and bill thanks,
Sounds like an idea, how do i now reference the combox and specific columns? The wizard generated the combo box and I don't know what name it got.
thanks [sig]<p>JLopez<br><a href=mailto:Lopez31@ATC-Enviro.com>Lopez31@ATC-Enviro.com</a><br>Environmental Services @ Your Service[/sig]

 

[jlnewbie]

Thanks Robert
seems you posted exactly as was answering bill and jendendav.
I tried your epression referencing the combobox and specific column but I get an undefined function error
Julio
I know I heard it before &quot;oh the pain of disjointed object names&quot; [sig]<p>JLopez<br><a href=mailto:Lopez31@ATC-Enviro.com>Lopez31@ATC-Enviro.com</a><br>Environmental Services @ Your Service[/sig]

 

[Robertd]

Hi Julio

Oh how are you using these are you wanting to reference these in a query?? i tried breaking a cbo's name just to make sure it worked.

MsgBox Me.[Acct Type].Column(1)

if you want to refer to these in a query the try

forms![formName]![Asbestos Type].column(1) & &quot; , &quot; &
forms![formName]![Percentage 1].column(1)

maybe try try the msgbox to make sure it gets the correct values first, remember the form has to be open when the query runs,

i tried [Forms]![Accounts]![Acct Type].[Column](1)
on another form and works fine (access 97) the Me. reference wouldn't work here only on object it belongs to of course, mind you i'll set my cbo's name back to what it should be ;-)

HTH

HTH
[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[jendendav]

Did you try looking at the combo box properties in design view? That will tell what the box name is. Or have you figured all of this out from Robert's help. [sig][/sig]

 

[jlnewbie]

Let see, I gerenated a form from this table just so that i would get its name. The name of the combobox is Asbestos Type per the form design view. I still get the undefined function when i use Robert's idea and I did have the form opened. In design view i also noticed that the combo bound column property was set correctly at 1. So, still stuck but i know you guys have gotten me in the right direction.
[sig]<p>JLopez<br><a href=mailto:Lopez31@ATC-Enviro.com>Lopez31@ATC-Enviro.com</a><br>Environmental Services @ Your Service[/sig]