Microsoft Access - Date Range query for multiple fields in one table

I am trying to create a query and report that will give me dates between a specified date range. 

The table is set up with fields for employee, expiration date 1, expiration date 2, expiration date 3, expiration date 4.
I want to choose a date range that will only pull dates between that date range for all date fields.  Instead what I'm getting is all entered dates if only one of the dates meets the criteria for that employee.

I entered ">=[Start Date] And <=[End Date]" under the Select Query criteria for the first Expiration date and put the same info in the Select Query Or section for the remaining 3 Expiration dates.

What am I doing wrong?

Since you want to perform an AND operation, you should put the criteria for the other expiration dates as a separate field criteria and not under the OR criteria.

Regards,
SQL Server Helper

Well sort of, not completely an AND operation.  I only want AND when it meets the date parameters.  When I do it the way you suggest, then the query looks for all 4 date fields to meet the criteria and won't give me a partial listing.  I'm probably not explaining this clearly.  The employee is only listed once in the table.  Each employee has four exp dates fields with different dates.  I want to be able to do a query that will give me all expiration dates that fall between 2 dates.  Unfortunately, what I am getting is that if one of them meets the criteria it's pulling the other 3 dates, regardless of whether they meet the date criteria or not.  Instead I just want them to pull the dates within the date criteria and leave the other fields blank if they don't meet the criteria.  The criteria I am currently using the pull the start/end dates is ">=[Start Date] And <=[End Date]".  Shouldn't I be adding something that if it doesn't meet the criteria to leave the field blank?  Thanks for trying to help me.

I think I have a better understanding of what you want.  You will need to use the IIF function for that purpose.  Here's a sample of how it will look like:

IIF([ExpirationDate] >= [Start Date] AND [ExpirationDate] <= [End Date], [ExpirationDate], NULL)

What this does is return the expiration date if it is within the date range you specified, otherwise it will return NULL for the field.

Regards,
SQL Server Helper

It didn't work.  I replaced Expiration Date to the actual field name in all instances in all the date fields and nothing pulled up on the query.  I tried them with the first expression as the criteria and the remaining 3 as the or.  Then I tried them all on criteria, then all on the or.  Nothing pulled up when I ran the action query.  It kept asking me for the "expiration date" for the first date, so I removed the middle "Expiration Date" out of each expression.  It got me right back where I started, pulling all the dates if one meets the date criteria.  This really shouldn't be this hard right?  What am I missing?

It would be easier if you use SQL query when you are building your SELECT statement instead of the diagram.  With the SQL query, you will be able to modify the query to suit your requirement.  Given this, I suggest pasting the query you have so far that doesn't work and we can work from it.

Regards,
SQL Server Helper

 Okay, this is what I have when viewing my Select Query in SQL view:

 SELECT [Staff Database].[Last Name], [Staff Database].[First Name], [Staff Database].[Program Office], [Staff Database].[DL Exp], [Staff Database].[Car Ins Exp], [Staff Database].[Car Regis Exp], [Staff Database].[Car Inspec Exp]
FROM [Staff Database]
WHERE ((([Staff Database].[DL Exp])=IIf([DL Exp]>=[Start Date] And ([Staff Database].[DL Exp])<=[End Date],[DL Exp],([Staff Database].[DL Exp]) Is Null))) OR ((([Staff Database].[Car Ins Exp])=IIf([Car Ins Exp]>=[Start Date] And ([Staff Database].[Car Ins Exp])<=[End Date],[Car Ins Exp],([Staff Database].[Car Ins Exp]) Is Null)) AND (([Staff Database].[Car Regis Exp])=IIf([Car Regis Exp]>=[Start Date] And ([Staff Database].[Car Regis Exp])<=[End Date],[Car Regis Exp],([Staff Database].[Car Regis Exp]) Is Null)) AND (([Staff Database].[Car Inspec Exp])=IIf([Car Inspec Exp]>=[Start Date] And ([Staff Database].[Car Inspec Exp])<=[End Date],[Car Inspec Exp],([Staff Database].[Car Inspec Exp]) Is Null)));     

I am not very savvy with looking at my queries this way, so bear with my ignorance.  Thanks for any help you can give.

Try this query:

SELECT [Staff Database].[Last Name], [Staff Database].[First Name], [Staff Database].[Program Office],
IIf([DL Exp]>=[Start Date] And [DL Exp]<=[End Date],[DL Exp], NULL) AS [DL Exp],
IIf([Car Ins Exp]>=[Start Date] And [Car Ins Exp]<=[End Date],[Car Ins Exp], NULL) AS [Car Ins Exp],
IIf([Car Regis Exp]>=[Car Regis Exp] And [Car Regis Exp]<=[End Date],[Car Regis Exp], NULL) AS [Car Regis Exp],
IIf([Car Inspec Exp]>=[Start Date] And [Car Inspec Exp]<=[End Date],[Car Inspec Exp], NULL) AS [Car Inspec Exp]
FROM [Staff Database]
WHERE ([DL Exp] >= [Start Date] AND [DL Exp] <= [End Date]) OR
([Car Ins Exp] >= [Start Date] AND [Car Ins Exp] <= [End Date]) OR
([Car Regis Exp] >= [Start Date] AND [Car Regis Exp] <= [End Date]) OR
([Car Inspec Exp] >= [Start Date] AND [Car Inspec Exp] <= [End Date])

Regards,
SQL Server Helper

Okay... I copy and pasted over the old info.  I went to run it and received the error:
"Curcular reference caused by alias "DL Exp" in query definition's SELECT list."
I'm hoping this makes sense to you.  I really didn't think this would be as difficult as it it becoming.

Based on the error message you are geting, you have to change the alias of the columns.  Here's an updated version of the query:

SELECT [Staff Database].[Last Name], [Staff Database].[First Name], [Staff Database].[Program Office],
IIf([DL Exp]>=[Start Date] And [DL Exp]<=[End Date],[DL Exp], NULL) AS [DL Exp1],
IIf([Car Ins Exp]>=[Start Date] And [Car Ins Exp]<=[End Date],[Car Ins Exp], NULL) AS [Car Ins Exp1],
IIf([Car Regis Exp]>=[Car Regis Exp] And [Car Regis Exp]<=[End Date],[Car Regis Exp], NULL) AS [Car Regis Exp1],
IIf([Car Inspec Exp]>=[Start Date] And [Car Inspec Exp]<=[End Date],[Car Inspec Exp], NULL) AS [Car Inspec Exp1]
FROM [Staff Database]
WHERE ([DL Exp] >= [Start Date] AND [DL Exp] <= [End Date]) OR
([Car Ins Exp] >= [Start Date] AND [Car Ins Exp] <= [End Date]) OR
([Car Regis Exp] >= [Start Date] AND [Car Regis Exp] <= [End Date]) OR
([Car Inspec Exp] >= [Start Date] AND [Car Inspec Exp] <= [End Date])

Regards,
SQL Server Helper

It Worked!!!!  THANKS SO MUCH!  I appreciate you taking the time to work through this with me.  Have an awesome day!

There's a bug in that code, where [Car Regis Exp] is used on both sides of a predicate in its Iif expression.  To simplify things a bit:

SELECT [Last Name], [First Name], [Program Office],
Switch([DL Exp] Between [Start Date] And [End Date], [DL Exp]) AS DL_Exp,
Switch([Car Ins Exp] Between [Start Date] And [End Date], [Car Ins Exp]) AS Car_Ins_Exp,
Switch([Car Regis Exp] Between [Start Date] And [End Date], [Car Regis Exp]) AS Car_Regis_Exp,
Switch([Car Inspec Exp] Between [Start Date] And [End Date], [Car Inspec Exp]) AS Car_Inspec_Exp
FROM [Staff Database]
WHERE [DL Exp] Between [Start Date] And [End Date]
 OR [Car Ins Exp] Between [Start Date] And [End Date]
 OR [Car Regis Exp] Between [Start Date] And [End Date]
 OR [Car Inspec Exp] Between [Start Date] And [End Date];

Hey Thanks!  I did realize the bug when I looked at the query and was able to fix it.  I tried your info and it seems to work as well!  I appreciate everyone's help!