OrderBy code not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ctdak - 04 Dec 2005 03:24 GMT

I have a builder query which is sorting on the last two characters of a text
field, "Right([Fieldname],2)". This has always worked fine in the query.
However, I am trying to replace this query with code in a Form_Open event, as
follows:

OrderByOn = True
OrderBy = "Right([Fieldname],2)"

This does not work, presumeably because the form's OrderBy property will
only take field names and not expressions including field names. So, how can
I code a replacement for this builder query?

Reply to this Message

Allen Browne - 04 Dec 2005 03:29 GMT

Try swapping the 2 lines, so you set OrderByOn after OrderBy.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a builder query which is sorting on the last two characters of a
>text
[quoted text clipped - 10 lines]
> can
> I code a replacement for this builder query?

Reply to this Message

ctdak - 04 Dec 2005 05:47 GMT

Allen,
That didn't work either. Same result.
ctdak

> Try swapping the 2 lines, so you set OrderByOn after OrderBy.
>
[quoted text clipped - 12 lines]
> > can
> > I code a replacement for this builder query?

Reply to this Message

Allen Browne - 04 Dec 2005 07:03 GMT

Okay, just tried it, and I am getting the same result as you.

Access silently rejects the OrderBy setting. If you type the value into the
property in design view, the property remains until you set OrderByOn to
True. Then the property is lost.

It looks like Access can't handle the property if it contains the function
name. I have no problem with that, but I do have a problem with it failing
silently, where it does not notify you that the property setting failed. In
my view, that constitutes a bug.

Here's a hint as to what goes wrong. If you try:
Forms(0).OrderBy = "Rnd()"
Forms(0).OrderByOn = True
and then ask:
? Forms(0).OrderBy
you get the answer:
[Rnd()]
The square brackets indicate that Access has turned the function call into a
parameter. The problem is therefore with the interpretation of the value
assigned to the property.

As a workaround, you could reassign the RecordSource of the form. For
example, if your form is bound to:
SELECT Client.* FROM Client;
you could use:
Me.RecordSource = "SELECT Client.* FROM Client ORDER BY
Right(Client.Surname,2);"

(Of course, all of this is highly inefficient, and I suspect that any design
that needs to do this should probably have the last 2 characters of the
field as a separate field so that the data is atomic.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
> That didn't work either. Same result.
[quoted text clipped - 20 lines]
>> > can
>> > I code a replacement for this builder query?

Reply to this Message

Dirk Goldgar - 04 Dec 2005 15:44 GMT

> As a workaround, you could reassign the RecordSource of the form. For
> example, if your form is bound to:
> SELECT Client.* FROM Client;
> you could use:
> Me.RecordSource = "SELECT Client.* FROM Client ORDER BY
> Right(Client.Surname,2);"

Or have the query create the calculated field ...

SELECT Client.*, Right(Client.Surname, 2) As LastTwo
FROM Client

Then you could set the OrderBy property of the form dynamically:

Me.OrderBy = "LastTwo"
Me.OrderByOn = True

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

ctdak - 06 Dec 2005 07:47 GMT

Thanks Allen. This confirms that I'm not going crazy, but rather Access
isn't handling what I wanted to do. Since the form in question needs to have
these records sorted this way every time it's opened, your solution of
reassigning the RecordSource with ORDER BY... is the easiest one. This works
for me.

(As far as efficiency - I agree that the design of the data could be
changed, but the number of records here is pretty small and there is a good
reason for leaving it this way in this case.)

Thanks again,
ctdak

> Okay, just tried it, and I am getting the same result as you.
>
[quoted text clipped - 53 lines]
> >> > can
> >> > I code a replacement for this builder query?

Reply to this Message