Data mismatch in criteria question

J

JHARRIS133

I am running a query on Sales data, which contains Opportunity Status and
Fallout Reason field. In the Opp. Status field, the options are Closed or
Closed-Won. In the case where the status is Closed, the Fallout reason will
have a list of various different reasons, such as No-Bid, Cancelled,
LOST-PRICE, LOST-SPEED, etc. I need to have a single number each for Lost,
No-Bid, Cancelled, and Won. I have written some VBA code to assist with this:
Function GetStatus(ByVal strStatus As String, Optional strFallout As String
= "") _ As String
If strStatus <> "Closed-Won" Then
If (Left(strFallout, 4) <> "LOST") Then
GetStatus = strFallout
Else
GetStatus = "Lost"
End If
Else
GetStatus = strStatus
End If
End Function

When I run this in my query, I receive a Data Mismatch in criteria error.
Please help!
 
D

Douglas J. Steele

Do you have fields in your table where the value you're passing to the
function is Null?

You have two options.

One is to change strStatus to Variant in your function declaration, and add
logic in the function to handle Null values.

The other is to pass Nz([MyFieldThatMayBeNull], "") to the function, rather
than strictly the field.
 
J

JHARRIS133

Thank you much, I changed the datatype to a Variant and it works properly!

Douglas J. Steele said:
Do you have fields in your table where the value you're passing to the
function is Null?

You have two options.

One is to change strStatus to Variant in your function declaration, and add
logic in the function to handle Null values.

The other is to pass Nz([MyFieldThatMayBeNull], "") to the function, rather
than strictly the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



JHARRIS133 said:
I am running a query on Sales data, which contains Opportunity Status and
Fallout Reason field. In the Opp. Status field, the options are Closed or
Closed-Won. In the case where the status is Closed, the Fallout reason
will
have a list of various different reasons, such as No-Bid, Cancelled,
LOST-PRICE, LOST-SPEED, etc. I need to have a single number each for
Lost,
No-Bid, Cancelled, and Won. I have written some VBA code to assist with
this:
Function GetStatus(ByVal strStatus As String, Optional strFallout As
String
= "") _ As String
If strStatus <> "Closed-Won" Then
If (Left(strFallout, 4) <> "LOST") Then
GetStatus = strFallout
Else
GetStatus = "Lost"
End If
Else
GetStatus = strStatus
End If
End Function

When I run this in my query, I receive a Data Mismatch in criteria error.
Please help!
Click to expand...
Click to expand...
 
Top