VBAPrincess
Programmer
(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 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!
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 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!