I've come across a curious problem in Access 2003:

If I set the OrderBy Property to:

Me.OrderBy = "dbo_Task.sim_date, dbo_task.name"

it works fine.

However, if I do this:

Me.OrderBy = "DateValue([dbo_Task.sim_date]), dbo_task.name"

nothing happens.

I've checked the help file and there is nothing there that explicitly
says this is not do-able, though it does not mention using function
calls in the string. Certainly, you can use function calls in the ORDER
BY clause of an Access query without problems, so I don't understand why
it simply fails to work here.

Any ideas?

-Jacob

Well, in what you posted the brackets are incorrect.

Me.OrderBy = "DateValue([dbo_Task].[sim_date]), [dbo_task].[name]"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 12/15/2010 4:06 PM, Jacob wrote:

That's irrelevant - brackets are stripped anyway by Access when it
inserts the statement into the property.

On 12/15/2010 4:06 PM, Jacob wrote:

--

-Jacob

On Dec 15, 4:06*pm, Jacob <paumo... (AT) gmail (DOT) com> wrote:
Those are indeed curious results. I was able to confirm the behavior
you saw in A2003, but not in A97. It looks like when using code that
sets the OrderBy property, A2003 does some kind of verification to
ensure that the fields placed in the property are fields from the
RecordSource. After a little experimentation, it seemed that in
A2003, whatever was parsed in comma delimited fashion was considered a
field name unless it had brackets around the field name. So,

OrderBy = "Score", OrderBy = "[Score]", OrderBy = "Score, Player" and
OrderBy = "[Score], Player"

all did the correct thing in both versions, but:

OrderBy = "-Score, Player" errored in A2003 only, thinking that '-
Score' was the name of a field.

OrderBy = "-[Score], Player" ignored the Score field entirely and
placed Player as the field name in the OrderBy property, but A97
respected the minus sign in the field expression, whether brackets
were used or not.

OrderBy = "Int([Score]), Player" behaved similarly.

In summary, the OrderBy property behaves differently in A2003 than it
did in A97. Any expression used in A2003's OrderBy property will
cause an error if brackets do not surround the field name. When
brackets surround the field name, A2003 will not cause an error, but
it will ignore the entire expression when placing fields into the
OrderBy property.

I usually define the RecordSource on-the-fly, so it would have been
unlikely for me to come across the difference between the two versions
on my own. Maybe try using 'AS' to give the field expression a name
in the RecordSource and entirely avoid expressions in the OrderBy
property.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com