Re: "Sort" a combination of letters and numbers e.g. 20, 21a,200,

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Again,

No matter what I do to the underlying query of my form I cannot get the
SortVal to work. It will not sort by area including both the alpha and
numeric values and it's not sorting the Tag exactly either. Strictly
speaking the Tag is alphanumeric, but with an input mask of GL/ followed by
numbers as in GL/0001 or GL/1561. The Form Sort will sort the Tag field
correctly but not the Area field. The SortVal calculated field is not doing
either.

I know I've got this sorted out in a report, but sometimes I will just need
to view the data (with more fields than the report contains). By the way,
have I mentioned that my form is in Data *** view?

Help!

Crazy Lady

"Marshall Barton" wrote:

> But, Lady, that's not "code", it's just a trivial expression
> in one line of the Sorting and Grouping window. The second
> one is just a field name selected from the field/expression
> drop down list. It sounds like you may be unfamiliar with
> using Sorting and Grouping (View menu). If so, take a
> little time out from making extra work for yourself and get
> familiar with this powerful, but easy to use feature of
> Access reports. Then give my earlier suggestion a try.
>
> Note that, unlike forms, anything beyond a trivial report
> **must** use Sorting and Grouping to specify it's sorting.
> Changing this according to user input requires a small
> amount of real VBA code. If your up to it, post back with
> specific details for a specific report and I'll provide some
> code for you to try.
>
> As to your other question, you can use a slightly more
> complex expression (not code) to cause your form's record
> source **query** to be sorted in either of two ways. First,
> some form's control (text box, check box, combo box, etc)
> should be used to allow the users to specify which sort they
> want. Since you didn't provide much in the way of clues to
> your requirements, I can only provide the general idea.
> Add a calculated field to the query:
> SortVal:IIf(Forms!someform.somecontrol = val1,fieldA,fieldB)
> --
> Marsh
> MVP [MS Access]
>
>
> Crazy Lady wrote:
> >Thanks for replying Marshall. I don't think this helps me, because I don't
> >know how to use code. I only have one piece of code in my database so far,
> >and that was given to me on this site.
> >
> >If you wouldn't mind eleborating I would be very grateful.
> >
> >You may be able to help further. I am wanting to sort in a report mainly,
> >but possibly also in a form and there are two possible sort criteria. I may
> >want to sort by this field or by another. I was intending to create two
> >reports sorted in those two ways and have a prompt ask which report to show.
> >Is there a better way?
> >
> >
> >> Crazy Lady wrote:
> >> >The numbers represent buildings and are stored as text. The Sort Ascending
> >> >and Decending option sorts the numbers only first and then the
> >> >numbers/letters. I want the list to end up as 20, 21a, 105c, 200.
> >>
> >>
> >"Marshall Barton" wrote:
> >> You can sort on expressions, not just fields.
> >>
> >> Try setting two levels of sort in Sorting and Grouping to:
> >> =Val(numfield)
> >> numfield
>
.

Quantcast