How do I filter fields based on date ranges

[Guest]

I really need to filter information in the following way:

Background info:

Audits are conducted of various areas in two departments. So I want to group
by department and group audits by area.

Each area is audited on different dates, so when I create a crosstab query,
I'm able to sum individual scores of each area. I'm very pleased that I was
able to get this far...

Problems:

1.) The dates, (formattted in short form, and are also my column headings in
the crosstab query) do not sort correctly. I can't figure this out. I want
them to sort in ascending order, left to right, from oldest date to newest
date.

2.) Ultimately, I want a report that will filter by department, group by
area and show the last number of scores I specify from the last audit date.
Is there a way to query a number of records from a certain date?

If anyone can help me I would greatly appreciate it.

Bryan

 

[[MVP] S.Clark]

Congrats on the progress that you've made so far.

1. Try setting the sort order in the query for the date field.

2. To query by a date range, you can use the Between...And structure.

e.g.. SELECT * FROM Tablename WHERE [datefield] Between #1/1/2005# And
Date()

 

[Guest]

Thanks Steve,

I've tried setting the sort order (ascending & descending). The result looks
something like this:
Column headings in [brackets]:

[Department][Area][9/17/04][9/21/04][9/7/04][1/18/05][1/20/05][1/7/05]

This is confusing as it doesn't seem to make a difference in either sort
order. The dates seem to get jumbled, regardless of what I do. I think it has
something to do with the fact that the dates are in the column heading field,
but I'm not sure how to fix this.

Regarding the date range query, I'm able to accurately query dates using
parameter dialog boxes, but since I don't audit each area on the same date, I
really want to compare the "last 5" audit records within the date range. I
think the real problem is that I can't get it to search through the value
fields in the crosstab query and have it sort in chronological order.

So what I get is this:

[9/1/04][9/2/04][9/3/04][9/4/04][9/5/04][1/20/05][1/21/05][3/22/05]
20 54 67
71

What I really want is this:

[9/1/04][9/3/04][1/20/05][3/22/05]
20 54 67 71

Then I can report this and put my calculated fields in for percent change in
the last two values for every area audited.

Do I need to abandon the notion that this can be done only in a crosstab
query?

Thanks,
Bryan

Congrats on the progress that you've made so far.

1. Try setting the sort order in the query for the date field.

2. To query by a date range, you can use the Between...And structure.

e.g.. SELECT * FROM Tablename WHERE [datefield] Between #1/1/2005# And
Date()


--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I really need to filter information in the following way:

Background info:

Audits are conducted of various areas in two departments. So I want to
group
by department and group audits by area.

Each area is audited on different dates, so when I create a crosstab
query,
I'm able to sum individual scores of each area. I'm very pleased that I
was
able to get this far...

Problems:

1.) The dates, (formattted in short form, and are also my column headings
in
the crosstab query) do not sort correctly. I can't figure this out. I want
them to sort in ascending order, left to right, from oldest date to newest
date.

2.) Ultimately, I want a report that will filter by department, group by
area and show the last number of scores I specify from the last audit
date.
Is there a way to query a number of records from a certain date?

If anyone can help me I would greatly appreciate it.

Bryan