SQL Change

[Jason]

I need to make a change to my SQL below and am not sure how to do it. I thank
you for your help.
I want to return the records where QI = FrmEmployeeStatsDate.[Combo24]
but I would also like to return all the records if that Combo box is left
blank. Can you help Thank you.

Here's the SQL
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate]);

 

[Van T. Dinh]

Replace

.... (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])...

with

.... ((DateID>=Forms!FrmEmployeeStatsDate!CboStartDate)
OR (Forms!FrmEmployeeStatsDate!CboStartDate Is Null))

 

[Jason]

I want to keep that line and add another but thanks

Replace

.... (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])...

with

.... ((DateID>=Forms!FrmEmployeeStatsDate!CboStartDate)
OR (Forms!FrmEmployeeStatsDate!CboStartDate Is Null))

--
HTH
Van T. Dinh
MVP (Access)

I need to make a change to my SQL below and am not sure how to do it. I
thank
you for your help.
I want to return the records where QI = FrmEmployeeStatsDate.[Combo24]
but I would also like to return all the records if that Combo box is left
blank. Can you help Thank you.

Here's the SQL
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate]);

 

[Bill Mosca, MS Access MVP]

Van's SQL did exactly that. It kept your line and added an Is Null criteria.

--
Bill Mosca, MS Access MVP

I want to keep that line and add another but thanks

Replace

.... (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])...

with

.... ((DateID>=Forms!FrmEmployeeStatsDate!CboStartDate)
OR (Forms!FrmEmployeeStatsDate!CboStartDate Is Null))

--
HTH
Van T. Dinh
MVP (Access)

I need to make a change to my SQL below and am not sure how to do it. I
thank
you for your help.
I want to return the records where QI = FrmEmployeeStatsDate.[Combo24]
but I would also like to return all the records if that Combo box is
left
blank. Can you help Thank you.

Here's the SQL
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate])
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]
Where (DateID>=Forms.FrmEmployeeStatsDate.[CboStartDate]);

 

[Jason]

I am sorry I may not have made my intent clear.

The line that Van changed does not need to it limits the records returned by
date. I will always have a date for that field. What I wanted to do was add
another line that would limit the records returned by employee within that
date period. QI = the emplyee. I added a combo box to my form that lets me
choose the employee I want to look at, but I would also like to return all
the records if I leave that field blank. Thank you