Combo box problem

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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
 
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
 
Ken

Thanks for input, answers inline

Tom

Ken Snell said:
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?
Click to expand...

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?
Click to expand...

Field set as Number, Long Integeer, Fixed, 0 Decimal places
 
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.

Tom said:
Ken

Thanks for input, answers inline

Tom

Ken Snell said:
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?
Click to expand...

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?
Click to expand...

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
Click to expand...
Click to expand...
 
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

Allen Browne said:
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.

Tom said:
Ken

Thanks for input, answers inline

Tom

Ken Snell said:
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?
Click to expand...

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?
Click to expand...

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
Click to expand...
Click to expand...
Click to expand...
 
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

Tom said:
Ken

Thanks for input, answers inline

Tom

Ken Snell said:
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?
Click to expand...

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?
Click to expand...

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
Click to expand...
Click to expand...
 
Dennis

THANK YOU!

Tom
Dennis said:
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

Tom said:
Ken

Thanks for input, answers inline

Tom

Ken Snell said:
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?
Click to expand...

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?
Click to expand...

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top