By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 992 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Orderby set by code causes stupid access 2000 behaviour

P: n/a
Hi there,

After spending several hours trying all I could imagine and search for in
Google I gave up. In a continuous form I want to sort the choosen column by
clicking the header (label) of that column. I even want to sort up and down
if one clicks again on the same header. No problem so far, all works well
for one column. Now I want to sort on the first choosen column ASC or DESC
and additionally on a second column ASC.

I use the code below that causes with 'DESC' serious problems because Access
puts stupidly extra [ ] signs around the first part (I have no idea which
idiot programmed all that [[[]]] ((())) code around every part of all
sql-like stuff) but anyway in this case it even causes errors.

Volgorde = IIf(Mid(VolgordeWas, LengteNaam + 2, 2) = "DE", " ASC ", "
DESC ")
Me.OrderBy = "AlbumTitel" & Volgorde & " , Nummer ASC"
Me.OrderByOn = True

When I type in the 'direct-screen' the following all works fine:
Forms("MyForm").OrderBy = "AlbumTitel DESC, Nummer ASC"
? Forms("MyForm").OrderBy
AlbumTitel DESC, Nummer

However after running the code after clicking the label Access makes that
the results are like an earthquake (I can not figure out what order is now
applied; likes a random order or so) and in the 'direct-screen' this shows :
? Forms("MyForm").OrderBy
[AlbumTitel DESC], Nummer

Any one any solution how to tell Access that the orderby-property should be
"AlbumTitel DESC, Nummer" and NOT "[AlbumTitel DESC], Nummer"

Thanks in advance,

Dutchy
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi, Dutchy.
Orderby set by code causes stupid access 2000 behaviour I use the code below that causes with 'DESC' serious problems because Access puts stupidly extra [ ] signs around the first part (I have no idea which
idiot programmed all that [[[]]] ((())) code around every part of all
sql-like stuff) but anyway in this case it even causes errors.
Stupid behavior? It was a marketing decision to prevent the huge number of
errors that will automatically occur as soon as strings are parsed by the
parser whenever a user places a space in an identifier. May the fleas of a
thousand camels infest the armpits of anyone who doesn't know why one should
_never_ put a space in a variable name, yet sits at the keyboard typing
spaces in variable names and identifiers in order to make the table names,
field names and control names easier for humans to read.

To work around the marketers' solution, trim the extra spaces around the
"ASC" and "DESC" then add a single space after the "AlbumTitel" string so
that it reads "AlbumTitel " instead. Now, even if someone adds a space in
the field names, your code will still work because Access will put the
brackets in the right places.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Dutchy" <bl*@bla.bla> wrote in message news:ch**********@reader13.wxs.nl... Hi there,

After spending several hours trying all I could imagine and search for in
Google I gave up. In a continuous form I want to sort the choosen column by clicking the header (label) of that column. I even want to sort up and down if one clicks again on the same header. No problem so far, all works well
for one column. Now I want to sort on the first choosen column ASC or DESC
and additionally on a second column ASC.

I use the code below that causes with 'DESC' serious problems because Access puts stupidly extra [ ] signs around the first part (I have no idea which
idiot programmed all that [[[]]] ((())) code around every part of all
sql-like stuff) but anyway in this case it even causes errors.

Volgorde = IIf(Mid(VolgordeWas, LengteNaam + 2, 2) = "DE", " ASC ", "
DESC ")
Me.OrderBy = "AlbumTitel" & Volgorde & " , Nummer ASC"
Me.OrderByOn = True

When I type in the 'direct-screen' the following all works fine:
Forms("MyForm").OrderBy = "AlbumTitel DESC, Nummer ASC"
? Forms("MyForm").OrderBy
AlbumTitel DESC, Nummer

However after running the code after clicking the label Access makes that
the results are like an earthquake (I can not figure out what order is now
applied; likes a random order or so) and in the 'direct-screen' this shows : ? Forms("MyForm").OrderBy
[AlbumTitel DESC], Nummer

Any one any solution how to tell Access that the orderby-property should be "AlbumTitel DESC, Nummer" and NOT "[AlbumTitel DESC], Nummer"

Thanks in advance,

Dutchy

Nov 13 '05 #2

P: n/a
Most valued Gunny !! :-)

I am amazed ! Your eye is as of a thousand eagles !

I knew that I had done it sometime ago but could not find it anymore. I
think I did left out there (by accident?) that nasty extra space without
knowing of this horror. May the hairs of a thousand camels keep you warm any
winter !!

I really don't use any spaces since I was making applications for a couple
of months (that was in 1998 I think). I even remove them if Access puts them
standard in subform-names and use underscores and/or uppercase characters in
long names as 'AlbumTitel'. I am aware of all the sudden crazyness that
occurs everey now and then.

But still I wonder why Access can't see always that DESC is a reserved word
in this case and therefore it shouldn't matter how many spaces you add after
the fieldname and before that reserved word. If you construct an
SQL-select-from-etc- phrase you can put in 'a thousand' spaces without any
problems !!

Furthermore if you just use one column, that extra space is of no
importance, and it even leaves the 'ASC' part from the orderby-string !!?
After adding ' ,secondfieldname ' it suddenly decides it needs that
brackets. And by the way it places them ' [AlbumTitel DESC], Nummer ' you
might expect that it recognized well that the first fieldname must me
ordered DESC so why whining about its own creation after that most
intelligent descision?!

Anyway thank you very much ! After fixing that space there occur no
brackets any more, not even with DESC .

--
Dutchy
'69 Camaro <Fo**************************@Spameater.orgZERO_SP AM> schreef in
berichtnieuws bM5%c.6417$vI2.3645@trnddc02...
Hi, Dutchy.
Orderby set by code causes stupid access 2000 behaviour
I use the code below that causes with 'DESC' serious problems because

Access
puts stupidly extra [ ] signs around the first part (I have no idea which idiot programmed all that [[[]]] ((())) code around every part of all
sql-like stuff) but anyway in this case it even causes errors.


Stupid behavior? It was a marketing decision to prevent the huge number

of errors that will automatically occur as soon as strings are parsed by the
parser whenever a user places a space in an identifier. May the fleas of a thousand camels infest the armpits of anyone who doesn't know why one should _never_ put a space in a variable name, yet sits at the keyboard typing
spaces in variable names and identifiers in order to make the table names,
field names and control names easier for humans to read.

To work around the marketers' solution, trim the extra spaces around the
"ASC" and "DESC" then add a single space after the "AlbumTitel" string so
that it reads "AlbumTitel " instead. Now, even if someone adds a space in
the field names, your code will still work because Access will put the
brackets in the right places.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Dutchy" <bl*@bla.bla> wrote in message news:ch**********@reader13.wxs.nl...
Hi there,

After spending several hours trying all I could imagine and search for in Google I gave up. In a continuous form I want to sort the choosen column

by
clicking the header (label) of that column. I even want to sort up and

down
if one clicks again on the same header. No problem so far, all works well for one column. Now I want to sort on the first choosen column ASC or DESC and additionally on a second column ASC.

I use the code below that causes with 'DESC' serious problems because

Access
puts stupidly extra [ ] signs around the first part (I have no idea which idiot programmed all that [[[]]] ((())) code around every part of all
sql-like stuff) but anyway in this case it even causes errors.

Volgorde = IIf(Mid(VolgordeWas, LengteNaam + 2, 2) = "DE", " ASC ", "
DESC ")
Me.OrderBy = "AlbumTitel" & Volgorde & " , Nummer ASC"
Me.OrderByOn = True

When I type in the 'direct-screen' the following all works fine:
Forms("MyForm").OrderBy = "AlbumTitel DESC, Nummer ASC"
? Forms("MyForm").OrderBy
AlbumTitel DESC, Nummer

However after running the code after clicking the label Access makes that the results are like an earthquake (I can not figure out what order is now applied; likes a random order or so) and in the 'direct-screen' this

shows :
? Forms("MyForm").OrderBy
[AlbumTitel DESC], Nummer

Any one any solution how to tell Access that the orderby-property should

be
"AlbumTitel DESC, Nummer" and NOT "[AlbumTitel DESC], Nummer"

Thanks in advance,

Dutchy



Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.