By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

Data type mismatch error - why?

P: n/a
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.Incipit, Master_CAO.FullText, 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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <ma*******@rogers.com> wrote in message
news:d4*************************@posting.google.co m...
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.Incipit, Master_CAO.FullText, 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

Nov 13 '05 #2

P: n/a
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<dR*********************@twister01.bloor.is.n et.cable.rogers.com>...
What happens if you leave out the WHERE clause. Do each of the fields
returned looked right?
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
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4*************************@posting.google.co m...
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.Incipit, Master_CAO.FullText, 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

Nov 13 '05 #3

P: n/a
"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message

news:<dR*********************@twister01.bloor.is.n et.cable.rogers.com>...
What happens if you leave out the WHERE clause. Do each of the fields
returned looked right?

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!


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!)

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.