concatenated text string using lookup values

[sakool]

Hi,

I am trying to use a concatenated string in a query but it is returning the
primary key value instead of the related value from the combo box. Does
anyone have ideas on how to fix this? This is the expression I used.
Day: [Order Form]![Event Day] & ", the " & [Order Form]![Event Date] & " of
" & [Order Form]![Event Month]

Thanks for your help

 

[Sergey Poberezovskiy]

If you specify a combobox by itself, Access returns the
value in its Bound column, and not the Text. So to resolve
your problem, you will need to refer to the corresponding
column, for example:
[Order Form]![Event Day].Column(1)

Remember that columns start from 0.

HTH

 

[sakool]

Yes, I should have mentioned that I tried that and got this error
Undefined Function '[Order Form]![Event Day].Column'

Thanks

If you specify a combobox by itself, Access returns the
value in its Bound column, and not the Text. So to resolve
your problem, you will need to refer to the corresponding
column, for example:
[Order Form]![Event Day].Column(1)

Remember that columns start from 0.

HTH

-----Original Message-----
Hi,

I am trying to use a concatenated string in a query but it is returning the
primary key value instead of the related value from the combo box. Does
anyone have ideas on how to fix this? This is the expression I used.
Day: [Order Form]![Event Day] & ", the " & [Order Form]! [Event Date] & " of
" & [Order Form]![Event Month]

Thanks for your help

.

 

[Sergey Poberezovskiy]

The easiest solution to me would be to write a Public
function in a standard module which would return the
desired result, and then use it in the query:

Public Function OrderDate() As String
Dim frm As [Form_Order Form]
On Error Resume Next
Set frm = Forms![Order Form]
On Error Goto 0
If Not frm Is Nothing Then
With frm
OrderDate = ![Event Day].Column(1) & ", " & ...
End With
End If
End Function

And then in your query

Select ..., OrderDate() As [My Date]
From ...

HTH

-----Original Message-----
Yes, I should have mentioned that I tried that and got this error
Undefined Function '[Order Form]![Event Day].Column'

Thanks

If you specify a combobox by itself, Access returns the
value in its Bound column, and not the Text. So to resolve
your problem, you will need to refer to the corresponding
column, for example:
[Order Form]![Event Day].Column(1)

Remember that columns start from 0.

HTH

-----Original Message-----
Hi,

I am trying to use a concatenated string in a query

but
it is returning the

primary key value instead of the related value from

the
combo box. Does

anyone have ideas on how to fix this? This is the expression I used.
Day: [Order Form]![Event Day] & ", the " & [Order

Form]!
[Event Date] & " of

" & [Order Form]![Event Month]

Thanks for your help

.

.

 

[sakool]

I just found the answer in help and support for anyone else looking for this
in the future.
http://support.microsoft.com/default.aspx?scid=kb;en-us;141617

Wrap the reference in the eval() function so that it doesn't try to parse it
separately.

Yes, I should have mentioned that I tried that and got this error
Undefined Function '[Order Form]![Event Day].Column'

Thanks

If you specify a combobox by itself, Access returns the
value in its Bound column, and not the Text. So to resolve
your problem, you will need to refer to the corresponding
column, for example:
[Order Form]![Event Day].Column(1)

Remember that columns start from 0.

HTH

-----Original Message-----
Hi,

I am trying to use a concatenated string in a query but it is returning the
primary key value instead of the related value from the combo box. Does
anyone have ideas on how to fix this? This is the expression I used.
Day: [Order Form]![Event Day] & ", the " & [Order Form]! [Event Date] & " of
" & [Order Form]![Event Month]

Thanks for your help

.