open report criteria based on form

[Guest]

our office just upgraded us from 97 to xp. Most of the access conversions
have worked except for this one.

The form was designed to open a report based on two sets of criteria, one
being the year, and the other being another set of criteria.

My old code on the form is thus:

dim.........

stWhereCategory = "reportCodes = Forms![rchOccRep]!lstSearch"
stWhereMonth = "occMm = Forms![rchOccRep]!lstSearch"
stYear = "occYy = Forms![rchOccRep]!lstYears"
With DoCmd
Select Case Me.occRepOption
Case 1
If IsNull(Forms![rchOccRep]!lstSearch) Then
.OpenReport stDocName, PrintMode
Else: .OpenReport stDocName, PrintMode, , _
stYear + " and " + stWhereCategory
End If
Case 2
If IsNull(Forms![rchOccRep]!lstSearch) Then
.OpenReport stDocName, PrintMode
Else: .OpenReport stDocName, PrintMode, , _
stYear + " and " + stWhereMonth
End If

etc........

This worked w/out a hitch in the old database. But now, depending on the
second set of criteria, I either get a blank report or a report based on the
very first option of the second list box, regardless of what was chosen.

I dunno if this will help, but here is my code for the list boxes as well.

dim....

strSQL = "SELECT DISTINCT (tblOccRep.occYy), "
stCategory = strSQL & _
"(tblReportCodes.codeName)" & _
"FROM tblReportCodes RIGHT JOIN (tblYear RIGHT JOIN tblOccRep ON " & _
"(tblYear.id) = (tblOccRep.occYy)) ON (tblReportCodes.id) = " & _
"(tblOccRep.reportCodes)" & _
"WHERE (((tblYear.reportYear) Like [Forms]![rchOccRep]![lstYears]))" & _
"ORDER BY (tblReportCodes.codeName);"
stMonth = strSQL & _
"(tblMonth.monthName)" & _
"FROM tblMonth RIGHT JOIN (tblYear RIGHT JOIN tblOccRep ON " & _
"(tblYear.id) = (tblOccRep.occYy)) ON (tblMonth.id) = " & _
"(tblOccRep.occMm)" & _
"WHERE (((tblMonth.monthName) Is Not Null) And " & _
"((tblYear.reportYear) Like [Forms]![rchOccRep]![lstYears]))" & _
"ORDER BY (tblMonth.monthName);"

With Me.lstSearch
Select Case Me.occRepOption
Case 1
.RowSource = stCategory
Case 2
.RowSource = stMonth

etc...........

I'm hoping someone can spot where the error is and guide me to the right
direction.

Thanks in advance for any help.

 

[Allen Browne]

Concatenate the values into the string, rather than the references.

First example:
stWhereCategory = "reportCodes = " & Forms![rchOccRep]!lstSearch
If the bound column of lstSearch is a Text field, you need extra quotes:
stWhereCategory = "reportCodes = """ & Forms![rchOccRep]!lstSearch &
""""

If that does not solve the problem immediately, try declaring the parameters
in the SQL statement. That way you get to define the data type. JET is quite
bad at getting the data type wrong when the types are undefined. You can
also help by setting the Format property of the unbound text boxes on your
form: General Number of numeric values, General Date for date values. More
on this:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

our office just upgraded us from 97 to xp. Most of the access conversions
have worked except for this one.

The form was designed to open a report based on two sets of criteria, one
being the year, and the other being another set of criteria.

My old code on the form is thus:

dim.........

stWhereCategory = "reportCodes = Forms![rchOccRep]!lstSearch"
stWhereMonth = "occMm = Forms![rchOccRep]!lstSearch"
stYear = "occYy = Forms![rchOccRep]!lstYears"
With DoCmd
Select Case Me.occRepOption
Case 1
If IsNull(Forms![rchOccRep]!lstSearch) Then
.OpenReport stDocName, PrintMode
Else: .OpenReport stDocName, PrintMode, , _
stYear + " and " + stWhereCategory
End If
Case 2
If IsNull(Forms![rchOccRep]!lstSearch) Then
.OpenReport stDocName, PrintMode
Else: .OpenReport stDocName, PrintMode, , _
stYear + " and " + stWhereMonth
End If

etc........

This worked w/out a hitch in the old database. But now, depending on the
second set of criteria, I either get a blank report or a report based on
the
very first option of the second list box, regardless of what was chosen.

I dunno if this will help, but here is my code for the list boxes as well.

dim....

strSQL = "SELECT DISTINCT (tblOccRep.occYy), "
stCategory = strSQL & _
"(tblReportCodes.codeName)" & _
"FROM tblReportCodes RIGHT JOIN (tblYear RIGHT JOIN tblOccRep ON " & _
"(tblYear.id) = (tblOccRep.occYy)) ON (tblReportCodes.id) = " & _
"(tblOccRep.reportCodes)" & _
"WHERE (((tblYear.reportYear) Like [Forms]![rchOccRep]![lstYears]))" &
_
"ORDER BY (tblReportCodes.codeName);"
stMonth = strSQL & _
"(tblMonth.monthName)" & _
"FROM tblMonth RIGHT JOIN (tblYear RIGHT JOIN tblOccRep ON " & _
"(tblYear.id) = (tblOccRep.occYy)) ON (tblMonth.id) = " & _
"(tblOccRep.occMm)" & _
"WHERE (((tblMonth.monthName) Is Not Null) And " & _
"((tblYear.reportYear) Like [Forms]![rchOccRep]![lstYears]))" & _
"ORDER BY (tblMonth.monthName);"

With Me.lstSearch
Select Case Me.occRepOption
Case 1
.RowSource = stCategory
Case 2
.RowSource = stMonth

etc...........

I'm hoping someone can spot where the error is and guide me to the right
direction.

Thanks in advance for any help.