Hi again Roger,
Pleased my answer helped you. Yes you can do a similar thing with the combo
box but with a little variation so that you get the data for the combo in the
first column and the primary and secondary sorts in the correct order from
the left hand side.
For this example assume that [NumbersAndText] is the name of the field that
you want to show in the combo box.
In the Row Source property for the combo box, Click the button with the
three dots at the end of the field to open the query builder for the combo
box.
Select the table from Show tables and then close Show tables dialog box.
Drag the field [NumbersAndText] into the first column of the matrix. It is
the first column that shows in the combo box and therefore this field must be
in the first column. However, DO NOT set the sort for this column.
In the second column create a virtual field =Val([NumbersAndText]). Set the
sort to Ascending for this field and uncheck the Show box. Because this is
the first sort form the left, it becomes the primary sort.
Now drag [NumbersAndText] from the table into the third column. (Before you
ask, that is right; you have NumbersAndText twice.). Set the sort to
Ascending for this field and uncheck the Show box.
Now you have the data in the first column which is what shows in the combo
box, the primary index on the numbers in the second column (which is the
first sort from the left) and the secondary index is the combined numbers and
text field in the third column (which is the second sort from the left).
Close the close X to close the query builder and select save at the prompt.
This saves the query embedded with the combo box.
As an added extra, if you click on the Save toolbar button while you have
the query builder open for the combo box, the query saves as a query and the
query name becomes the Row source instead of the query being embedded in the
combo box. (I don't so this myself because I like to keep it embedded in the
combo but thought you might like the bit of info.)
I have tried to explain it as simply as possible to make it easy for you.
However, it might be overkill because I don't know how competant you are with
Access. However, feel free to get back to me if you experience any problems.
--
Regards,
OssieMac
Roger Bell said:
Thanks OssieMac for your concise and prompt answer. Is there any way you can
use the Val function in a Combo Box so that the order is the same as the
report: 1A, 1B etc.
Thanks again
OssieMac said:
I should have also said that the virtual field needs to be to the left of the
real field that has both both numeric and text. This is so the numeric sort
becomes the primary sort. You will also need to have the text and numbers
field sort set to ascending so that it becomes the secondary sort otherwise
your alpha characters may not be in order.
--
Regards,
OssieMac
:
Create a virtual field in the report query as Val([YourField])
Set sort for this field to Ascending.
Val function reads the text up to last numeric and creates a real number so
your sort works as required.
Don't know how much instruction you need but get back to me if you need more
info to do this.
--
Regards,
OssieMac
:
I have a Report that is sorted on Plot Numbers which is a Text Field and
includes Entries like 1A, 1B, 2A, 2B etc.
The problem is that when I run the Report the entries list as 10A, 10B, then
down the list is 1A etc. I would like the report to list 1A, 1B, 2A, 2B, 3A,
3B etc in that order.
Is there any way I can achieve this?
Thanks for any help
