Restrict a Union Qry

[Jason]

I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];

 

[Klatuu]

Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

 

[Jason]

Thats seems to get the qry working correctly but somehow it is not picking up
the date from the field on my form. Is this right

Where(DateID>=[FrmEmployeestatsDate].[CboStartDate])

Thank you

Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];

 

[Jason]

Thank you I got it now, just add "forms" to the begining

Thats seems to get the qry working correctly but somehow it is not picking up
the date from the field on my form. Is this right

Where(DateID>=[FrmEmployeestatsDate].[CboStartDate])

Thank you

Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];

 

[Klatuu]

Try:
Where(DateID>=[Forms]![FrmEmployeestatsDate].[CboStartDate])
or
Where(DateID>= #[Forms]![FrmEmployeestatsDate].[CboStartDate]#)

Thats seems to get the qry working correctly but somehow it is not picking up
the date from the field on my form. Is this right

Where(DateID>=[FrmEmployeestatsDate].[CboStartDate])

Thank you

Think of each SELECT statement as a separate query and it will be easy. your
WHERE clause should go at the end of each SELECT statement. You will need a
WHERE for each SELECT.

I am trying to put a restriction on the dates I have in a Union qry. I only
want to return records that are => the date I have listed in a form
"FrmEmployeeStatsDate.CBOStartDate" However I am not sure how to add it to
the code. Any ideas for me. Thank you

Here is the Code
The date field is DateId

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0
as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score, 0 AS [SUM OF EMPLOYEE] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score, 0 AS [SUM OF
EMPLOYEE] From [qryROADSAFETY]
UNION SELECT "CALLOUTS" AS SOURCE,DateID, QI, 0 AS IVATTEMPTS, 0 AS
IVSUCCESS, 0 AS ETATTEMPTS, 0 AS ETSUCCESS, 0 AS [TOTAL CALLS], 0 AS [ALS
CALLS], 0 AS [NX CALLS], 0 AS MILES, 0 AS SCORE, [EMPLOYEE OUT] From
[qryCALLOUT];