Web Development

Ask something
Discussion Locked

Access 2000 Simple Query

0 helpful
5 replies

by mra2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a Flat file with Employee Name, Class1 Date, Class2 Date & Class3 Date.

What I am trying to do is get a Spreadsheet looking report with Dates across the top and using a query for a date Range.

For Example: Show ALL Classes >= 05-01-2006 and <= 05-23-2006

I put in one Employee with:
Class1 Date = 05-05-2006
Class2 Date = 04-10-2006
Class3 Date = 05-10-2006

I put >= #05-01-2006# and <= #05-23-2006 on a different line for each of the date fields in a Query.

However the Query ALWAYS picks ALL THREE Dates even though the Class2 Date simply does NOT MEET the Criteria.

What am I doing wrong? How can I get the correct answer?

I would gladly Email you this tiny MDB file if you wish...

Alan NYC
ABRAMOWITZA@coned.com

5 replies

by mra2

I am trying to select:
Class1 date
Class2 Date
Class3 date

I do NOT want a query which selects when all three meet the range.
I want a query where ANY meet the range.

by dr dij

I think I understand what you want:
you're selecting ROWS of data, not individual fields.

in the access query grid, across means AND (so all dates would have to match, which they don't)
OR is in sept line down

I think you wanted to create a query where ANY date within the range in 3 fields pulls the row.

this you did OK.

if you have one employee with dates you mentioned, OR will pull this whole record because ONE date is in the range.

if you then wanted the returned dates in any field to be blank if not within the date range, you'd need an access query formula to blank the dates in the returned rows.
then you'd return
emp class1date class2date class3date
emp1 05-05-2006 (blanked out) 5-10-2006

This discussion is closed to new replies.