Using Access 2010. Can I use a string function in an OrderBy? Here's
my code so far:

Me.OrderBy = "Left(Block,5)"

Block is a column name in my table. I'm getting an error message that
the column Block cannot be found.

If I change it to this:

Me.OrderBy = "Block"

then all is well.

What I'm trying for is this, but thought I'd take baby steps first:

Me.OrderBy = "Left(Block,5), Val(Mid(Block,6))

I want "Block1", "Block2", ..."Block10" to sort in the expected order
and not in this order: "Block1", "Block10", "Block2".

Thanks for any help or advice.

On 30/03/2011 21:49:14, emanning wrote:
"Block10" come from. Might make more sense of your question if we know this.

BTW I presume you mean Field Name rather than column name.

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Access users, developers, and even Microsoft have, since Access' beginnings,
used "Field-Record" and "Column-Row" nomenclature interchangeably. Which of
the two any given person uses has been just a matter of personal preference,
and many of us use both at different times. What point are you trying to
make, or what difference are you thinking there is?

Larry Linson
Microsoft Office Access MVP

On 31/03/2011 02:25:09, "Access Developer" wrote:
That's a detail, Larry.
Just think if everyone used the same words to describe the same thing, then
discussion would be clearer .... and more booring. The relevent bit is how
does "Block" become "Block1", "Block2" etc.

Phil

emanning wrote:
Every entry in the [Block] column starts with the string "Block"??? Wow, a
very common mistake made by beginners is to store data (that should be in
rows) in metadata (table or field names - tables named
"2008Sales","2009Sales", etc., or fields named "JulySales", "AugSales",
etc.). You seem to have committed a new error: storing metadata in data,
LOL! Think how much simpler this problem would be for you if you started
with a Number column called block and simply stored the block numbers.

Now I might have been misled by your description - perhaps there might be
different strings in the frist 5 characters besides "Block", hence the need
to order by that string. If so, you now see how your attempt to simplify
the problem has actually confused it for us. It's better to show us a few
sample rows of actual data (relevant fields only) instead of concocting an
example to "simplify" the problem.

Anyways, I suspect your problem might be due to a failure to enclose the
field name in brackets []:
="Left([Block],5)"
but it's been so long I actually worked with Access forms/reports that I
might be wrong.

On Mar 31, 6:29*am, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
It's only one column called "Block". It's the users choice to enter
what they want in that column. For now they choose "Block1",
"Block2", etc. Until they tell me different, it's up to me to display
this column in a continuous form in the following order:

Block1
Block2
...
Block10

instead of how Access wants to sort it, which is:

Block1
Block10
Block2
.....

The brackets make no difference.

I'll try a query instead. I was curious if I could use the string
functions in OrderBy.

On Mar 31, 3:28*pm, emanning <emann... (AT) kumc (DOT) edu> wrote:
Hi Emanning,

You could change "Block1" to "Block01", "Block2" to "Block02", to have
it sorted in the way you want it.


Imb.

On Mar 31, 8:47*am, imb <im... (AT) onsmail (DOT) nl> wrote:
Thanks for your reply. I thought about that too. But I think I'll
just go the query route.

emanning wrote:
So you're letting your users dictate your database design? Recipe for
disaster, that ... they should give you the requirements and you should
choose the design. If this table has thousands of records, performance will
suffer by the need to calculate the sorting value.
I would explain to them the pitfalls of that approach and, if they insisted
on entering what they wanted, I would create a separate Number field to
store the block number calculated in an AfterUpdate event.

The only time I used OrderBy was when I needed dynamic sorting - I much
preferred to do the sorting in the query that provided the records for the
form. I never tried to use any functions in the OrderBy - does the
documentation give any clue? Are you required to provide a list of columns
only? ... ok, I'm back from looking it up in A2003 online help and yes,
that's the issue in a nutshell: "The OrderBy property is a string expression
that is the name of the field or fields on which you want to sort records."
So, what you can do is create a calculated field in the query that supplies
the records for the form/report and list that calculated field in the
OrderBy property.

I'm not the one who brought up "a detail". I figured there must be some
reason you said "BTW I presume you mean Field Name rather than column name."
and wondered what differentiation you made between Fields and Columns.

My understanding, from emanning's post, was that "Block1", "Block10", "Block
2", etc. were
the content of the Field/Column named "Block".

Larry Linson
Microsoft Access MVP

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote