A2007 error 3464 setting group level in Report_Open

[VBAPrincess]

(I am using Access 2007, WinXP Pro)
I have a couple of reports that are output as a result of a search a user has made where a number of fields are available as filters. My code creates the filter, generates a temp table, and then displays the results to the user based on what resides in the temp table. After viewing the results, the user can print two different reports -- a summary or a detail -- of all records or just selected records.

On the search form, I have two drop-downs that give the user choices for sorting purposes. I have code to have the reports sort by these fields as well. I used code I found here

http://bytes.com/topic/access/answers/711146-change-report-sort-order-vba

and it seems to work fine on my detail report. However, the summary report keeps throwing an error 3464 Data type mismatch in criteria expression.

I have stepped thru the code and the call to open the report is made, it runs thru the Report_Open code, returns to the calling procedure in the Search form and errors. I have an error handler in the Report_Open and it doesn't stop there, it just errors when it goes back to the DoCmd.OpenReport call.

The error occurs whether I want all records or just ones I have selected. (I have a Y/N field called Select in my temp table so I can choose some of the records) I don't understand what could be different from the detail.

I made sure that the summary report has two grouping levels (these have no headers, footers, etc -- they are just used for sorting) to begin with so that when I update them to my desired fields, then it can update.

Here's my code. Line 19 in the cmdFilter_Click procedure is where the error occurs:
frmSearch
Private Sub cmdFilter_Click()
1 On Error GoTo ErrorHandler
Dim strFind As String, strFilter As String

2 DoCmd.SetWarnings False
Dim strOrderBy As String
Dim strFirst As String
Dim strSecond As String

3 strFirst = Replace(Me.cboFirst, " ", "")

4 If Not IsNull(Me.cboSecond) Then
5 strSecond = Replace(Me.cboSecond, " ", "")
6 End If
7 strOrderBy = ";" & strFirst & "," & strSecond
...
'PRINT SUMMARY
19 DoCmd.OpenReport "rptSummaryCompSales", acViewPreview, OpenArgs:=strOrderBy
...
End Sub

Report
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Report_Open_Error

1 If Forms!frmSearch!cboInclude = 1 Then
2 Me.RecordSource = "qrySearchResults"
3 Else
4 Me.RecordSource = "qrySearchResultsSubset"
5 End If

6 nStart = InStr(1, Me.OpenArgs, ";")
7 If nStart > 0 Then
' if there's a semi-colon in openargs, take the second param as orderby
8 cOrderby = Right(Me.OpenArgs, Len(Me.OpenArgs) - nStart)
9 End If
10 If Nz(cOrderby) = "" Then
' default orderby for this report
11 cOrderby = "[SaleDate],[Loc]"
12 End If
' make sure corderby ends with a comma
13 cOrderby = cOrderby & IIf(Right(cOrderby, 1) <> ",", ",", "")
14 nStart = 1
15 nEnd = 1
16 For nIdx = 0 To 1
' sorting by name is actually by two fields, so we only have 8 sort fields to work with
17 nEnd = InStr(nStart, cOrderby, ",")
18 If nEnd > 0 Then
19 cFld = Mid(cOrderby, nStart, nEnd - nStart)
20 nStart = nEnd + 1
21 End If
22 Me.GroupLevel(nIdx).ControlSource = cFld
23 Next nIdx

Exit Sub

Report_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Report_Open of VBA Document Report_rptSummaryCompSales"
End Sub

TIA!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[dhookom]

I would try use Debug.Print to troubleshoot:

18            If nEnd > 0 Then
19                cFld = Mid(cOrderby, nStart, nEnd - nStart)
19.5              Debug.Print nIdx & ": " & cFld 
20                nStart = nEnd + 1
21            End If

If you have duplicate field names or spaces in field names you may experience issues.

Duane
Hook'D on Access
MS Access MVP

 

[VBAPrincess]

Thanks Duane for the reply. I have walked thru it (F8) and the field names are ok. No spaces and the fields are correct. In fact, the group level (or sort) is already set as 0=SaleDate and 1=Loc, and what I'm trying to do is have it swap them so the report is sorted by Loc (location) and then by SaleDate. However, it just keeps giving me the error.

I modified the report so that Loc was the first default sort, and then SaleDate. It worked ok and occasionally I got errors. Ex: choosing SalePrice as the first sort causes an error, but NumUnits does not.

SOLUTION: I made both of the sorts = Loc and now it seems to work no matter what combination. I think it didn't like "exchanging" the SaleDate sort with other data types.



Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!