Type Mismatch error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to create a report whereby the user can pick various criteria to
base a report on and then hit the preview button to view the report.
However, I'm getting a 'Type Mismatch' error.

Each criteria is in its own unbound textbox ;
TEXTBOX NAME TABLENAME FIELDNAME

AgentNameSelect AgentDetails NameAgent
CoachSelect AgentDetails Coach
CentreSelectLabel AgentDetails Centre
Text176 DateWeekPeriod Period/Week

The Preview button is found on the Startup Form within the file called 2nd
Level Support Table.

Below is the coding currently being used... What am I doing wrong?

regards,


Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click:

Dim pstrCriteria As String

If Not IsNull(Me.AgentNameSelect) Then
pstrCriteria = "[AgentDetails].[NameAgent]='" & Me!AgentNameSelect &
"'And"
End If

If Not IsNull(Me.CoachSelect) Then
pstrCriteria = "[AgentDetails].[Coach]='" & Me!CoachSelect & "'AND "
End If

If Not IsNull(Me.CentreSelectLabel) Then
pstrCriteria = "[AgentDetails].[Centre]='" & Me!CentreSelectLabel &
"'AND "
End If

If Not IsNull(Me.ProductSelect) Then
pstrCriteria = "[Product / Service].[Product / Service]='" &
Me!ProductSelect & "'AND "
End If

If Not IsNull(Me.Combo221) Then
pstrCriteria = "[Reason].[Reason]='" & Me!Combo221 & "'AND "
End If

If Not IsNull(Me.Text176) Then
pstrCriteria = "[DateWeekPeriod].[Period/Week]='" & Me!DateWeekPeriod &
"'"
End If

If Right$(pstrCriteria, 4) + "AND" Then
pstrCriteria = Left$(pstrCriteria, Len(pstrCriteria) - 4)
End If


Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview,
pstrCriteria

Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, pstrCriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview,
pstrCriteria
End Select
Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Error$
Resume Exit_PreviewReport_Click

End Sub
 
You're missing a space after the And; corrected code below:

If Not IsNull(Me.AgentNameSelect) Then
pstrCriteria = "[AgentDetails].[NameAgent]='" & Me!AgentNameSelect &
"'And "
End If
 
I'm still getting the error message... thx

Ken Snell (MVP) said:
You're missing a space after the And; corrected code below:

If Not IsNull(Me.AgentNameSelect) Then
pstrCriteria = "[AgentDetails].[NameAgent]='" & Me!AgentNameSelect &
"'And "
End If

--

Ken Snell
<MS ACCESS MVP>


GailK said:
Hi,

I'm trying to create a report whereby the user can pick various criteria
to
base a report on and then hit the preview button to view the report.
However, I'm getting a 'Type Mismatch' error.

Each criteria is in its own unbound textbox ;
TEXTBOX NAME TABLENAME FIELDNAME

AgentNameSelect AgentDetails NameAgent
CoachSelect AgentDetails Coach
CentreSelectLabel AgentDetails Centre
Text176 DateWeekPeriod
Period/Week

The Preview button is found on the Startup Form within the file called 2nd
Level Support Table.

Below is the coding currently being used... What am I doing wrong?

regards,


Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click:

Dim pstrCriteria As String

If Not IsNull(Me.AgentNameSelect) Then
pstrCriteria = "[AgentDetails].[NameAgent]='" & Me!AgentNameSelect &
"'And"
End If

If Not IsNull(Me.CoachSelect) Then
pstrCriteria = "[AgentDetails].[Coach]='" & Me!CoachSelect & "'AND "
End If

If Not IsNull(Me.CentreSelectLabel) Then
pstrCriteria = "[AgentDetails].[Centre]='" & Me!CentreSelectLabel &
"'AND "
End If

If Not IsNull(Me.ProductSelect) Then
pstrCriteria = "[Product / Service].[Product / Service]='" &
Me!ProductSelect & "'AND "
End If

If Not IsNull(Me.Combo221) Then
pstrCriteria = "[Reason].[Reason]='" & Me!Combo221 & "'AND "
End If

If Not IsNull(Me.Text176) Then
pstrCriteria = "[DateWeekPeriod].[Period/Week]='" & Me!DateWeekPeriod &
"'"
End If

If Right$(pstrCriteria, 4) + "AND" Then
pstrCriteria = Left$(pstrCriteria, Len(pstrCriteria) - 4)
End If


Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview,
pstrCriteria

Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview,
pstrCriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview,
pstrCriteria
End Select
Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Error$
Resume Exit_PreviewReport_Click

End Sub
Click to expand...
Click to expand...
 
A few things.

As you've written it, your code is only going to pick 1 condition, not AND
them together (as you apparently want). That's because you're overwriting
pstrCriteria inside each IF construct. You need:

If Not IsNull(Me.CoachSelect) Then
pstrCriteria = pstrCriteria & _
"[AgentDetails].[Coach]='" & Me!CoachSelect & "'AND "
End If

rather than what you've got.

Secondly, your code is assuming that every field type is text. Is that the
case? For numeric fields, loose the single quotes in your criteria. For
example, assuming that Period/Week is a numeric field, you'd want:

If Not IsNull(Me.Text176) Then
pstrCriteria = _
pstrCriteria & "[DateWeekPeriod].[Period/Week]=" & Me!DateWeekPeriod
End If

Finally, your check to remove the final AND is incorrect. You're stripping
off the last 4 characters, but you're only comparing them to 3 characters
(assuming you fix the + that should be an =):

If Right$(pstrCriteria, 4) = "AND " Then
pstrCriteria = Left$(pstrCriteria, Len(pstrCriteria) - 4)
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GailK said:
Hi,

I'm trying to create a report whereby the user can pick various criteria
to
base a report on and then hit the preview button to view the report.
However, I'm getting a 'Type Mismatch' error.

Each criteria is in its own unbound textbox ;
TEXTBOX NAME TABLENAME FIELDNAME

AgentNameSelect AgentDetails NameAgent
CoachSelect AgentDetails Coach
CentreSelectLabel AgentDetails Centre
Text176 DateWeekPeriod
Period/Week

The Preview button is found on the Startup Form within the file called 2nd
Level Support Table.

Below is the coding currently being used... What am I doing wrong?

regards,


Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click:

Dim pstrCriteria As String

If Not IsNull(Me.AgentNameSelect) Then
pstrCriteria = "[AgentDetails].[NameAgent]='" & Me!AgentNameSelect &
"'And"
End If

If Not IsNull(Me.CoachSelect) Then
pstrCriteria = "[AgentDetails].[Coach]='" & Me!CoachSelect & "'AND "
End If

If Not IsNull(Me.CentreSelectLabel) Then
pstrCriteria = "[AgentDetails].[Centre]='" & Me!CentreSelectLabel &
"'AND "
End If

If Not IsNull(Me.ProductSelect) Then
pstrCriteria = "[Product / Service].[Product / Service]='" &
Me!ProductSelect & "'AND "
End If

If Not IsNull(Me.Combo221) Then
pstrCriteria = "[Reason].[Reason]='" & Me!Combo221 & "'AND "
End If

If Not IsNull(Me.Text176) Then
pstrCriteria = "[DateWeekPeriod].[Period/Week]='" & Me!DateWeekPeriod &
"'"
End If

If Right$(pstrCriteria, 4) + "AND" Then
pstrCriteria = Left$(pstrCriteria, Len(pstrCriteria) - 4)
End If


Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview,
pstrCriteria

Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview,
pstrCriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview,
pstrCriteria
End Select
Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Error$
Resume Exit_PreviewReport_Click

End Sub
Click to expand...
 
Back
Top