Sort, and then sort from the first sort

K

KB

In my database with columns (among others) "Date" and "Status", I have
information I need to pull up primarily by date. In the Date column, I
entered "Enter a start/ end date" criteria, and it only pulls up that
information just fine. My problem is now that those dates are up, I now only
want to show those files that are labelled "pending" in the status column. I
tried to enter pending in the criteria area, and access doesnt know which
criteria to sort as primary, so it lists all "pending" jobs, regardless of
date criteria. How do I sort first by date, then by status based on the dates
entered?
I did get a resoponse to post my sql, I was hoping for a more specific answer.

Thanks!
 
B

Brendan Reynolds

I've posted an example at the following URL ...

http://brenreyn.brinkster.net/filtersortexample.zip

There are three queries in the example, 'qrySort' demonstrates how to sort
on two columns, 'qryFilter' demonstrates how to filter by two columns, and
'qrySortAndFilter' demonstrates how to sort on one column while filtering by
another.
 
M

Michael J. Strickland

KB said:
In my database with columns (among others) "Date" and "Status", I have
information I need to pull up primarily by date. In the Date column, I
entered "Enter a start/ end date" criteria, and it only pulls up that
information just fine. My problem is now that those dates are up, I now
only
want to show those files that are labelled "pending" in the status column.
I
tried to enter pending in the criteria area, and access doesnt know which
criteria to sort as primary, so it lists all "pending" jobs, regardless of
date criteria. How do I sort first by date, then by status based on the
dates
entered?
I did get a resoponse to post my sql, I was hoping for a more specific
answer.

Thanks!


In Query Design View:

1. For the "Sort" box of the Date field, select Ascending.
2. For the "Criteria" box of the Date field, enter the dates you want to
select separated by Or (e.g. 10/01/06" Or "11/01/06" Or ...).

3. In the Criteria box of the Status field, enter "pending".

--
 
K

KB

Thanks, the only thing is I still need to make it date sensative. In the
criteria section, under date criteria, I already have "Between [Enter a
Beginning Date:] And [Enter a Ending Date:]", so it will ask for dates before
even opening the query. When I tried to also enter "<>"pending"" in the
criteria under status, it pulls up the dates specified, but it gives every
status except "pending".
Any way to reverse it?

I think I'm almost there.
 
B

BruceM

I think that BETWEEN [Start Date] AND [End Date] would work best for the
date criteria. OR would look for records on one of two dates, rather than
within a range.
 
K

KB

I do have that in the criteria under date, so when I click on the query, I am
asked for a start date and a finish date before it will open the query, and
when it opens, I do only get the info in between the dates specified. The
problem is that when I add "pending" in the status column under criteria, it
still asks me for in between dates as the date column criteria has asked it
to do, but after I enter the dates, it opens the query, and shows all pending
jobs, regardless of the in between dates I just entered.

BruceM said:
I think that BETWEEN [Start Date] AND [End Date] would work best for the
date criteria. OR would look for records on one of two dates, rather than
within a range.
 
B

Brendan Reynolds

Change '<>' to '='.

--
Brendan Reynolds
Access MVP

KB said:
Thanks, the only thing is I still need to make it date sensative. In the
criteria section, under date criteria, I already have "Between [Enter a
Beginning Date:] And [Enter a Ending Date:]", so it will ask for dates
before
even opening the query. When I tried to also enter "<>"pending"" in the
criteria under status, it pulls up the dates specified, but it gives every
status except "pending".
Any way to reverse it?

I think I'm almost there.

Brendan Reynolds said:
I've posted an example at the following URL ...

http://brenreyn.brinkster.net/filtersortexample.zip

There are three queries in the example, 'qrySort' demonstrates how to
sort
on two columns, 'qryFilter' demonstrates how to filter by two columns,
and
'qrySortAndFilter' demonstrates how to sort on one column while filtering
by
another.
 
B

BruceM

Try:
="Pending"
Exactly as it appears, with the double quotes. It's not clear exactly what
you used.

KB said:
Thanks, the only thing is I still need to make it date sensative. In the
criteria section, under date criteria, I already have "Between [Enter a
Beginning Date:] And [Enter a Ending Date:]", so it will ask for dates
before
even opening the query. When I tried to also enter "<>"pending"" in the
criteria under status, it pulls up the dates specified, but it gives every
status except "pending".
Any way to reverse it?

I think I'm almost there.

Brendan Reynolds said:
I've posted an example at the following URL ...

http://brenreyn.brinkster.net/filtersortexample.zip

There are three queries in the example, 'qrySort' demonstrates how to
sort
on two columns, 'qryFilter' demonstrates how to filter by two columns,
and
'qrySortAndFilter' demonstrates how to sort on one column while filtering
by
another.
 
B

BruceM

My reply was to the person who suggested using OR in the criteria. For the
other, I posted in another part of the thread, but here it is:
="Pending"
exactly as it appears should produce the desired results.

KB said:
I do have that in the criteria under date, so when I click on the query, I
am
asked for a start date and a finish date before it will open the query,
and
when it opens, I do only get the info in between the dates specified. The
problem is that when I add "pending" in the status column under criteria,
it
still asks me for in between dates as the date column criteria has asked
it
to do, but after I enter the dates, it opens the query, and shows all
pending
jobs, regardless of the in between dates I just entered.

BruceM said:
I think that BETWEEN [Start Date] AND [End Date] would work best for the
date criteria. OR would look for records on one of two dates, rather
than
within a range.

Michael J. Strickland said:
In my database with columns (among others) "Date" and "Status", I have
information I need to pull up primarily by date. In the Date column, I
entered "Enter a start/ end date" criteria, and it only pulls up that
information just fine. My problem is now that those dates are up, I
now
only
want to show those files that are labelled "pending" in the status
column. I
tried to enter pending in the criteria area, and access doesnt know
which
criteria to sort as primary, so it lists all "pending" jobs,
regardless
of
date criteria. How do I sort first by date, then by status based on
the
dates
entered?
I did get a resoponse to post my sql, I was hoping for a more specific
answer.

Thanks!


In Query Design View:

1. For the "Sort" box of the Date field, select Ascending.
2. For the "Criteria" box of the Date field, enter the dates you want
to
select separated by Or (e.g. 10/01/06" Or "11/01/06" Or ...).

3. In the Criteria box of the Status field, enter "pending".

--
 
K

KB

It worked. You have no idea how much I appreciate the help!

Brendan Reynolds said:
Change '<>' to '='.

--
Brendan Reynolds
Access MVP

KB said:
Thanks, the only thing is I still need to make it date sensative. In the
criteria section, under date criteria, I already have "Between [Enter a
Beginning Date:] And [Enter a Ending Date:]", so it will ask for dates
before
even opening the query. When I tried to also enter "<>"pending"" in the
criteria under status, it pulls up the dates specified, but it gives every
status except "pending".
Any way to reverse it?

I think I'm almost there.

Brendan Reynolds said:
I've posted an example at the following URL ...

http://brenreyn.brinkster.net/filtersortexample.zip

There are three queries in the example, 'qrySort' demonstrates how to
sort
on two columns, 'qryFilter' demonstrates how to filter by two columns,
and
'qrySortAndFilter' demonstrates how to sort on one column while filtering
by
another.

--
Brendan Reynolds
Access MVP


In my database with columns (among others) "Date" and "Status", I have
information I need to pull up primarily by date. In the Date column, I
entered "Enter a start/ end date" criteria, and it only pulls up that
information just fine. My problem is now that those dates are up, I now
only
want to show those files that are labelled "pending" in the status
column.
I
tried to enter pending in the criteria area, and access doesnt know
which
criteria to sort as primary, so it lists all "pending" jobs, regardless
of
date criteria. How do I sort first by date, then by status based on the
dates
entered?
I did get a resoponse to post my sql, I was hoping for a more specific
answer.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top