Combo Box Union Query

[Guest]

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.

 

[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.

 

[Guest]

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;

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)

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.

 

[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.

 

[Guest]

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

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)

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;

 

[Guest]

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;

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)

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;