473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Orderby set by code causes stupid access 2000 behaviour

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
2 3319
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: G.Gerard | last post by:
Hello I am setting the Order By property of a subreport on the On Open Event of the subreport and I am getting the following error: error number 2101 ( The setting you entered isn't valid...
6
by: P. Emigh | last post by:
By default in more recent versions, Access forms keep users' last sort request in the "orderby" property. That can slow things down considerably, especially when the last user has chosen a...
8
by: Adam Louis | last post by:
I would like help resolving this problem. I'm a novice who's been hired to query a hospital database and extract useful information, available to me only in a dynamically generated, downloadable...
2
by: DFS | last post by:
Access97: I opened a report, then opened a small form that floats above it and lets you choose report fields to sort on (by issuing the OrderBy statement). Well, apparently OrderBy actually...
9
by: J Bunton | last post by:
My Access 97 applictions seem to have recently become very Cpu useage hungry. When opened they go to almost 100% useage on the peformance monitor. After several seconds this returns to near...
1
by: chrissmith_76_Fed_Up_With_Spam | last post by:
Hello all, I am using Access 2002, with file format of Access 2000, and am experiencing a problem. I have a subform that is shown in datasheet view for users to edit data direct to a table. ...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
2
by: tim otero | last post by:
I should be able to figure this one out, but it's late: I'm trying to sort a report by two fields. I can do it easily if I hard-code the fields. However, I want to get user input and store the...
7
by: pbd22 | last post by:
Hi. I really need some advice on fine-tuning a stored procedure that is the meat of the search logic on my site. Customers are allowed to save searches, which dumps the search logic in a table...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.