Combo Box Format

[L'apprentis]

I have a problem with one of my combo box. The format setting of the combo box doesn't change anything to the way I see my data. The row source is based on a query of which the 'data sheet view' shows my field with three decimal number. Whatever setting I change on my combo boxe parameter, the data in the list are always shown with 2 decimals. What can i do to view my data with 3 decimals?

 

[ghudson]

Build your combo box with SQL and use the Format() function to format the field in the SQL [query].

 

[L'apprentis]

Thank you very much

it's working:

SELECT DISTINCT Format([CasingOD],'00.000') AS [Casing OD] 
FROM TblCasing 
WHERE (((Format([CasingOD],'00.000'))<>0));

Cheers,

 

[L'apprentis]

I have a problem with the combo box, after using it a couple of time, the format function disapear and I need to reinsert it in the row source query. I don't understand??? (The combobox is unbound and located on a single form)

 

[L'apprentis]

I have changed the row source and it seems to work fine, but now I get a problem when i am adding the ORDER BY clause:

SELECT DISTINCT  Format(CasingOD,"#,##0.000") AS OD  FROM TblCasing WHERE CasingOD<>0 ORDER BY OD;

I get the following error message: ORDER BY clause (OD) conflicts with DISTINCT.

 

[L'apprentis]

I managed to make it work, i forgot to use the Format again in the Order By Cllause:

SELECT DISTINCT Format(CasingOD,"#,##0.000") AS OD 
FROM TblCasing 
WHERE (((TblCasing.CasingOD)<>0)) 
ORDER BY Format(CasingOD,"#,##0.000");

Now I am facing a different kind of problem, the record are put in order as if they were text field not numeric field:
1,11,15,2,5,62,63,71,8...instead of 1,2,5,8,11,15,62,63,71
Is there a simple way to change the way numeric field are ordered?

 

[ghudson]

Add another hidden field for the CasingOD field to your SQL and set the order to accending for the newly hidden CasingOD field but do not use the format function on the new field that you are sorting by.

 

[L'apprentis]

I really don't want to be a pain but how do you add an hidden field to a query?
I am really sorry for being annoying.

 

[ghudson]

You just add the field of your choice but you do not display [deselect the Show box] it in your combo box. Ensure it is on the far left of your fields.

 

[L'apprentis]

I see what you mean, this is quite an interesting and simple method to get round it indeed:

SELECT DISTINCT TblCasing.CasingOD, Format([CasingOD],"#,##0.000") AS OD 
FROM TblCasing WHERE (((Format([CasingOD],"#,##0.000"))<>0)) 
ORDER BY TblCasing.CasingOD;

How would you do then if you were in the situation where the field has a text format and always starts with a number and you would like to view them in numerical order?

 

[ghudson]

Have not had the need for that one. I would just start playing around with the different options until I got it right. Good luck!

 

[L'apprentis]

I see...Thanks anyway for your help.
It has been really helpfull.

 

[L'apprentis]

Ghudson, if one day you need to deal with what we discussed you might want to have a look at the following thread where you'll find a great sample data base under Sort.zip.
http://www.access-programmers.co.uk/forums/showthread.php?t=83419

 

[ghudson]

Thank you!