Type Mismatch

D

Dan @BCBS

This code reads 3 list boxes and produces the report based on those choices.
The last one "Reviewers" is giving me an error Type Mismatch.
Everything is the same as the other two, I don't see whats wrong???
Thank you....


Private Sub cmdKeyIndicators_Click()
On Error GoTo Err_cmdKeyIndicators_Click

Dim stDocName As String

'these need to be declared also
Dim stAreaList As String
Dim stProductList As String
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant

stDocName = "Report1"
stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If

'get reviewer in ListReviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[gbulocation] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[insurancetype] " & stProductList & " And "
End If

'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " " And ""
End If

'remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' debug - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdKeyIndicators:
Exit Sub

Err_cmdKeyIndicators_Click:

MsgBox err.Description
Resume Exit_cmdKeyIndicators

End Sub
///////////////////
 
J

J. Goddard

Hi -

you haven't given us much to work with -

When do you ge the error? When you are building the criteria string, or
when you open the report? The code looks OK.

If you are getting the error when you open the report, I would check the
data type of [reviewer]. Your code implies it is text, since you use
quotes around the values, but if it is numeric, that might be the cause
of the error.

John

P.S. I note that although you check for Nulls in txtStart and txtEnd,
you don't use them in the code - is this deliberate? Just an observation.

J

This code reads 3 list boxes and produces the report based on those choices.
The last one "Reviewers" is giving me an error Type Mismatch.
Everything is the same as the other two, I don't see whats wrong???
Thank you....


Private Sub cmdKeyIndicators_Click()
On Error GoTo Err_cmdKeyIndicators_Click

Dim stDocName As String

'these need to be declared also
Dim stAreaList As String
Dim stProductList As String
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant

stDocName = "Report1"
stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If

'get reviewer in ListReviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[gbulocation] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[insurancetype] " & stProductList & " And "
End If

'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " " And ""
End If

'remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' debug - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdKeyIndicators:
Exit Sub

Err_cmdKeyIndicators_Click:

MsgBox err.Description
Resume Exit_cmdKeyIndicators

End Sub
///////////////////
Click to expand...
 
D

Dan @BCBS

1st. about your side note: I only want to check for null on the date range.

2nd. Let me explain what I'm doing and whats happening.
As I mentioned this is a form with 3 list boxes.
If I make a choice in list box (1 or 2) or (1 and 2) I get exactally what I
asked for, but as soon as I pick from the 3rd list box I get the error "Type
Mismatch"..
The value is text - not numeric.
The error occurs when I click the command button to run this code.
The only difference between the first two list boxes and the last on is that
the first two are from tables, the 3rd is from a query, but the only thing
the query does is ask if one field is true.
Also, all 3 of the list boxes are all Multi Select=simple.

What else can I explain? Any suggestions??





J. Goddard said:
Hi -

you haven't given us much to work with -

When do you ge the error? When you are building the criteria string, or
when you open the report? The code looks OK.

If you are getting the error when you open the report, I would check the
data type of [reviewer]. Your code implies it is text, since you use
quotes around the values, but if it is numeric, that might be the cause
of the error.

John

P.S. I note that although you check for Nulls in txtStart and txtEnd,
you don't use them in the code - is this deliberate? Just an observation.

J

This code reads 3 list boxes and produces the report based on those choices.
The last one "Reviewers" is giving me an error Type Mismatch.
Everything is the same as the other two, I don't see whats wrong???
Thank you....


Private Sub cmdKeyIndicators_Click()
On Error GoTo Err_cmdKeyIndicators_Click

Dim stDocName As String

'these need to be declared also
Dim stAreaList As String
Dim stProductList As String
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant

stDocName = "Report1"
stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If

'get reviewer in ListReviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[gbulocation] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[insurancetype] " & stProductList & " And "
End If

'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " " And ""
End If

'remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' debug - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdKeyIndicators:
Exit Sub

Err_cmdKeyIndicators_Click:

MsgBox err.Description
Resume Exit_cmdKeyIndicators

End Sub
///////////////////
Click to expand...
Click to expand...
 
J

J. Goddard

Which line of the code is causing the error? The easiest way to find
out is to comment out the On Error ... statement, run the code again,
and then open the debug window to see where it is when it fails.

If it is on the Docmd.openreport line, then stlinkcriteria is the
problem; if so please post it. Use "debug.print strlinkCriteria "
to write it to the immediate window.

John

1st. about your side note: I only want to check for null on the date range.

2nd. Let me explain what I'm doing and whats happening.
As I mentioned this is a form with 3 list boxes.
If I make a choice in list box (1 or 2) or (1 and 2) I get exactally what I
asked for, but as soon as I pick from the 3rd list box I get the error "Type
Mismatch"..
The value is text - not numeric.
The error occurs when I click the command button to run this code.
The only difference between the first two list boxes and the last on is that
the first two are from tables, the 3rd is from a query, but the only thing
the query does is ask if one field is true.
Also, all 3 of the list boxes are all Multi Select=simple.

What else can I explain? Any suggestions??





:

Hi -

you haven't given us much to work with -

When do you ge the error? When you are building the criteria string, or
when you open the report? The code looks OK.

If you are getting the error when you open the report, I would check the
data type of [reviewer]. Your code implies it is text, since you use
quotes around the values, but if it is numeric, that might be the cause
of the error.

John

P.S. I note that although you check for Nulls in txtStart and txtEnd,
you don't use them in the code - is this deliberate? Just an observation.

J

This code reads 3 list boxes and produces the report based on those choices.
The last one "Reviewers" is giving me an error Type Mismatch.
Everything is the same as the other two, I don't see whats wrong???
Thank you....


Private Sub cmdKeyIndicators_Click()
On Error GoTo Err_cmdKeyIndicators_Click

Dim stDocName As String

'these need to be declared also
Dim stAreaList As String
Dim stProductList As String
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant

stDocName = "Report1"
stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If

'get reviewer in ListReviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[gbulocation] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[insurancetype] " & stProductList & " And "
End If

'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " " And ""
End If

'remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' debug - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdKeyIndicators:
Exit Sub

Err_cmdKeyIndicators_Click:

MsgBox err.Description
Resume Exit_cmdKeyIndicators

End Sub
///////////////////
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Dan,

You really should stay with the same thread......I'm working nights and I
just got back on the computer.


See your previous thread from 10/6/06 12:07pm


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Dan @BCBS said:
This code reads 3 list boxes and produces the report based on those choices.
The last one "Reviewers" is giving me an error Type Mismatch.
Everything is the same as the other two, I don't see whats wrong???
Thank you....


Private Sub cmdKeyIndicators_Click()
On Error GoTo Err_cmdKeyIndicators_Click

Dim stDocName As String

'these need to be declared also
Dim stAreaList As String
Dim stProductList As String
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant

stDocName = "Report1"
stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If

'get reviewer in ListReviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[gbulocation] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[insurancetype] " & stProductList & " And "
End If

'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " " And ""
End If

'remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' debug - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdKeyIndicators:
Exit Sub

Err_cmdKeyIndicators_Click:

MsgBox err.Description
Resume Exit_cmdKeyIndicators

End Sub
///////////////////
Click to expand...
 
D

Dan @BCBS

I do appoligise, I'm late on roling this db out and that made me impatient.

Thanks for the help I can get it out now....


SteveS said:
Dan,

You really should stay with the same thread......I'm working nights and I
just got back on the computer.


See your previous thread from 10/6/06 12:07pm


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Dan @BCBS said:
This code reads 3 list boxes and produces the report based on those choices.
The last one "Reviewers" is giving me an error Type Mismatch.
Everything is the same as the other two, I don't see whats wrong???
Thank you....


Private Sub cmdKeyIndicators_Click()
On Error GoTo Err_cmdKeyIndicators_Click

Dim stDocName As String

'these need to be declared also
Dim stAreaList As String
Dim stProductList As String
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant

stDocName = "Report1"
stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If

'get reviewer in ListReviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[gbulocation] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[insurancetype] " & stProductList & " And "
End If

'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " " And ""
End If

'remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' debug - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdKeyIndicators:
Exit Sub

Err_cmdKeyIndicators_Click:

MsgBox err.Description
Resume Exit_cmdKeyIndicators

End Sub
///////////////////
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DoCmd.SendObject 2
3 list boxes 1 answer 9
3 choices 3
DoCmd.OpenQuery 4
records per list 4
ItemsSelected 1
Duplicates being produced 2
Combine 3 List box Choices 1

Top