Concatenate two columns in a table or IF statement?

S

Sr Accountant

I have a table that has a provider # column and then a provider name column.
In order to get my drop down list to appear correctly for our users on the
main form, I created a query that added the provider # and name together.
However, when I do this, of course my reports do not work properly now. The
report prompts me for a provider #, which is now missing by its self. Is
there a way to leave what the user sees, as the combined name and number, but
something like an "if" provider # and name is this, then give me the provider
# only somewhere else that I can pull for my report?

Any help would be greatly appreciated.
 
S

storrboy

I have a table that has a provider # column and then a provider name column.
In order to get my drop down list to appear correctly for our users on the
main form, I created a query that added the provider # and name together.
However, when I do this, of course my reports do not work properly now. The
report prompts me for a provider #, which is now missing by its self. Is
there a way to leave what the user sees, as the combined name and number, but
something like an "if" provider # and name is this, then give me the provider
# only somewhere else that I can pull for my report?

Any help would be greatly appreciated.
Click to expand...


Why not just have both columns displayed in the combobox? If they were
the two leftmost in the control(Provider#,ProviderName...), you would
make sure the following properties of the combo were set:

BoundColumn=1 (If in example Provider# is the desired value)
ColumnCount=(Minimum of 2)
ColumnWidths=(set widths in inches desired for each column ie.
"1.5;2")

This will leave the columns separate, but both are displayed in the
dropdown.
 
W

Wayne-I-M

Hi

I would not have mixed together the fields - its a simply thing to have the
combo box or dropdown list display multiple columns.

On you form create the combo and use the wizard to create the drop down as
you want.

Or use something like this is a the row source (row type would be Table/Query)

SELECT [TableName].[ClientNumber], [TableName].[Client1stName],
[TableName].[Client2ndName] FROM [TableName] ORDER BY [Client2ndName];

Change the field names and table name to what they are in your DB

Column Count = 3
Column Widths = 1cm;2.542cm;2.542cm
(note 2.542cm is the standard width created by the wizard)


This way you fields will be displayed and your report will still work

Hope this helps
 
Top