Combo Box Union Query

J

JOM

I have the following select statement in my combobox row source

SELECT [EmployeeInfo].EmpID, [FirstName] & ", " & [LastName] AS EName FROM
[EmployeeInfo] UNION select -1,'[all]' from [EmployeeInfo];

What am trying to do is select all to open a report based on what is in the
comboxbox and between begining date and ending date but it tells me that
there is no data, but there is data for the dates i put in.
 
V

Van T. Dinh

You are probably using a Parametrised Query as the RecordSource for the
Report and the criteria for EmpID / [All] is incorrectly constructed.

Post the SQL String of the Report's RecodSource if you need further help.
 
J

JOM

the sql for the report is as follows(its a crosstab query)

PARAMETERS [forms]![ServiceLevel]![beginDate] DateTime,
[forms]![ServiceLevel]![EndDate] DateTime;
TRANSFORM Count(qryPipeLineSum.fp) AS CountOffp
SELECT qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0) AS Months, Format([Date],"ww",0,0) AS Weeks, Format([Date],"Short
Date") AS dates, qryPipeLineSum.EName, qryPipeLineSum.Date,
qryPipeLineSum.Date
FROM qryPipeLineSum
WHERE (((qryPipeLineSum.Date) Between [forms]![ServiceLevel]![beginDate] And
[forms]![ServiceLevel]![EndDate]))
GROUP BY qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0), Format([Date],"ww",0,0), Format([Date],"Short Date"),
qryPipeLineSum.EName, qryPipeLineSum.Date, qryPipeLineSum.EmpID
ORDER BY qryPipeLineSum.EName
PIVOT qryPipeLineSum.fp;







Van T. Dinh said:
You are probably using a Parametrised Query as the RecordSource for the
Report and the criteria for EmpID / [All] is incorrectly constructed.

Post the SQL String of the Report's RecodSource if you need further help.

--
HTH
Van T. Dinh
MVP (Access)




JOM said:
I have the following select statement in my combobox row source

SELECT [EmployeeInfo].EmpID, [FirstName] & ", " & [LastName] AS EName FROM
[EmployeeInfo] UNION select -1,'[all]' from [EmployeeInfo];

What am trying to do is select all to open a report based on what is in
the
comboxbox and between begining date and ending date but it tells me that
there is no data, but there is data for the dates i put in.
Click to expand...
Click to expand...
 
V

Van T. Dinh

It looks like "qryPipeLineSum" is where you used the ComboBox as a Parameter
so you need to post the SQL String for this Query. The problem is most like
in the criteria involving the ComboBox.
 
J

JOM

The parameter query is in the query I posted, also I have the following click
statement in the button on my form that opens the report
and maybe this could be the problem.... (This is because when I select
something else other than [all], it opens the report......

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Dim stDocName As String
Dim strWhereEmpl As String

If IsNull(Me.cmbDaily) Then
MsgBox "Select an employee to Preview."
Me.cmbDaily.SetFocus
Exit Sub
End If

'Check to see that ending date is later than beginning date.
If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning date."
EndDate.SetFocus
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
Exit Sub
End If

strWhereEmpl = "EmpID = " & Forms![ServiceLevel]!cmbDaily
stDocName = "Daily Service Level Summary"

DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub


Van T. Dinh said:
It looks like "qryPipeLineSum" is where you used the ComboBox as a Parameter
so you need to post the SQL String for this Query. The problem is most like
in the criteria involving the ComboBox.

--
HTH
Van T. Dinh
MVP (Access)


JOM said:
the sql for the report is as follows(its a crosstab query)

PARAMETERS [forms]![ServiceLevel]![beginDate] DateTime,
[forms]![ServiceLevel]![EndDate] DateTime;
TRANSFORM Count(qryPipeLineSum.fp) AS CountOffp
SELECT qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0) AS Months, Format([Date],"ww",0,0) AS Weeks,
Format([Date],"Short
Date") AS dates, qryPipeLineSum.EName, qryPipeLineSum.Date,
qryPipeLineSum.Date
FROM qryPipeLineSum
WHERE (((qryPipeLineSum.Date) Between [forms]![ServiceLevel]![beginDate]
And
[forms]![ServiceLevel]![EndDate]))
GROUP BY qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0), Format([Date],"ww",0,0), Format([Date],"Short Date"),
qryPipeLineSum.EName, qryPipeLineSum.Date, qryPipeLineSum.EmpID
ORDER BY qryPipeLineSum.EName
PIVOT qryPipeLineSum.fp;
Click to expand...
Click to expand...
 
J

JOM

here is my other query...

SELECT Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
FROM Query1
GROUP BY Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
ORDER BY Query1.EName;






Van T. Dinh said:
It looks like "qryPipeLineSum" is where you used the ComboBox as a Parameter
so you need to post the SQL String for this Query. The problem is most like
in the criteria involving the ComboBox.

--
HTH
Van T. Dinh
MVP (Access)


JOM said:
the sql for the report is as follows(its a crosstab query)

PARAMETERS [forms]![ServiceLevel]![beginDate] DateTime,
[forms]![ServiceLevel]![EndDate] DateTime;
TRANSFORM Count(qryPipeLineSum.fp) AS CountOffp
SELECT qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0) AS Months, Format([Date],"ww",0,0) AS Weeks,
Format([Date],"Short
Date") AS dates, qryPipeLineSum.EName, qryPipeLineSum.Date,
qryPipeLineSum.Date
FROM qryPipeLineSum
WHERE (((qryPipeLineSum.Date) Between [forms]![ServiceLevel]![beginDate]
And
[forms]![ServiceLevel]![EndDate]))
GROUP BY qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0), Format([Date],"ww",0,0), Format([Date],"Short Date"),
qryPipeLineSum.EName, qryPipeLineSum.Date, qryPipeLineSum.EmpID
ORDER BY qryPipeLineSum.EName
PIVOT qryPipeLineSum.fp;
Click to expand...
Click to expand...
 
V

Van T. Dinh

OK, you seem to use a series of Queries and you don't know what we need to
help you to solve your problem ...

In your first post in this thread, you described the "cboEmploee" with the
"All" option. Surely, you must be using the reference to this ComboBox as
the Parameter in one Query in a series of Queries you have. From what you
described, the problem is in the criteria that uses the reference to the
ComboBox. We need the SQL String of this particular Query so that we can
see what's wrong with the crieria you used.
 
J

JOM

ok! well here is where the query begins
Query 1
************************************************************
SELECT [VOE Inquiry Tracking].EmpID, Switch([voe inquiry
Tracking]!Datecompleted-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateCompleted,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateCompleted,1)<=2,"c<=48",[voe inquiry
Tracking]!Datecompleted-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateCompleted,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateCompleted,1)>2,"C>48",[voe inquiry
Tracking]!Datefollowup-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE Inquiry
Tracking]!Date,[VOE Inquiry Tracking]!DateFollowup,7)-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry Tracking]!DateFollowup,1)<=2,"O<=48",[voe
inquiry Tracking]!Datefollowup-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateFollowup,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateFollowup,1)>2,"O>48",True,"Ns") AS fp, [VOE Inquiry
Tracking].Date, [VOE Inquiry Tracking].DateCompleted, [VOE Inquiry
Tracking].DateFollowup, [LastName] & " " & Left([Firstname],1) & "." AS
EName, [VOE Inquiry Tracking].VoeID, [Employee Info].LastName
FROM [Employee Info] INNER JOIN [VOE Inquiry Tracking] ON [Employee
Info].EmpID = [VOE Inquiry Tracking].EmpID;
************************************************************

*******This is Query qryPipeLineSum*******************************
************************************************************
SELECT Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
FROM Query1
GROUP BY Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
ORDER BY Query1.EName;
************************************************************


*********this the reports recordsource the one that contains the parameters **
************************************************************
PARAMETERS [forms]![ServiceLevel]![beginDate] DateTime,
[forms]![ServiceLevel]![EndDate] DateTime;
TRANSFORM Count(qryPipeLineSum.fp) AS CountOffp
SELECT qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0) AS Months, Format([Date],"ww",0,0) AS Weeks, Format([Date],"Short
Date") AS dates, qryPipeLineSum.EName, qryPipeLineSum.Date,
qryPipeLineSum.Date
FROM qryPipeLineSum
WHERE (((qryPipeLineSum.Date) Between [forms]![ServiceLevel]![beginDate] And
[forms]![ServiceLevel]![EndDate]))
GROUP BY qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0), Format([Date],"ww",0,0), Format([Date],"Short Date"),
qryPipeLineSum.EName, qryPipeLineSum.Date, qryPipeLineSum.EmpID
ORDER BY qryPipeLineSum.EName
PIVOT qryPipeLineSum.fp;
************************************************************




JOM said:
I have the following select statement in my combobox row source

SELECT [EmployeeInfo].EmpID, [FirstName] & ", " & [LastName] AS EName FROM
[EmployeeInfo] UNION select -1,'[all]' from [EmployeeInfo];

What am trying to do is select all to open a report based on what is in the
comboxbox and between begining date and ending date but it tells me that
there is no data, but there is data for the dates i put in.
Click to expand...
 
J

JOM

I posted my reply but under me instead of you, so I am going to post itunder
your name, accept my apology if this creates duplicates....

ok! well here is where the query begins
Query 1
************************************************************
SELECT [VOE Inquiry Tracking].EmpID, Switch([voe inquiry
Tracking]!Datecompleted-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateCompleted,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateCompleted,1)<=2,"c<=48",[voe inquiry
Tracking]!Datecompleted-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateCompleted,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateCompleted,1)>2,"C>48",[voe inquiry
Tracking]!Datefollowup-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE Inquiry
Tracking]!Date,[VOE Inquiry Tracking]!DateFollowup,7)-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry Tracking]!DateFollowup,1)<=2,"O<=48",[voe
inquiry Tracking]!Datefollowup-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateFollowup,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateFollowup,1)>2,"O>48",True,"Ns") AS fp, [VOE Inquiry
Tracking].Date, [VOE Inquiry Tracking].DateCompleted, [VOE Inquiry
Tracking].DateFollowup, [LastName] & " " & Left([Firstname],1) & "." AS
EName, [VOE Inquiry Tracking].VoeID, [Employee Info].LastName
FROM [Employee Info] INNER JOIN [VOE Inquiry Tracking] ON [Employee
Info].EmpID = [VOE Inquiry Tracking].EmpID;
************************************************************

*******This is Query qryPipeLineSum*******************************
************************************************************
SELECT Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
FROM Query1
GROUP BY Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
ORDER BY Query1.EName;
************************************************************


*********this the reports recordsource the one that contains the parameters **
************************************************************
PARAMETERS [forms]![ServiceLevel]![beginDate] DateTime,
[forms]![ServiceLevel]![EndDate] DateTime;
TRANSFORM Count(qryPipeLineSum.fp) AS CountOffp
SELECT qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0) AS Months, Format([Date],"ww",0,0) AS Weeks, Format([Date],"Short
Date") AS dates, qryPipeLineSum.EName, qryPipeLineSum.Date,
qryPipeLineSum.Date
FROM qryPipeLineSum
WHERE (((qryPipeLineSum.Date) Between [forms]![ServiceLevel]![beginDate] And
[forms]![ServiceLevel]![EndDate]))
GROUP BY qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0), Format([Date],"ww",0,0), Format([Date],"Short Date"),
qryPipeLineSum.EName, qryPipeLineSum.Date, qryPipeLineSum.EmpID
ORDER BY qryPipeLineSum.EName
PIVOT qryPipeLineSum.fp;
************************************************************
 
V

Van T. Dinh

Let me ask you another way: If you want to do a Report on a single Employee
with the same date restriction, I would expect you to select the Employee in
the ComboBox and in your processing of the Report, you would some how
restrict the data to this particular Employee. Right?

Now, describe how you restrict the data to only those that belong to the
Employee selected in the ComboBox ...

--
HTH
Van T. Dinh
MVP (Access)




JOM said:
ok! well here is where the query begins
Query 1
************************************************************
SELECT [VOE Inquiry Tracking].EmpID, Switch([voe inquiry
Tracking]!Datecompleted-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateCompleted,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateCompleted,1)<=2,"c<=48",[voe inquiry
Tracking]!Datecompleted-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateCompleted,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateCompleted,1)>2,"C>48",[voe inquiry
Tracking]!Datefollowup-[voe inquiry Tracking]!Date-DateDiff("ww",[VOE
Inquiry
Tracking]!Date,[VOE Inquiry Tracking]!DateFollowup,7)-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateFollowup,1)<=2,"O<=48",[voe
inquiry Tracking]!Datefollowup-[voe inquiry
Tracking]!Date-DateDiff("ww",[VOE
Inquiry Tracking]!Date,[VOE Inquiry
Tracking]!DateFollowup,7)-DateDiff("ww",[VOE Inquiry Tracking]!Date,[VOE
Inquiry Tracking]!DateFollowup,1)>2,"O>48",True,"Ns") AS fp, [VOE Inquiry
Tracking].Date, [VOE Inquiry Tracking].DateCompleted, [VOE Inquiry
Tracking].DateFollowup, [LastName] & " " & Left([Firstname],1) & "." AS
EName, [VOE Inquiry Tracking].VoeID, [Employee Info].LastName
FROM [Employee Info] INNER JOIN [VOE Inquiry Tracking] ON [Employee
Info].EmpID = [VOE Inquiry Tracking].EmpID;
************************************************************

*******This is Query qryPipeLineSum*******************************
************************************************************
SELECT Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
FROM Query1
GROUP BY Query1.EmpID, Query1.fp, Query1.DateFollowup, Query1.Date,
Query1.DateCompleted, Query1.EName, Query1.VoeID
ORDER BY Query1.EName;
************************************************************


*********this the reports recordsource the one that contains the
parameters **
************************************************************
PARAMETERS [forms]![ServiceLevel]![beginDate] DateTime,
[forms]![ServiceLevel]![EndDate] DateTime;
TRANSFORM Count(qryPipeLineSum.fp) AS CountOffp
SELECT qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0) AS Months, Format([Date],"ww",0,0) AS Weeks,
Format([Date],"Short
Date") AS dates, qryPipeLineSum.EName, qryPipeLineSum.Date,
qryPipeLineSum.Date
FROM qryPipeLineSum
WHERE (((qryPipeLineSum.Date) Between [forms]![ServiceLevel]![beginDate]
And
[forms]![ServiceLevel]![EndDate]))
GROUP BY qryPipeLineSum.EName, qryPipeLineSum.EmpID, qryPipeLineSum.Date,
qryPipeLineSum.percomplete, qryPipeLineSum.perOutstand, Format([Date],"mmm
yyyy",0,0), Format([Date],"ww",0,0), Format([Date],"Short Date"),
qryPipeLineSum.EName, qryPipeLineSum.Date, qryPipeLineSum.EmpID
ORDER BY qryPipeLineSum.EName
PIVOT qryPipeLineSum.fp;
************************************************************




JOM said:
I have the following select statement in my combobox row source

SELECT [EmployeeInfo].EmpID, [FirstName] & ", " & [LastName] AS EName
FROM
[EmployeeInfo] UNION select -1,'[all]' from [EmployeeInfo];

What am trying to do is select all to open a report based on what is in
the
comboxbox and between begining date and ending date but it tells me that
there is no data, but there is data for the dates i put in.
Click to expand...
Click to expand...
 
Top