Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #697 > unrolled thread

using OrderBy and string functions

Back to article view | Back to comp.databases.ms-access

Contents

  using OrderBy and string functions emanning <emanning@kumc.edu> - 2011-03-30 13:49 -0700
    Re: using OrderBy and string functions "Phil" <phil@stantonfamily.co.uk> - 2011-03-30 22:59 +0100
      Re: using OrderBy and string functions "Access Developer" <accdevel@gmail.com> - 2011-03-30 20:25 -0500
        Re: using OrderBy and string functions "Phil" <phil@stantonfamily.co.uk> - 2011-03-31 09:34 +0100
          Re: using OrderBy and string functions "Access Developer" <accdevel@gmail.com> - 2011-03-31 21:14 -0500
    Re: using OrderBy and string functions "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-03-31 07:29 -0400
      Re: using OrderBy and string functions emanning <emanning@kumc.edu> - 2011-03-31 06:28 -0700
        Re: using OrderBy and string functions imb <imb4u@onsmail.nl> - 2011-03-31 06:47 -0700
          Re: using OrderBy and string functions emanning <emanning@kumc.edu> - 2011-03-31 07:35 -0700
        Re: using OrderBy and string functions "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-03-31 09:56 -0500
          Re: using OrderBy and string functions "Access Developer" <accdevel@gmail.com> - 2011-03-31 21:26 -0500

#697 — using OrderBy and string functions

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.

[toc] | [next] | [standalone]

#700

On 30/03/2011 21:49:14, emanning wrote:
> 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.
> 
If "Block" is a field name in your table, where to "Block1" ,"Block2" ....
"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

[toc] | [prev] | [next] | [standalone]

#704

"Phil" <phil@stantonfamily.co.uk> wrote

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

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 

[toc] | [prev] | [next] | [standalone]

#706

On 31/03/2011 02:25:09, "Access Developer" wrote:
> "Phil" <phil@stantonfamily.co.uk> wrote
> 
> > BTW I presume you mean Field Name rather than column name.
> 
> 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
> 
> 

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

[toc] | [prev] | [next] | [standalone]

#736

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@stantonfamily.co.uk> wrote in message 
news:in1eak$n8o$1@speranza.aioe.org...
> On 31/03/2011 02:25:09, "Access Developer" wrote:
>> "Phil" <phil@stantonfamily.co.uk> wrote
>>
>> > BTW I presume you mean Field Name rather than column name.
>>
>> 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
>>
>>
>
> 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 

[toc] | [prev] | [next] | [standalone]

#707

emanning wrote:
> 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.

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.

 

[toc] | [prev] | [next] | [standalone]

#708

On Mar 31, 6:29 am, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
> emanning wrote:
> > 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.
>
> 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.

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.

[toc] | [prev] | [next] | [standalone]

#710

On Mar 31, 3:28 pm, emanning <emann...@kumc.edu> wrote:
> On Mar 31, 6:29 am, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
>
>
>
>
>
> > emanning wrote:
> > > 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.
>
> > 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.
>
> 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.- Hide quoted text -
>
> - Show quoted text -

Hi Emanning,

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


Imb.

[toc] | [prev] | [next] | [standalone]

#715

On Mar 31, 8:47 am, imb <im...@onsmail.nl> wrote:
> On Mar 31, 3:28 pm, emanning <emann...@kumc.edu> wrote:
>
>
>
>
>
>
>
>
>
> > On Mar 31, 6:29 am, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
>
> > > emanning wrote:
> > > > 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.
>
> > > 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.
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Hi Emanning,
>
> You could change "Block1" to "Block01", "Block2" to "Block02", to have
> it sorted in the way you want it.
>
> Imb.

Thanks for your reply.  I thought about that too.  But I think I'll
just go the query route.

[toc] | [prev] | [next] | [standalone]

#717

emanning wrote:
>>
>> 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.
>
> 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.

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.

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

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.

[toc] | [prev] | [next] | [standalone]

#737

"Bob Barrows" <reb01501@NOyahooSPAM.com> wrote

 >  So you're letting your users dictate your
 > database design? Recipe for disaster, that ...

I agree with your conclusion but understood that emanning named the 
Field/Column "Block" and the users chose the content.

It's really a matter of the users not understanding that alphanumeric sorts 
differently from numeric.

And, if the users choose the content, and he optimizes for "Block" followed 
by a numeric, there's nothing that would keep the users from choosing some 
other combination of alphabetic and numeric and not liking the way those 
sorted...  suppose a user entered "ABC010XYZ1" and another chose 
"ABC1XYZ02"... how would they expect that to be sorted? The absolute best 
solution is to give the users a short course on what they don't understand, 
and why they aren't seeing what they expect -- teach them the difference 
between alphanumeric sort order and numeric sort order.

If, and only if, the users can specific a logical and consistent order in 
which they want arbitrary alphanumeric values to sort (difficult, and 
probably impossible, to cover all possible cases) can you handle a "custom 
sort order".

 Larry Linson
 Microsoft Office Access MVP 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web