concatenating text & combo (1 Viewer)

[SueBK]

In a report, how do I concatenate a combo and text field?

My example: In Table 1 I have a field "Name", which is look up bound to column 1 but showing column 2 of Table 2. I also have a free entry date field "Date". I like a single item on my report "[Name], [Date]".

Whenever I've tried this before my result has ended up being "1, 12/4/09" instead of "John Bloggs, 12/4/09".

How do I concatenate so it returns the 2nd column results, rather than the bound column, PLUS the additional text field?

 

[boblarson]

1st of all, I hope you aren't saying that you have lookups defined at TABLE level. They should be at form level. If at table level, that's your problem.

 

[bulrush]

Let me see if I can remember the details about combo boxes.

Your combo box must be set to return the column (of the source query in the combo box) that you want to display. This value is put into the .Value and .Text properties.

If you are returning column 1 in the cbx (the value "1") but you really want column 2 ("John Boggs"), you have to set your combo box to return column 2. You can't have both.

A second solution: let the cbx return column 1 (value "1") and use it as a key for a Dlookup in your concatenation. So your text box would have a concat that looks like:
=Dlookup("field_to_return","table","IDFIELD=" & cbxMycombo.value) & ", " & txtMydate

p.s. This VBA code in my second solution would go in the report's Detail_Format event, like so:
txtNewfield =Dlookup("field_to_return","table","IDFIELD=" & cbxMycombo.value) & ", " & txtMydate

 

[Scooterbug]

to reference the column, it would be ControlName.column(x) where x is the column position. Remember that when dealing with column position, the first position is 0.

So, if the control is, in fact a combo box with a row source (and not a table level lookup like Bob mentioned) it would be:

Forms!FormName!Name.column(1) & ", " & forms!formName!Date

Also note, that using both Name and Date as a control name or field should not be done. Both of those are Reserved Words and will cause you issues at some point.