Sorting Problem

[Randy]

I am having a sorting problem on a form. No subforms.
Continuous Page Main Form. The query works fine pulling up a datasheet
but when I attach it to a button on a form. It brings up today's
records but does not sort.

What do I need to change.

Here is the code for the query.

SELECT MAIN.DATE1, MAIN.WORKER, MAIN.TIME
FROM MAIN
WHERE (((MAIN.DATE1)=Date()))
ORDER BY MAIN.DATE1, MAIN.WORKER, MAIN.TIME;

Thanks for any help..............

[Alan Webb]

Randy,
What about:
SELECT MAIN.TIME, MAIN.WORKER
FROM MAIN
WHERE (((MAIN.DATE1)=Date()))
ORDER BY MAIN.TIME, MAIN.WORKER;

Sorting by date when the only date requested is today is redundant. If the
preferred sort is time then worker, then what I gave you should work.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Randy" <ra****@msn.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

I am having a sorting problem on a form. No subforms.
Continuous Page Main Form. The query works fine pulling up a datasheet
but when I attach it to a button on a form. It brings up today's
records but does not sort.

What do I need to change.

Here is the code for the query.

SELECT MAIN.DATE1, MAIN.WORKER, MAIN.TIME
FROM MAIN
WHERE (((MAIN.DATE1)=Date()))
ORDER BY MAIN.DATE1, MAIN.WORKER, MAIN.TIME;

Thanks for any help..............

[Randy]

I tried it your way also but it is not sorting either by worker or
time, time or worker. I took out the sort by Date1. For some reason
it will bring up the today's date but refuses to sort the other two
fields. I can do it manually from the filter button and it works fine,
but if I attach it to a button on the form all it brings up is todays
records with no other sorts...??? its baffling me. The query works
fine on a datasheet.

Can you think of anything else?

[Alan Webb]

Randy,
This makes me wonder about the underlying data. I am curious as to what the
declared data type is for the column you want to sort on and whether it is
declared as a date, text, or . . .? Another thing to try is to declare an
index on the table that supplies the data which has the sort you want. Data
entry speed suffers as a result of this, but that may or may not be an issue
depending on whether this is a table used primarily for data capture or for
reporting & presentation of data.

Date serials will sort differently than dates stored as text in some cases.
A date stored as text could sort like this:
1/11/2001
11/1/2001
12/1/2001
2/1/2001

While you would expect:
1/11/2001
2/11/2001
11/1/2001
12/1/2001

So, I wonder about how the data is stored and whether that might lead to an
answer?
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Randy" <ra****@msn.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

I tried it your way also but it is not sorting either by worker or
time, time or worker. I took out the sort by Date1. For some reason
it will bring up the today's date but refuses to sort the other two
fields. I can do it manually from the filter button and it works fine,
but if I attach it to a button on the form all it brings up is todays
records with no other sorts...??? its baffling me. The query works
fine on a datasheet.

Can you think of anything else?

[Randy McCall]

I had imported the data so that may be the problem. I think you have
hit the nail on the head.

What do I need to do to fix this? I am new and just learning Access.

*** Sent via Developersdex http://www.developersdex.com ***

[Alan Webb]

Randy,
Look at the table that contains the imported data in design view and see
what the data type of the column you want to sort is. If it isn't
Date/Time, then it won't sort the way you expect. Don't immediately change
it though, there may be some of the data that doesn't translate to what
Access understands to be a valid date. If there is some data in there that
won't cleanly convert to a valid date (a clerk entered "time:
2004:02:23:18:22:03:50" instead of #2/23/2004 18:22:03:50#.) then Access
will change it to text and leave it for you to figure out what happened.
Once you've found all the data that doesn't convert to dates properly and
decided to either change it or add a column with the change and change it in
the new column (better for preserving an audit trail) then you can fix the
bad data and convert the column to Date/Time and you should be fine after
that.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Randy McCall" <ra****@msn.com> wrote in message
news:du***************@news.uswest.net...

I had imported the data so that may be the problem. I think you have
hit the nail on the head.

What do I need to do to fix this? I am new and just learning Access.

*** Sent via Developersdex http://www.developersdex.com ***