Sortable percentage in query

[flaviooooo]

Hey,

this seems like such a simple question, but I can't get it to work...

I have a query that does a calculation. Now I would like to show this calculation through a new query, as a percent.
I do this as follows:

SELECT Format([attr_perc]/100,"Percent") AS ATTR_P, Q_ATTR.StaffID, Q__ATTR.ATTR_PERC
FROM Q__ATTR;


It shows perfectly the percentages and all but when I sort on it, it sorts like a textfield.
99% is at the top instead of 100%...

Any idea on how to fix?

 

[Remou]

Can you sort on the unformatted attr_perc rather than the formatted field?

 

[dhookom]

It rarely makes much sense to perform your formatting in a query. It generally just leads to issues such as yours. You are much better off formatting your values in the control sources or format properties of you text boxes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[flaviooooo]

Hey guys,

I tried formatting it in the form I want to show, but unfortunately it has no effect...

The thing is a bit tricky though:

I created a query, with an ID and the formula (unformatted in the query, so it reads something like 0,89563254699)

I then need to show it on a form, but it is shown in a combobox, with ID as column 1, formula as column 2. Column 1 has width 0 so it isn't shown, but is still the bound column.

Maybe this is why it doesn't format as a percent? I can't find another reason or solution...

 

[dhookom]

Is the SQL you provided, the Row Source of a combo box or the Record Source of a form? Did you try Remou's suggestion:

SELECT Format([attr_perc]/100,"Percent") AS ATTR_P, Q_ATTR.StaffID, Q__ATTR.ATTR_PERC
FROM Q__ATTR
ORDER BY aatr_perc;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[flaviooooo]

Hey,

the Id field would be the record source, while my complete query containing ID, Perc would be the row source of the combo. That way I can linke the Perc to my form, while it still remains editable

The code you show works fine in my query, but I would like the form to be dynamically sortable