Formatting the text portion of a combo box

[Mike]

This is sort of a continuation from a previous post, but in the concept of
what was there, i think i'm missing something, so here it goes - step by
step - from the begging.

In short, i'm creating a combo box to lookup records in the table based on a
phone number. Problem is, the combo box doesn't format the list in
(000)000-0000 format. It formats it 5555551212.

The explanation was to create a query and add two columns of phone number in
it. Format the second one to the correct (000)000-0000 and reference that
one visually in the form.

I thought i had it, it looked to be working, then i tried to rebuild it and
it's failed ever since.

Here's the step by step using Access 2007.
I started simple: A table named TEST. Three fields in it. ID, PHONE, and
CUST. ID is autonumber, the other two are text fields.
add two records

Created a query.
Chose the Query Design option.
Chose the table TEST
Dragged in PHONE, PHONE, ID, CUST
(FWIW, viewing the table in datashat view lists the columns as Expr1000,
PHONE, ID, CUST)
I right click in the 2nd Phone field and set a format of 00-0000-0000
Checking the datasheet view shows Expr1000 in the default format 000000000
and PHONE in the (000)000-0000 format.

Creating a form
Basic form, three fields as noted.
I create a combo box:
Find records based on values from the combo
I choose both phone records
The sample values the combo box displays are both in 0000000000 format.
After finishing the query and running the form, both sides of the combobox
display as 0000000000, no formatting is getting from the query to the form.
I haven't bothered with hiding one column or picking a binding value. At
this point they can't even display properly.

Is there some step i'm missing or something i'm overlooking. This is driving
me nuts!

Mike

 

[Pat Hartman]

When I suggested duplicating the column, I thought that you were formatting
the key. Since you have an ID column and I presume that is what you use to
identify a record, you don't need the unformatted phone column.

1. Rearrange your query's columns so that the ID is first.
2. Instead of using right-click to format, you need to actually use the
Format() function. You need to turn the phone number into a text string in
order for it to maintain its format in the RowSource.
3. Bound column is 1
4. Column count is 3
5. Column widths is 0";1";0"

 

[Mike]

I'm sure this will sound stupid, but where, specifically, do i use/place the
format() function? On a line in the query? In the tables combobox itself?
and if so, under which setting? I understand the rest.

 

[Mike]

Putting in the rowsource works...solution was posted earlier this morning in
the previous thread.

Thanks to everyone for their help!

Mike