A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Form doesn't sort records per the underlying query



 
 
Thread Tools Display Modes
  #41  
Old April 9th, 2009, 04:13 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...

When I click "About Microsoft Access" I get: Microsoft Access 2000
(9.0.3821 SR-1)


I don't think you have applied all service packs, so you may want to visit
Office Update and do that. However, you have appplied the most important
one (SR1), and I don't have any reason to believe any later service packs
would affect the issue you're experiencing.

the WHERE statement is quite complex and uses aggregate functions. Could
the use of aggregate functions in a WHERE statement do "more" than just
filter the query its acting on?


I don't believe so. I would be very surprised if the complexity of the
where-condition had any effect on the form's sort sequence. On the other
hand, I don't yet understand what accounts for the behavior you report, so I
won't make an absolute statement.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #42  
Old April 9th, 2009, 04:16 PM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...

I did BOTH of those things (actually the property sheet shows my OrderBy
property is blank). I did put the line of code in my OnOpen event but
that didn't fix the problem.

"Dirk Goldgar" wrote in message
...

I expect that Albert's advice -- open the form in design view, clear the
Order By property, and save it -- will have solved your problem. If not,
you could add code to the form's Open event to set the OrderByOn property
to False:

Me.OrderByOn = False

... and that should also solve your problem.



That perplexes me, and I'll have to do some experimenting to see if I can
reproduce your problem. There's always the possibility that yuor form is
corrupted in some way, but I think that is much less llikely than that just
haven't yet hit on the cause of the behavior.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #43  
Old April 9th, 2009, 04:49 PM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access
Fred
external usenet poster
 
Posts: 1,451
Default Form doesn't sort records per the underlying query

I'm not in the same expertise league as these other guys but here's something
to try that that only takes 30 seconds and is a good thing to do anyway.
Open the query in design view and in it's properties (not the SQL or grid)
make sure the "order by" property is empty.
  #44  
Old April 9th, 2009, 05:28 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Form doesn't sort records per the underlying query

John S. Ford, MD wrote:
When I click "About Microsoft Access" I get: Microsoft Access 2000
(9.0.3821 SR-1)
It seems like the WHERE argument disables the query's inherent sort routine.
I'm surprised that the WHERE argument can do this but let me ask you one
question.

the WHERE statement is quite complex and uses aggregate functions. Could
the use of aggregate functions in a WHERE statement do "more" than just
filter the query its acting on?



PMFJI, but I am concerned that you do not have a clear
picture of the query. You said,:
"the WHERE statement is quite complex
and uses aggregate functions",
BUT, AFAIK, a WHERE clause can not contain aggregate
functions. That is the province of the HAVING clause.
Getting mixed up on which is which can cause the query to
produce an unexpected result.

I don't see how any of this can affect the sorting though.

--
Marsh
MVP [MS Access]
  #45  
Old April 9th, 2009, 05:47 PM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"Fred" wrote in message
...
I'm not in the same expertise league as these other guys but here's
something
to try that that only takes 30 seconds and is a good thing to do anyway.
Open the query in design view and in it's properties (not the SQL or grid)
make sure the "order by" property is empty.



I would certainly recommend doing this, even though I can't (so far)
reproduce any effect on the form of the query's Order By property. Still,
I'm using Access 2003, not 2000, so there could be a difference between my
system and Dr. Ford's.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #46  
Old April 9th, 2009, 06:42 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"Marshall Barton" wrote in message
...

PMFJI, but I am concerned that you do not have a clear
picture of the query. You said,:
"the WHERE statement is quite complex
and uses aggregate functions",
BUT, AFAIK, a WHERE clause can not contain aggregate
functions. That is the province of the HAVING clause.
Getting mixed up on which is which can cause the query to
produce an unexpected result.



I assumed he meant *domain* aggregate functions, such as DLookup -- although
I could conceive of having an aggregate subquery.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #47  
Old April 10th, 2009, 02:33 AM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access
John S. Ford, MD
external usenet poster
 
Posts: 215
Default Form doesn't sort records per the underlying query

Dear Fred,

I tried that. Both the filter and order by properties are blank.

John


"Dirk Goldgar" wrote in message
...
"Fred" wrote in message
...
I'm not in the same expertise league as these other guys but here's
something
to try that that only takes 30 seconds and is a good thing to do anyway.
Open the query in design view and in it's properties (not the SQL or
grid)
make sure the "order by" property is empty.



I would certainly recommend doing this, even though I can't (so far)
reproduce any effect on the form of the query's Order By property. Still,
I'm using Access 2003, not 2000, so there could be a difference between my
system and Dr. Ford's.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.