'Data type mismatch in criteria expression' Error

[mark]

When placing a function into a field of query builder, I expect to be
able to apply a criteria.

For example, for the following simple function:

Function TestA(VarA) As String
If VarA > 100 Then TestA = VarA
End Function

I can insert a field in to the builder:

Expr1: TestA([RVID])

and apply the criteria <>"" to filter zero length results:



But I have a more complex function, whose return type is 'string' but
refuses to allow the criteria of <>"" to be set. It produces the 'Data
type mismatch in criteria expression' Error.

Here is the function :

Function SequenceGap(Bank As String, Number As Single, Iteration As
Long) As String
' Identifies a gap in the sequence of check numbers
Static LastNo As Single
Static LastBank As String
If LastNo = 0 Then LastNo = Number
If LastBank = "" Then LastBank = Bank
If (LastBank = Bank) And ((Number - LastNo) > Iteration) Then
SequenceGap = LastNo & " - " & Number
Else
SequenceGap = ""
End If
LastNo = Number
LastBank = Bank
End Function


insert field in to the builder:

Gap: SequenceGap([EBank Account],[Cheque],1)

CANNOT apply the criteria:

<>""

produces error: 'Data type mismatch in criteria expression'

 

[Golfinray]

When you get that message, you are trying to apply something to a datatype
that you can't apply to it. Like applying numeric functions to text or trying
to apply text functions to date datatypes.

 

[mark]

When you get that message, you are trying to apply something to a
datatype that you can't apply to it. Like applying numeric functions to
text or trying to apply text functions to date datatypes.

Agreed. As I said, the returned value of the function was with the
string type, so I expect <>"" criteria to work .

 

[boblarson]

Could it be that you have nulls that need handling?

Gap: SequenceGap(Nz([EBank Account],""),Nz([Cheque],0),1)

May not be, but it was just a thought
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[mark]

Could it be that you have nulls that need handling?

Gap: SequenceGap(Nz([EBank Account],""),Nz([Cheque],0),1)

May not be, but it was just a thought
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Function declared as a String - can't be nulls.

Function SequenceGap(Bank As String, Number As Single, Iteration As
Long) As String

The problem makes no sense to me - the simple function was fine, so why
the difference - I'm just wondering if any one has had this before and
has an explanation.

 

[boblarson]

I didn't say that the function was null, I said the parameters might be. A
number can be null so if you are passing a null then you could have a
problem.

But, also I don't think I would use the reserved word NUMBER as a variable
name.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

Could it be that you have nulls that need handling?

Gap: SequenceGap(Nz([EBank Account],""),Nz([Cheque],0),1)

May not be, but it was just a thought
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Function declared as a String - can't be nulls.

Function SequenceGap(Bank As String, Number As Single, Iteration As
Long) As String

The problem makes no sense to me - the simple function was fine, so why
the difference - I'm just wondering if any one has had this before and
has an explanation.

 

[mark]

I didn't say that the function was null, I said the parameters might
be. A number can be null so if you are passing a null then you could
have a problem.

Since parameters have their types specified, the function would fail
will #Error# returned into the fields. The function does not fail,
returns a result, I just can't set any criteria.

But, also I don't think I would use the reserved word NUMBER as a
variable name.

Have changed this - no improvement.

 

[a a r o n . k e m p f]

Dude
ROFL

Access queries randomly crap out with cryptic messages like this.

This is one of the main reasons I moved to SQL Server a decade ago.
Things just work with SQL Server.

With access; you stack 3 queries on top of each other and at least one
of them is bound to crap out

Because JET is obsolete; Microsoft has decided not to fix bugs like
this.

So:

a) work on a buggy ass database for the rest of your life- MS hasn't
made a single investment in JET in the past decade
b) come to the promised land and learn the worlds most popular
database engine www.microsoft.com/sql

aren't you tired of all the random error messages when MS Access goes
titsup?