Reporting Criteria help needed

[angiem]

What I am trying to do, is run a report with a cross tab query as the recordsource. The user selects all or an individual names from a listbox on the form. I have no problems getting all the names it is just when they choose an individual name.

This is the code that I have

strCriteria = Me.LstNames.Column(0, LstNames.ListIndex)
DoCmd.OpenReport stDocName, acPreview, , "[empname]='" & strCriteria & "'"

Thanks

Angie

http://www.millardconsulting.ca

 

[dhookom]

There is no way to use a multi-select list box like this. You could loop through the selected items to build strCriteria with " In ('firstname', 'secondname',...)". Check faq703-3936 for an example.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

Maybe I've not made myself clear the list box is not a multi-select. The user can select either an individual name or an option to select all names.

All
Name 1
Name 2
Name 3


I have other reports running this way, that don't use a cross tab query, and I have no problems.



Angie

http://www.millardconsulting.ca

 

[dhookom]

Maybe you should tell us what the problem is or what the symptoms are when you try this with a crosstab query. Does your crosstab results have the field [EmpName]? What is the SQL view of your crosstab?

I'm not sure how sending:
EmpName = 'All'
would select any employees other than those named "All".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

OK, When I run this report with the cross tab query. I get all the employees, but I cannot run the query for just an individual employee, and I need this facility to give the users the option. What I do on the other reports that don't user a cross tab is:

strCriteria = Me.lstName.Column(0, lstName.ListIndex)
If strCriteria = "All" Then
DoCmd.OpenReport strDocName, acViewPreview
Else
DoCmd.OpenReport strDocName, acPreview, , "[empname]='" & strCriteria & "'"
End If

Here is the SQL view of the cross tab

TRANSFORM Sum(sales.soldNo) AS SumOfsoldNo
SELECT emp.empname
FROM emp INNER JOIN sales ON emp.empno = sales.empno
WHERE (((sales.deldate) Between getsdate() And getedate()))
GROUP BY emp.empname
PIVOT sales.sos;

Thanks

Angie

http://www.millardconsulting.ca

 

[dhookom]

Interesting... Are you suggesting the report based on a crosstab will open properly if you select "All"?

You still haven't told us what the error is. What is the error message or issue when you attempt to set the criteria?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

Yes, If I choose All the report works correctly. It lists all the employees. If I choose an individual name, the cross tab query again lists all the employees and the report will show the first employee in the cross tab query.

Thanks



Angie

http://www.millardconsulting.ca

 

[dhookom]

I'm not sure what you mean by "the cross tab query again lists all the employees and the report will show the first employee in the cross tab query". Does your report show one or all employees?

I set all this up in a sample database and it all worked as expected.
Try add a line before the open report:

        MsgBox "strCriteria: " & strCriteria
        DoCmd.OpenReport strDocName, acPreview, , "[empname]='" & strCriteria & "'"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

If I select "All" the report will show all employees. If I select "Name 1" then instead of getting all their details I get the first employee only, and not the one I selected.

Angie

http://www.millardconsulting.ca

 

[dhookom]

Angie,
What do you see in the MsgBox?
I expect your issue may be
strCriteria = Me.lstName.Column(0, lstName.ListIndex)
Is there a reason why you don't use:
strCriteria = Me.lstName

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

In the Message Box I get the name correctly.

Angie

http://www.millardconsulting.ca

 

[dhookom]

Again:
Is there a reason why you don't use:
strCriteria = Me.lstName

What happens if you hard-code
"[EmpName]='Smith'"
in the where clause?

What happens if you place "Smith" directly in the query criteria?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

The only reason I wasn't using strCriteria= Me.LstName was that I'd been using 2 columns in the list boxes. I've changed that now to read strCriteria = me.lstname, and there is no change.

When I hard-code the name, I get exactly the same thing happen, there is no change.

I get the first name and not the one I selected.

It just seems to ignore that criteria all together with the cross tab query

Thanks



Angie

http://www.millardconsulting.ca

 

[dhookom]

So, you are saying if your crosstab is:

TRANSFORM Sum(sales.soldNo) AS SumOfsoldNo
SELECT emp.empname
FROM emp INNER JOIN sales ON emp.empno = sales.empno
WHERE (((sales.deldate) Between getsdate() And getedate())) AND empName ="Smith"
GROUP BY emp.empname
PIVOT sales.sos;

you will view the record for "Anderson" rather than "Smith"????

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

This gets confusing.

If I hardcode the name in the cross tab query it will work OK.

But it appears that when I try to pass the name in the where string(strCriteria) to open the report with the cross tab query it won't do it.



Angie

http://www.millardconsulting.ca

 

[dhookom]

Do you have any code in the On Open event of the report that changes the Filter property?

Have you tried to set the column headings property in the crosstab query?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

This is the only code that I have in the Open event.


Private Sub Report_Open(Cancel As Integer)
Dim qdf As QueryDef
Dim intX As Integer

Set db = CurrentDb()

Set qdf = db.QueryDefs("QryRptWeeklyNew")

Set rs = qdf.OpenRecordset()

intColumnCount = rs.Fields.Count

DoCmd.MoveSize 0, 0

End Sub

No I've not tried to set the column headings property. This is a cross tab with dynamic headings report.



Angie

http://www.millardconsulting.ca

 

[dhookom]

You are probably using the horrible Solutions.mdb crosstab report? This would most likely be your issue since your rs includes all records with no filter. Your report might send one record from the where clause but your report code doesn't use the same record.

If you want to continue with this ugly solution (my opinion) then you might want to modify your code to apply the same filter to the recordset as is applied to the report.

For dynamic crosstab reports, check my faster and much more flexible solution at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[angiem]

Yes, I was using the horrible solutions.mdb. I'll have a look at your solution.

Many thanks for all your help.



Angie

http://www.millardconsulting.ca