WHERE clause statement (1 Viewer)

[redducati748]

My question relates to the WHERE clause in my SQL statement. I ran the query with a single condition, which is shown in the SQL below. When a second condition is added to the SQL statement as shown below in the second statement the query does not pull data. I felt it could be a an operator issue, but after trying multiple scenarios I cannot get a data pull from the tables when the second condition is added to the WHERE clause. Any help would be greatly appreciated.
Thank you-


SINGLE CONDITION:

SELECT [Anton Time Reporting].CtecId, [Anton Time Reporting].StaffName, [Anton's Reporting Subform].StartDate, [Anton's Reporting Subform].EndDate, [Anton's Reporting Subform].Tasks, [Anton's Reporting Subform].Hours

FROM [Anton Time Reporting], [Anton's Reporting Subform]

WHERE [Anton's Reporting Subform].StartDate=[]

UNION SELECT [Christian Time Reporting].CtecId, [Christian Time Reporting].StaffName, [Christian's Reporting Subform].StartDate, [Christian's Reporting Subform].EndDate, [Christian's Reporting Subform].Tasks, [Christian's Reporting Subform].Hours

FROM [Christian Time Reporting], [Christian's Reporting Subform]

WHERE [Christian's Reporting Subform].StartDate=[]

UNION SELECT [Lynn Time Reporting].CtecId, [Lynn Time Reporting].StaffName, [Lynn's Reporting Subform].StartDate, [Lynn's Reporting Subform].EndDate, [Lynn's Reporting Subform].Tasks, [Lynn's Reporting Subform].Hours

FROM [Lynn Time Reporting], [Lynn's Reporting Subform]

WHERE [Lynn's Reporting Subform].StartDate=[];


DUAL CONDITION:

SELECT [Anton Time Reporting].CtecId, [Anton Time Reporting].StaffName, [Anton's Reporting Subform].StartDate, [Anton's Reporting Subform].EndDate, [Anton's Reporting Subform].Tasks, [Anton's Reporting Subform].Hours

FROM [Anton Time Reporting], [Anton's Reporting Subform]

WHERE ((([Anton's Reporting Subform].StartDate)=[]) AND (([Anton's Reporting Subform].EndDate)=[]))

UNION SELECT [Christian Time Reporting].CtecId, [Christian Time Reporting].StaffName, [Christian's Reporting Subform].StartDate, [Christian's Reporting Subform].EndDate, [Christian's Reporting Subform].Tasks, [Christian's Reporting Subform].Hours

FROM [Christian Time Reporting], [Christian's Reporting Subform]

WHERE ((([Christian's Reporting Subform].StartDate)=[]) AND (([Christian's Reporting Subform].EndDate)=[]))

UNION SELECT [Lynn Time Reporting].CtecId, [Lynn Time Reporting].StaffName, [Lynn's Reporting Subform].StartDate, [Lynn's Reporting Subform].EndDate, [Lynn's Reporting Subform].Tasks, [Lynn's Reporting Subform].Hours

FROM [Lynn Time Reporting], [Lynn's Reporting Subform]

WHERE ((([Lynn's Reporting Subform].StartDate)=[]) AND (([Lynn's Reporting Subform].EndDate)=[]));

 

[RV]

The problem is in your WHERE clause.

WHERE [Anton's Reporting Subform].StartDate=[] AND [Anton's Reporting Subform].EndDate=[]

The problem here is in your brackets []
The way you've defined your parameters there's only ONE defined...
If you enter for instance 01/01/2002 you'll retrieve rows where both StartDate and EndDate are equal to 01/01/2002.

Use this instead:

WHERE [Anton's Reporting Subform].StartDate=
[Enter startdate]
AND [Anton's Reporting Subform].EndDate=[Enter enddate]

Bye the way, why are you using three times
two tables in your UNION where:

- you only need two
- you don't need a UNION

You should consider rebuilding your tablestructure according to the principles of normalization....

RV