OrderBy code not working

[Guest]

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?

 

[Allen Browne]

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

 

[Guest]

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
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?

 

[Allen Browne]

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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?

 

[Dirk Goldgar]

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

 

[Guest]

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.

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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?