Query with MULTIPLE DATES 1

[papic1972]

Hi all,


I have a query (qryOrders) that queries the following 4 fields from a table tblOrders:

RoughInDate
ServiceDate
CommissionDate
FitoutDate

The query is run from a form (frmEnterDate) which is used to enter ONE date only. I need this date to be matched against any of the 4 fields.

E.g if the date entered on frmEnterDate is “26/06/05”, & this matches the date in the RoughInDate field & Service Date field, I need both fields to be returned.


I hope I have made myself clear enough!

Thanks in advance.

 

[KenReay]

Hi

From what you say, I think you need a Union query

SELECT OrderNo, RoughInDate FROM tblOrders
WHERE RoughInDate = Forms!MyForm!MyDate
UNION
SELECT OrderNo, ServiceDate FROM tblOrders
WHERE ServiceDate = Forms!MyForm!MyDate
UNION
SELECT OrderNo, ComissionDate FROM tblOrders
WHERE CommissionDate = Forms!MyForm!MyDate
UNION
SELECT OrderNo, FitOutDate FROM tblOrders
WHERE FitOutDate = Forms!MyForm!MyDate;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[papic1972]

Hi Ken,

Thanks for your response. I tried your suggestion & it only returns the "RoughinDate" Value, it doesn't return any of the other values. I tried shuffling the code around & it appears that it only returns values from the SELECT statement of the code. Why would it be doing this considering the statement is a UNION of multiple select statements?

 

[papic1972]

Hi Ken,

Thanks for your response. I tried your suggestion & it only returns the "RoughinDate" Value, it doesn't return any of the other values. I tried shuffling the code around & it appears that it only returns values from the 1st SELECT statement of the code. Why would it be doing this considering the statement is a UNION of multiple select statements?

 

[r937]

the result of a union always has the column name that was used in the first select

if you want to know which column it came from, you have to mark it

select OrderNo, 'RoughInDate' as thecolumn
, RoughInDate from tblOrders
where RoughInDate = Forms!MyForm!MyDate
union all
select OrderNo, 'ServiceDate'
, ServiceDate from tblOrders
where ServiceDate = Forms!MyForm!MyDate
union all
select OrderNo, 'ComissionDate'
, ComissionDate from tblOrders
where CommissionDate = Forms!MyForm!MyDate
union all
select OrderNo, 'FitOutDate'
, FitOutDate from tblOrders
where FitOutDate = Forms!MyForm!MyDate;

note the use of UNION ALL instead of UNION, because there cannot be duplicates

r937.com | rudy.ca

 

[papic1972]

Hey r937,


That's excellent!!!!!! That is exactly what I was after.
Have a star!