Combo box problem

[Tom]

Using Access 2002, have a combo box on a form and using:

SELECT DISTINCT tblOutput.Year
FROM tblOutput
WHERE (((tblOutput.Year) Is Not Null))
ORDER BY tblOutput.Year DESC;

as the record source. In the table we have the entries:

2005
2004
2003

yet what is seen in the combobox is:

2005.00
2004.00
2003.00

Any advise on how to correct this would be appreciated

TIA

Tom

 

[Allen Browne]

Try setting the Format property of the combo to General Number.

 

[Tom]

Hi Allen

Thanks for your input but it makes no difference & setting decimal places to
Auto or 0 has no effect

any other suggestions?

Tom

 

[Ken Snell [MVP]]

Is the combo box bound to a field in the form's recordsource? What is the
format that is set for that field if yes?

Do you see the 2 decimal places in the dropdown list? or just in the combo
box after you make a selection?

What is the format for the [Year] field in tblOutput?

Also, do not use Year as the name of a table's or query's field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

 

[Tom]

Ken

Thanks for input, answers inline

Tom

Is the combo box bound to a field in the form's recordsource? What is the
format that is set for that field if yes?

Combo is unbound

Do you see the 2 decimal places in the dropdown list? or just in the combo
box after you make a selection?
In the dropdown list
What is the format for the [Year] field in tblOutput?

Field set as Number, Long Integeer, Fixed, 0 Decimal places

 

[Allen Browne]

It would be good to get to the source of the issue, which could be because
of the name as Ken says.

A klutzy workaround would be:
SELECT DISTINCT Format(tblOutput.Year, "0") AS TheYear
FROM tblOutput
WHERE (((tblOutput.Year) Is Not Null))
ORDER BY tblOutput.Year DESC;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ken

Thanks for input, answers inline

Tom

Is the combo box bound to a field in the form's recordsource? What is the
format that is set for that field if yes?

Combo is unbound

Do you see the 2 decimal places in the dropdown list? or just in the
combo box after you make a selection?
In the dropdown list
What is the format for the [Year] field in tblOutput?

Field set as Number, Long Integeer, Fixed, 0 Decimal places

Also, do not use Year as the name of a table's or query's field. It and
many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:
point taken - will correct
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

 

[Tom]

Allen

trying your suggestion I get an error message: orderby
clause(tblOutput.Year) conflicts with distinct.

Additionally changing the field name from Year to Output year makes no
difference

Tom

It would be good to get to the source of the issue, which could be because
of the name as Ken says.

A klutzy workaround would be:
SELECT DISTINCT Format(tblOutput.Year, "0") AS TheYear
FROM tblOutput
WHERE (((tblOutput.Year) Is Not Null))
ORDER BY tblOutput.Year DESC;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ken

Thanks for input, answers inline

Tom

Is the combo box bound to a field in the form's recordsource? What is
the format that is set for that field if yes?

Combo is unbound

Do you see the 2 decimal places in the dropdown list? or just in the
combo box after you make a selection?
In the dropdown list
What is the format for the [Year] field in tblOutput?

Field set as Number, Long Integeer, Fixed, 0 Decimal places

Also, do not use Year as the name of a table's or query's field. It and
many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:
point taken - will correct
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>


Hi Allen

Thanks for your input but it makes no difference & setting decimal
places to Auto or 0 has no effect

any other suggestions?

Tom
Try setting the Format property of the combo to General Number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using Access 2002, have a combo box on a form and using:

SELECT DISTINCT tblOutput.Year
FROM tblOutput
WHERE (((tblOutput.Year) Is Not Null))
ORDER BY tblOutput.Year DESC;

as the record source. In the table we have the entries:

2005
2004
2003

yet what is seen in the combobox is:

2005.00
2004.00
2003.00

Any advise on how to correct this would be appreciated

 

[Dennis]

To fix your combo display problem, on the Year field in the tblOutput table,
Remove the fixed in the format so that it is blank
Change 0 dp to Auto

Ken

Thanks for input, answers inline

Tom

Is the combo box bound to a field in the form's recordsource? What is the
format that is set for that field if yes?

Combo is unbound

Do you see the 2 decimal places in the dropdown list? or just in the combo
box after you make a selection?
In the dropdown list
What is the format for the [Year] field in tblOutput?

Field set as Number, Long Integeer, Fixed, 0 Decimal places

Also, do not use Year as the name of a table's or query's field. It and
many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:
point taken - will correct
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

 

[Tom]

Dennis

THANK YOU!

Tom

To fix your combo display problem, on the Year field in the tblOutput
table,
Remove the fixed in the format so that it is blank
Change 0 dp to Auto

Ken

Thanks for input, answers inline

Tom

Is the combo box bound to a field in the form's recordsource? What is
the
format that is set for that field if yes?

Combo is unbound

Do you see the 2 decimal places in the dropdown list? or just in the
combo
box after you make a selection?
In the dropdown list
What is the format for the [Year] field in tblOutput?

Field set as Number, Long Integeer, Fixed, 0 Decimal places

Also, do not use Year as the name of a table's or query's field. It and
many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for
more
information:
point taken - will correct
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>


Hi Allen

Thanks for your input but it makes no difference & setting decimal
places
to Auto or 0 has no effect

any other suggestions?

Tom
Try setting the Format property of the combo to General Number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Using Access 2002, have a combo box on a form and using:

SELECT DISTINCT tblOutput.Year
FROM tblOutput
WHERE (((tblOutput.Year) Is Not Null))
ORDER BY tblOutput.Year DESC;

as the record source. In the table we have the entries:

2005
2004
2003

yet what is seen in the combobox is:

2005.00
2004.00
2003.00

Any advise on how to correct this would be appreciated