Parameter query on multiple dates

L

Lisa

Hi,

I have a query with at least 15 date fields. I would like to run a
parameter query to extract all 15 date fields that fall within the beginning
and ending date entered. I keep getting all records.

Now, I have read back thru the threads and tried previous suggestions, like
putting the criteria on separate rows and whatnot. Still getting all.
Here's the SQL.

SELECT [tbl Property].[Property ID], [tbl Property].[Property Name], [tbl
Property].Driver, [LOI / Contract].[Initial Deposit], [LOI /
Contract].[Initial Deposit Due Date], [LOI / Contract].[Additional Deposit],
[LOI / Contract].[Additional Deposit Due], [LOI / Contract].[2nd Additional
Deposit], [LOI / Contract].[2nd Additional Deposit Due], [LOI /
Contract].[3rd Additional Deposit], [LOI / Contract].[3rd Additional Deposit
Due], [LOI / Contract].[4th Additional Deposit], [LOI / Contract].[4th
Additional Deposit Due], [LOI / Contract].DepositInitialTotalDate, [LOI /
Contract].DepositAddTotalDate, [LOI / Contract].DepositAdd2TotalDate, [LOI /
Contract].DepositAdd3TotalDate, [LOI / Contract].DepositAdd4TotalDate, [LOI /
Contract].[Refundable?], [LOI / Contract].[RefundableAdd?], [LOI /
Contract].[RefundableAdd2?], [LOI / Contract].[RefundableAdd3?], [LOI /
Contract].[RefundableAdd4?], [LOI / Contract].RefundInitialTotalDate, [LOI /
Contract].RefundAddTotalDate, [LOI / Contract].RefundAdd2TotalDate, [LOI /
Contract].RefundAdd3TotalDate, [LOI / Contract].RefundAdd4TotalDate, [LOI /
Contract].EventInitDep, [LOI / Contract].EventAddDep, [LOI /
Contract].Event2AddDep, [LOI / Contract].Event3AddDep, [LOI /
Contract].Event4AddDep, [LOI / Contract].EventInitRefundDep, [LOI /
Contract].EventAddRefundDep, [LOI / Contract].Event2AddRefundDep, [LOI /
Contract].Event3AddRefundDep, [LOI / Contract].Event4AddRefundDep
FROM [tbl Property] INNER JOIN [LOI / Contract] ON [tbl Property].[Property
ID] = [LOI / Contract].[Property ID]
WHERE ((([LOI / Contract].[Initial Deposit Due Date]) Between CDate([Please
enter beginning date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].[Additional Deposit Due]) Between CDate([Please enter beginning
date]) And CDate([Please enter ending date]))) OR ((([LOI / Contract].[2nd
Additional Deposit Due]) Between CDate([Please enter beginning date]) And
CDate([Please enter ending date]))) OR ((([LOI / Contract].[3rd Additional
Deposit Due]) Between CDate([Please enter beginning date]) And CDate([Please
enter ending date]))) OR ((([LOI / Contract].[4th Additional Deposit Due])
Between CDate([Please enter beginning date]) And CDate([Please enter ending
date]))) OR ((([LOI / Contract].DepositInitialTotalDate) Between
CDate([Please enter beginning date]) And CDate([Please enter ending date])))
OR ((([LOI / Contract].DepositAddTotalDate) Between CDate([Please enter
beginning date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd2TotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd3TotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd4TotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundInitialTotalDate) Between CDate([Please enter beginning
date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAddTotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd2TotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd3TotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd4TotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date])));

Thanks. Lisa
 
D

Duane Hookom

Either normalize your table structure or create a union query that creates a
separate record of each combination of PropertyID and a date. You can then
easily query for the property between a date range.

To get you kick started on the date union query:

SELECT [Property ID], [Initial Deposit Due Date] as TaskDate,
"Initial Deposit Due Date" as Task
FROM [LOI / Contract]
UNION ALL
SELECT [Property ID], [Additional Deposit Due],
"Additional Deposit Due"
FROM [LOI / Contract]
UNION ALL
SELECT [Property ID], [3rd Additional Deposit Due],
"3rd Additional Deposit Due"
FROM [LOI / Contract]
UNION ALL
--etc--;
You can then create a query that searches against the TaskDate field.

You should really consider normalizing...
--
Duane Hookom
MS Access MVP
--

Lisa said:
Hi,

I have a query with at least 15 date fields. I would like to run a
parameter query to extract all 15 date fields that fall within the
beginning
and ending date entered. I keep getting all records.

Now, I have read back thru the threads and tried previous suggestions,
like
putting the criteria on separate rows and whatnot. Still getting all.
Here's the SQL.

SELECT [tbl Property].[Property ID], [tbl Property].[Property Name], [tbl
Property].Driver, [LOI / Contract].[Initial Deposit], [LOI /
Contract].[Initial Deposit Due Date], [LOI / Contract].[Additional
Deposit],
[LOI / Contract].[Additional Deposit Due], [LOI / Contract].[2nd
Additional
Deposit], [LOI / Contract].[2nd Additional Deposit Due], [LOI /
Contract].[3rd Additional Deposit], [LOI / Contract].[3rd Additional
Deposit
Due], [LOI / Contract].[4th Additional Deposit], [LOI / Contract].[4th
Additional Deposit Due], [LOI / Contract].DepositInitialTotalDate, [LOI /
Contract].DepositAddTotalDate, [LOI / Contract].DepositAdd2TotalDate, [LOI
/
Contract].DepositAdd3TotalDate, [LOI / Contract].DepositAdd4TotalDate,
[LOI /
Contract].[Refundable?], [LOI / Contract].[RefundableAdd?], [LOI /
Contract].[RefundableAdd2?], [LOI / Contract].[RefundableAdd3?], [LOI /
Contract].[RefundableAdd4?], [LOI / Contract].RefundInitialTotalDate, [LOI
/
Contract].RefundAddTotalDate, [LOI / Contract].RefundAdd2TotalDate, [LOI /
Contract].RefundAdd3TotalDate, [LOI / Contract].RefundAdd4TotalDate, [LOI
/
Contract].EventInitDep, [LOI / Contract].EventAddDep, [LOI /
Contract].Event2AddDep, [LOI / Contract].Event3AddDep, [LOI /
Contract].Event4AddDep, [LOI / Contract].EventInitRefundDep, [LOI /
Contract].EventAddRefundDep, [LOI / Contract].Event2AddRefundDep, [LOI /
Contract].Event3AddRefundDep, [LOI / Contract].Event4AddRefundDep
FROM [tbl Property] INNER JOIN [LOI / Contract] ON [tbl
Property].[Property
ID] = [LOI / Contract].[Property ID]
WHERE ((([LOI / Contract].[Initial Deposit Due Date]) Between
CDate([Please
enter beginning date]) And CDate([Please enter ending date]))) OR ((([LOI
/
Contract].[Additional Deposit Due]) Between CDate([Please enter beginning
date]) And CDate([Please enter ending date]))) OR ((([LOI / Contract].[2nd
Additional Deposit Due]) Between CDate([Please enter beginning date]) And
CDate([Please enter ending date]))) OR ((([LOI / Contract].[3rd Additional
Deposit Due]) Between CDate([Please enter beginning date]) And
CDate([Please
enter ending date]))) OR ((([LOI / Contract].[4th Additional Deposit Due])
Between CDate([Please enter beginning date]) And CDate([Please enter
ending
date]))) OR ((([LOI / Contract].DepositInitialTotalDate) Between
CDate([Please enter beginning date]) And CDate([Please enter ending
date])))
OR ((([LOI / Contract].DepositAddTotalDate) Between CDate([Please enter
beginning date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd2TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd3TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd4TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundInitialTotalDate) Between CDate([Please enter beginning
date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAddTotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd2TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd3TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd4TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date])));

Thanks. Lisa
Click to expand...
 
L

Lisa

Thank you. I'll give it a shot -

Lisa

Duane Hookom said:
Either normalize your table structure or create a union query that creates a
separate record of each combination of PropertyID and a date. You can then
easily query for the property between a date range.

To get you kick started on the date union query:

SELECT [Property ID], [Initial Deposit Due Date] as TaskDate,
"Initial Deposit Due Date" as Task
FROM [LOI / Contract]
UNION ALL
SELECT [Property ID], [Additional Deposit Due],
"Additional Deposit Due"
FROM [LOI / Contract]
UNION ALL
SELECT [Property ID], [3rd Additional Deposit Due],
"3rd Additional Deposit Due"
FROM [LOI / Contract]
UNION ALL
--etc--;
You can then create a query that searches against the TaskDate field.

You should really consider normalizing...
--
Duane Hookom
MS Access MVP
--

Lisa said:
Hi,

I have a query with at least 15 date fields. I would like to run a
parameter query to extract all 15 date fields that fall within the
beginning
and ending date entered. I keep getting all records.

Now, I have read back thru the threads and tried previous suggestions,
like
putting the criteria on separate rows and whatnot. Still getting all.
Here's the SQL.

SELECT [tbl Property].[Property ID], [tbl Property].[Property Name], [tbl
Property].Driver, [LOI / Contract].[Initial Deposit], [LOI /
Contract].[Initial Deposit Due Date], [LOI / Contract].[Additional
Deposit],
[LOI / Contract].[Additional Deposit Due], [LOI / Contract].[2nd
Additional
Deposit], [LOI / Contract].[2nd Additional Deposit Due], [LOI /
Contract].[3rd Additional Deposit], [LOI / Contract].[3rd Additional
Deposit
Due], [LOI / Contract].[4th Additional Deposit], [LOI / Contract].[4th
Additional Deposit Due], [LOI / Contract].DepositInitialTotalDate, [LOI /
Contract].DepositAddTotalDate, [LOI / Contract].DepositAdd2TotalDate, [LOI
/
Contract].DepositAdd3TotalDate, [LOI / Contract].DepositAdd4TotalDate,
[LOI /
Contract].[Refundable?], [LOI / Contract].[RefundableAdd?], [LOI /
Contract].[RefundableAdd2?], [LOI / Contract].[RefundableAdd3?], [LOI /
Contract].[RefundableAdd4?], [LOI / Contract].RefundInitialTotalDate, [LOI
/
Contract].RefundAddTotalDate, [LOI / Contract].RefundAdd2TotalDate, [LOI /
Contract].RefundAdd3TotalDate, [LOI / Contract].RefundAdd4TotalDate, [LOI
/
Contract].EventInitDep, [LOI / Contract].EventAddDep, [LOI /
Contract].Event2AddDep, [LOI / Contract].Event3AddDep, [LOI /
Contract].Event4AddDep, [LOI / Contract].EventInitRefundDep, [LOI /
Contract].EventAddRefundDep, [LOI / Contract].Event2AddRefundDep, [LOI /
Contract].Event3AddRefundDep, [LOI / Contract].Event4AddRefundDep
FROM [tbl Property] INNER JOIN [LOI / Contract] ON [tbl
Property].[Property
ID] = [LOI / Contract].[Property ID]
WHERE ((([LOI / Contract].[Initial Deposit Due Date]) Between
CDate([Please
enter beginning date]) And CDate([Please enter ending date]))) OR ((([LOI
/
Contract].[Additional Deposit Due]) Between CDate([Please enter beginning
date]) And CDate([Please enter ending date]))) OR ((([LOI / Contract].[2nd
Additional Deposit Due]) Between CDate([Please enter beginning date]) And
CDate([Please enter ending date]))) OR ((([LOI / Contract].[3rd Additional
Deposit Due]) Between CDate([Please enter beginning date]) And
CDate([Please
enter ending date]))) OR ((([LOI / Contract].[4th Additional Deposit Due])
Between CDate([Please enter beginning date]) And CDate([Please enter
ending
date]))) OR ((([LOI / Contract].DepositInitialTotalDate) Between
CDate([Please enter beginning date]) And CDate([Please enter ending
date])))
OR ((([LOI / Contract].DepositAddTotalDate) Between CDate([Please enter
beginning date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd2TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd3TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].DepositAdd4TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundInitialTotalDate) Between CDate([Please enter beginning
date]) And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAddTotalDate) Between CDate([Please enter beginning date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd2TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd3TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date]))) OR ((([LOI /
Contract].RefundAdd4TotalDate) Between CDate([Please enter beginning
date])
And CDate([Please enter ending date])));

Thanks. Lisa
Click to expand...
Click to expand...
 

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

Similar Threads

query brings up wrong dates 4
Top 10 in Crosstab query 3
ACCESS query expression that works on one computer but not another 4
Parameter Query 1
Between Dates Parameter Query 7
Dates with an IFFy situation 4
Report built on a Crosstab Query (Access 2003) 1
MS Query 2000 on dates with parameters 4

Top