'Data type mismatch error' using Mid function in a query

[martlaco1]

Trying to fix a query that (I thought) had worked once upon a time, and
I keep getting a Data Type Mismatch error whenever I enter any criteria
for an expression using a Mid function. Without the criteria, the Mid
function returns the values when I run the query. So if one of the
values is a "t" (no quotes), can I not ask to isolate that record by
putting "t" as a criteria? Nope - error, error.

If I put it within the expression itself (adding ="t") at the end, the
query returns T or F values (0 or -1). Ok, so can't I again isolate
the -1 (True) value here by putting -1 in the criteria. Again,
apparently not.

Should this not be a simple matter - what am I missing?

Any help is greatly appreciated. Thanks!
Martin Lacoste
P.S. Here is the code if it helps at all. It works as it should when I
remove the ="t" criteria, but I was to filter out records with such a
criteria, which I cannot.

SELECT Mid([Incipit],InStr(1,[Incipit],[WrongCase]),[LengthWrong]) AS
WrongWord, [New Index].CantusIDNumber , [New Index].Incipit,
InStr(1,[Incipit],[WrongCase]) AS [Position],
Mid([Incipit],(InStr([Incipit],[WrongCase])-1),1) AS [SpaceBefore?],
Asc(Left(Mid([Incipit],InStr(1,[Incipit],[WrongCase]),[LengthWrong]),1))
AS [Ascii Value of Wrong Character], InStr([Incipit],[Ignore1]) AS
[Exception]
FROM tblSpellingErro rs, [New Index]
WHERE (((InStr(1,[Incipit],[WrongCase]))>1) AND
((Mid([Incipit],(InStr([Incipit],[WrongCase])-1),1))="t") AND
((InStr([Incipit],[Ignore1]))=1 Or (InStr([Incipit],[Ignore1]))=0))
ORDER BY Mid([Incipit],InStr(1,[Incipit],[WrongCase]),[LengthWrong]),
[New Index].CantusIDNumber ;

[martlaco1]

BTW - using Access2000.
Martin

[martlaco1]

BTW - using Access2000
Martin

[Dirk Goldgar]

<ma*******@roge rs.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com

Trying to fix a query that (I thought) had worked once upon a time,
and I keep getting a Data Type Mismatch error whenever I enter any
criteria for an expression using a Mid function. Without the
criteria, the Mid function returns the values when I run the query.
So if one of the values is a "t" (no quotes), can I not ask to
isolate that record by putting "t" as a criteria? Nope - error,
error.

If I put it within the expression itself (adding ="t") at the end, the
query returns T or F values (0 or -1). Ok, so can't I again isolate
the -1 (True) value here by putting -1 in the criteria. Again,
apparently not.

Should this not be a simple matter - what am I missing?

Any help is greatly appreciated. Thanks!
Martin Lacoste
P.S. Here is the code if it helps at all. It works as it should when
I remove the ="t" criteria, but I was to filter out records with
such a criteria, which I cannot.

SELECT Mid([Incipit],InStr(1,[Incipit],[WrongCase]),[LengthWrong]) AS
WrongWord, [New Index].CantusIDNumber , [New Index].Incipit,
InStr(1,[Incipit],[WrongCase]) AS [Position],
Mid([Incipit],(InStr([Incipit],[WrongCase])-1),1) AS [SpaceBefore?],
Asc(Left(Mid([Incipit],InStr(1,[Incipit],[WrongCase]),[LengthWrong]),1)) AS [Ascii Value of Wrong Character], InStr([Incipit],[Ignore1]) AS
[Exception]
FROM tblSpellingErro rs, [New Index]
WHERE (((InStr(1,[Incipit],[WrongCase]))>1) AND
((Mid([Incipit],(InStr([Incipit],[WrongCase])-1),1))="t") AND
((InStr([Incipit],[Ignore1]))=1 Or (InStr([Incipit],[Ignore1]))=0))
ORDER BY Mid([Incipit],InStr(1,[Incipit],[WrongCase]),[LengthWrong]),
[New Index].CantusIDNumber ;

From what you describe, I suspect that the calculation or the comparison
returns an error for one or more records, but that the error-producing
calculation is not actually performed until it has to be -- this is what
Jet usually does when a a query calls a function, to save time. When
you apply a criterion to the field, though, the calculation has to be
evaluated for all records right up front.

This is just speculation, but I would check all records for values of
Incipit and WrongCase such that Incipit doesn't contain WrongCase, or
contains it starting at the first position in the string. Either of
those situations, it seems to me, will give an error. I don't *think*
Null in either of those fields would raise an error, but that may be
worth checking for, too.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)