I want to format the choices in the drop down of a combo box

  • Thread starter Thread starter Flora
  • Start date Start date
F

Flora

I have a combo box that I'd like to format so that the selections in
the dropdown list have the following format: PCP-01-01-01. (The
selected value is displayed in the correct format). Currently, the
values in the dropdown list look like: 010101 but I want the formatted
characters PCP- - - to show as well.

Currently the rowsource code looks like this

SELECT tbl_CONTROL_MATRIX.[PCP-SP-Ref]
FROM tbl_CONTROL_MATRIX
ORDER BY tbl_CONTROL_MATRIX.[PCP-SP-Ref];

I tried to add the format function into the sql code above but I can't
get it right. Here are some of my attempts:

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], ""PCP-"##"-"##"-"##")

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], "'PCP-'##'-'##'-'##")

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], ""PCP-"@@-@@-@@)

and an assortment of other attempts.

I'm tired of hacking this problem. All the resources that I've looked
into describe the format function for date, time or case.What is the
correct way to use the format function in my situation?

Thanks for your help.
Cheers!!
 
I have a combo box that I'd like to format so that the selections in
the dropdown list have the following format: PCP-01-01-01. (The
selected value is displayed in the correct format). Currently, the
values in the dropdown list look like: 010101 but I want the formatted
characters PCP- - - to show as well.

Currently the rowsource code looks like this

SELECT tbl_CONTROL_MATRIX.[PCP-SP-Ref]
FROM tbl_CONTROL_MATRIX
ORDER BY tbl_CONTROL_MATRIX.[PCP-SP-Ref];

I tried to add the format function into the sql code above but I can't
get it right. Here are some of my attempts:

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], ""PCP-"##"-"##"-"##")

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], "'PCP-'##'-'##'-'##")

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], ""PCP-"@@-@@-@@)

and an assortment of other attempts.

I'm tired of hacking this problem. All the resources that I've looked
into describe the format function for date, time or case.What is the
correct way to use the format function in my situation?

Thanks for your help.
Cheers!!
Click to expand...

Select "PCP-" & Format([PCP-SP-Ref],"@@-@@-@@") as NewColumnName From
tbl_Control_Matrix;
 
Thank you Fred and Al. Your responses INSTANTLY resolved my problem.
The simplicity of most solutions amaze me!!
 
Flora said:
I have a combo box that I'd like to format so that the selections in
the dropdown list have the following format: PCP-01-01-01. (The
selected value is displayed in the correct format). Currently, the
values in the dropdown list look like: 010101 but I want the formatted
characters PCP- - - to show as well.

Currently the rowsource code looks like this

SELECT tbl_CONTROL_MATRIX.[PCP-SP-Ref]
FROM tbl_CONTROL_MATRIX
ORDER BY tbl_CONTROL_MATRIX.[PCP-SP-Ref];

I tried to add the format function into the sql code above but I can't
get it right. Here are some of my attempts:

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], ""PCP-"##"-"##"-"##")

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], "'PCP-'##'-'##'-'##")

SELECT format(tbl_CONTROL_MATRIX.[PCP-SP-Ref], ""PCP-"@@-@@-@@)

and an assortment of other attempts.

I'm tired of hacking this problem. All the resources that I've looked
into describe the format function for date, time or case.What is the
correct way to use the format function in my situation?
Click to expand...


The combo box's Format property only affects what is
diplayed in the box, not the drop down list.

To affect the dropdown list, you need to include the
formatted string in the combo box's row source query:

SELECT [PCP-SP-Ref],
Format([PCP-SP-Ref], """PCP-""@@-@@-@@")
FROM tbl_CONTROL_MATRIX
ORDER BY [PCP-SP-Ref]

Then set the combo box's properties:
ColumnCount 2
ColumnWidths 0;
BoundColumn 1
The BoundColumn allows you to use the unformatted value in
any calculations or save it to a field in the form's record
source table.
 
Back
Top