Data type mismatch error - why?

[Martin Lacoste]

Is there some issue with using too many left/right/mid/len functions
in queries? Depending on the usage, they work fine, but... then
there's here:

SELECT Master_CAO.Inci pit, Master_CAO.Full Text, Len([Incipit]) AS
[Length of Incipit], Left([Master_CAO]![FullText],Len([Incipit])) AS
[FullText-same length]
FROM Master_CAO
WHERE (((Left([FullText],Len([Incipit])))<>[Incipit]));

I'm trying to compare two fields, Incipit and FullText - FullText is a
longer version of Incipit, so I only want to compare the first part of
Fulltext, hence the len function. I can view this truncated text no
problem, but once I put in any criteria (<> in this case), I get a
data type mismatch error, which stymies me, since the data type
doesn't seem to have anything to do with it (Incipit is a text field,
FullText is a memo field; changing Incipit to a memo field doesn't
remove the error).

Should I look at using VBA?

Any ideas?
Thanks!
Martin Lacoste

[Douglas J. Steele]

Re: Data type mismatch error - why?

What happens if you leave out the WHERE clause. Do each of the fields
returned looked right?

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)



"Martin Lacoste" <martlaco1@roge rs.com> wrote in message
news:d497f955.0 408110608.ecaa1 dc@posting.goog le.com...[color=blue]
> Is there some issue with using too many left/right/mid/len functions
> in queries? Depending on the usage, they work fine, but... then
> there's here:
>
> SELECT Master_CAO.Inci pit, Master_CAO.Full Text, Len([Incipit]) AS
> [Length of Incipit], Left([Master_CAO]![FullText],Len([Incipit])) AS
> [FullText-same length]
> FROM Master_CAO
> WHERE (((Left([FullText],Len([Incipit])))<>[Incipit]));
>
> I'm trying to compare two fields, Incipit and FullText - FullText is a
> longer version of Incipit, so I only want to compare the first part of
> Fulltext, hence the len function. I can view this truncated text no
> problem, but once I put in any criteria (<> in this case), I get a
> data type mismatch error, which stymies me, since the data type
> doesn't seem to have anything to do with it (Incipit is a text field,
> FullText is a memo field; changing Incipit to a memo field doesn't
> remove the error).
>
> Should I look at using VBA?
>
> Any ideas?
> Thanks!
> Martin Lacoste[/color]

[Martin Lacoste]

Re: Data type mismatch error - why?

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message news:<dRwSc.163 8537$Ar.572665@ twister01.bloor .is.net.cable.r ogers.com>...[color=blue]
> What happens if you leave out the WHERE clause. Do each of the fields
> returned looked right?
>[/color]
Yes.

I wonder if I may have the answer/reason....

Am I correct in saying that Null [Incipit] fields will cause errors,
even if null fields are filtered out? Even though not specified
below, I set the criteria for [Incipit] as Is Not Null (there are a
few null records), but still caused the error. I would guess then
that Access still works through the comparison first and, finding null
records, is not happy. I would've thought that filtering out the null
records would avoid the problem. Not so - deleting said records does
however..

....

Just tried something else - replacing the few null fields with "" - no
error. So, Access will indicate an error if a null field is present,
even if it does not form part of the results. Interesting... - I
don't recall seeing that as a possibility in any help files..!

Anyways.... thanks for your help, Doug! It always helps to have
others help you think through it!
Martin


[color=blue]
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Martin Lacoste" <martlaco1@roge rs.com> wrote in message
> news:d497f955.0 408110608.ecaa1 dc@posting.goog le.com...[color=green]
> > Is there some issue with using too many left/right/mid/len functions
> > in queries? Depending on the usage, they work fine, but... then
> > there's here:
> >
> > SELECT Master_CAO.Inci pit, Master_CAO.Full Text, Len([Incipit]) AS
> > [Length of Incipit], Left([Master_CAO]![FullText],Len([Incipit])) AS
> > [FullText-same length]
> > FROM Master_CAO
> > WHERE (((Left([FullText],Len([Incipit])))<>[Incipit]));
> >
> > I'm trying to compare two fields, Incipit and FullText - FullText is a
> > longer version of Incipit, so I only want to compare the first part of
> > Fulltext, hence the len function. I can view this truncated text no
> > problem, but once I put in any criteria (<> in this case), I get a
> > data type mismatch error, which stymies me, since the data type
> > doesn't seem to have anything to do with it (Incipit is a text field,
> > FullText is a memo field; changing Incipit to a memo field doesn't
> > remove the error).
> >
> > Should I look at using VBA?
> >
> > Any ideas?
> > Thanks!
> > Martin Lacoste[/color][/color]

[Douglas J. Steele]

Re: Data type mismatch error - why?

"Martin Lacoste" <martlaco1@roge rs.com> wrote in message
news:d497f955.0 408120723.69626 026@posting.goo gle.com...[color=blue]
> "Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message[/color]
news:<dRwSc.163 8537$Ar.572665@ twister01.bloor .is.net.cable.r ogers.com>...[color=blue][color=green]
> > What happens if you leave out the WHERE clause. Do each of the fields
> > returned looked right?
> >[/color]
> Yes.
>
> I wonder if I may have the answer/reason....
>
> Am I correct in saying that Null [Incipit] fields will cause errors,
> even if null fields are filtered out? Even though not specified
> below, I set the criteria for [Incipit] as Is Not Null (there are a
> few null records), but still caused the error. I would guess then
> that Access still works through the comparison first and, finding null
> records, is not happy. I would've thought that filtering out the null
> records would avoid the problem. Not so - deleting said records does
> however..
>
> ...
>
> Just tried something else - replacing the few null fields with "" - no
> error. So, Access will indicate an error if a null field is present,
> even if it does not form part of the results. Interesting... - I
> don't recall seeing that as a possibility in any help files..!
>
> Anyways.... thanks for your help, Doug! It always helps to have
> others help you think through it![/color]

You could try using the Nz() function to convert the Null fields to spaces

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)