General discussion


Access 2000 Simple Query

By MRA2 ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by john.a.wills In reply to Access 2000 Simple Query

What columns are you selecting?

Collapse -

by MRA2 In reply to Access 2000 Simple Query

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.

Collapse -

by Dr Dij In reply to Access 2000 Simple Query

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

Collapse -

by MRA2 In reply to

Yes, Yes! How do I blank the rows????

Collapse -

by MRA2 In reply to Access 2000 Simple Query

This question was closed by the author

Related Discussions

Related Forums