Data Type Mismatch Error on 'Yes/No' fields in Query

[D Burke]

Hi,

I have a query in which I am pulling data based from a table based on
several choices in various listboxes.

Thus far I have found that every possible combination of possibilities
works - except those in which the choice is 'yes' or 'no' and is set to pull
data from a 'yes/no' type field in a table. I keep getting a 'Data Type
Mismatch' error where these are included.

I've included a segment of my code that refers to one of these fields
(there are several others, but are exactly the same save for the field
names).

If Forms!MailMergeExport!MutualFund.ItemsSelected.Count > 0 Then
strWhereMutualFund = "[Prospect Client Records].MF IN("
For Each varItem In Forms!MailMergeExport!MutualFund.ItemsSelected
strWhereMutualFund = strWhereMutualFund & "'" &
ctl8.ItemData(varItem) & "', "
Next varItem
'remove final comma/space and add ")"
strWhereMutualFund = Left(strWhereMutualFund, Len(strWhereMutualFund) -
2) & ")"
Else
strWhereMutualFund = "(1=1)"
End If

Does anyone have any suggestions as to how to resolve this?

Thanks in advance!!!

 

[Dan Artuso]

Hi,
Well the big question is what does your string evaluate to?
Have you done a debug.Print strWhereMutualFund
to examine it?
Offhand it looks to me like you'll end up with:
"[Prospect Client Records].MF IN('somevalue'[Prospect Client Records].MF IN('somevalue'

or something similar because you keep concatenating the whole string
onto itself..

Post the what you see in the debug window.

 

[John Spencer (MVP)]

Try changing this portion to

"...[Prospect Client Records].Advisory = True..."

You'll need to change the VBA code that builds the where clause for the Yes/No
fields so that it returns True or False and not a string. That may be as simple
as dropping the apostrophe from the string you are building. Then you would end
up with

"...[Prospect Client Records].Advisory IN (Yes)..."

Which will probably work, although I do prefer True or False vs Yes or No

Hi Dan,

Thanks for your quick response. The section of the string to which I am
referring is "...[Prospect Client Records].Advisory IN ('Yes')". The rest of
the string is correct as I've tried with all the different variables. The
only time I receive this error is when any of the three 'Yes/No' type fields
are selected. If these aren't included as part of the user's selection,
everything works beautifully.

Admittedly, I can't seem to recall how to find the debug window. I always
have a MsgBox appear to show me the string, thus the reason for my not
including the ENTIRE string.

Thanks!

HTH

Hi,
Well the big question is what does your string evaluate to?
Have you done a debug.Print strWhereMutualFund
to examine it?
Offhand it looks to me like you'll end up with:
"[Prospect Client Records].MF IN('somevalue'[Prospect Client Records].MF IN('somevalue'

or something similar because you keep concatenating the whole string
onto itself..

Post the what you see in the debug window.

--
HTH
Dan Artuso, Access MVP

Hi,

I have a query in which I am pulling data based from a table based on
several choices in various listboxes.

Thus far I have found that every possible combination of possibilities
works - except those in which the choice is 'yes' or 'no' and is set to pull
data from a 'yes/no' type field in a table. I keep getting a 'Data Type
Mismatch' error where these are included.

I've included a segment of my code that refers to one of these fields
(there are several others, but are exactly the same save for the field
names).

If Forms!MailMergeExport!MutualFund.ItemsSelected.Count > 0 Then
strWhereMutualFund = "[Prospect Client Records].MF IN("
For Each varItem In Forms!MailMergeExport!MutualFund.ItemsSelected
strWhereMutualFund = strWhereMutualFund & "'" &
ctl8.ItemData(varItem) & "', "
Next varItem
'remove final comma/space and add ")"
strWhereMutualFund = Left(strWhereMutualFund, Len(strWhereMutualFund) -
2) & ")"
Else
strWhereMutualFund = "(1=1)"
End If

Does anyone have any suggestions as to how to resolve this?

Thanks in advance!!!