Weird "Data type mismatch in criteria expression" 1

[cocobingo]

Hi All!
I have a strange beahaviour here, and I can't find the cause of it.

First of all, don't pay attention to the ";" after "

http://www.",

it is added by the froum and does not appear in my code.


I have a table (ket's say it has 2 fields, to simplify), with a website url (field : [website] text 255) and an email address (field : text 255).

I want to compare the part of the url that is after the "[URL unfurl="true"]www."[/URL] with the part of the email that is after the "@". It will not always work of course, but that is not the point. Here is my query :

SELECT Mid(website,InStr(website,"[URL unfurl="true"]www.&quot+4)[/URL] AS truncated_website, Mid(email,InStr(email,"@&quot+1) AS truncated_email
FROM theTable
WHERE (theTable.website Is Not Null) AND (theTable.email Is Not Null) AND (Mid(website,InStr(website,&quot;[URL unfurl="true"]www.&quot+4)[/URL] <> Mid(email,InStr(email,&quot;@&quot+1))

So I get a 'Data type mismatch in criteria expression when I try to execute this query.

Here is what I tried :
1/
SELECT Mid(website,InStr(website,&quot;[URL unfurl="true"]www.&quot+4)[/URL] AS truncated_website, Mid(email,InStr(email,&quot;@&quot+1) AS truncated_email
FROM theTable
WHERE (theTable.website Is Not Null) AND (theTable.email Is Not Null)
That gives me a result with not empty field, and it works fine.

2/
SELECT Mid(website,InStr(website,&quot;[URL unfurl="true"]www.&quot+4)[/URL] AS truncated_website, Mid(email,InStr(email,&quot;@&quot+1) AS truncated_email, Vartype(Mid(website,InStr(website,&quot;[URL unfurl="true"]www.&quot+4))[/URL] As TypeOfUrl, Vartype(Mid(email,InStr(email,&quot;@&quot+1)) As TypeOfEmail
FROM theTable
WHERE (theTable.website Is Not Null) AND (theTable.email Is Not Null)
That gives me a table with no empty field, and both TypeOfUrl and TypeOfEmail are always 8 (that means that truncated_website and truncated_email are always string values).

3/ Replacing &quot;<>&quot; with &quot;not like&quot; or with a StrComp(...)<>0 does not help, always a &quot;Data type mismatch&quot;

Now it gets really weird :
4/ SELECT Mid(website,InStr(website,&quot;[URL unfurl="true"]www.&quot+4)[/URL] AS truncated_website
FROM theTable
WHERE (theTable.website Is Not Null) AND Vartype(Mid(website,InStr(website,&quot;[URL unfurl="true"]www.&quot+4))[/URL] <> 8
Gives me a &quot;Data Type Mismath&quot; !!!

So maybe some website don't match &quot;[URL unfurl="true"]www.&quot;[/URL] :
5/ DELETE * FROM theTable where website not like &quot;*[URL unfurl="true"]www.*&quot;[/URL]
10 lines deleted, but still the same problem...

Now I am out of idea !!!
Any help would be appreciated !
I forgot to mention tha I definitively could this around with some code or by using some temporary table, but want i really want is to understand what is happen,ing!


Thansk a lot !

 

[nicsin]

AFAIK

Instr

expects the starting position as well so

InStr(website,"www.")

should be

InStr(1,website,"www.")

. Is it any good?

Why did you use

InStr(website,"www.";)+4

instead of

InStr(website,"www.";)+1

?

 

[cocobingo]

Hi nicsin, thanks for answering!

Well, the start value in instr is facultative and it defaults to 1 if omitted, but I check this right now and ... It Works !!! Thanks!
For your 2nd question, I used +4 because Instr returns the position of the first char, so here it returns the positions of the first &quot;w&quot; in &quot;

http://www.&quot;,

so I need +4 to start cutting the string after the &quot;.&quot;.

Thanks a lot, it learned that I should never use default values in these functions!!!

 

[cocobingo]

Oops, I was to quick in answering... it still doesn't work !
:-(

 

[nicsin]

Have you corrected all instances of

instr

in your query?

 

[cocobingo]

Yes, here is an example of the a not working query :

SELECT Mid(website,InStr(1,website,&quot;

http://www.&quot;)+4),

Mid(email,InStr(1,email,&quot;@&quot+1)
FROM theTable
WHERE (website Is Not Null) AND (email Is Not Null) AND (InStr(1,website,&quot;

http://www.&quot;)>0)

AND (InStr(1,email,&quot;@&quot>0) AND
VarType(Mid(website,InStr(1,website,&quot;

http://www.&quot;)+4))<>

VarType(Mid(email,InStr(1,email,&quot;@&quot+1))

 

[nicsin]

I don't think you will have any chance in using complex functions as

Mid(website,InStr(1,website,"www.";)+4)

in the

where

part of the query!

Unless you think of something else I have a wild suggestion. Break the two fields in two parts like website1 - website2 and email1 - email2. in the first fields store '

http://www.'

or whatever for website and everything before the '@' character for the email. In the second fields store everything after.

example:

website email

http://www.anAddress.com

myName@anAddress.com

would become

website1 website2 email1 email2
www. anAddress.com myName@ anAddress.com

This way you could just compare the second fields... But I also assume that you have the capability of changing your database structure as well as your front end! Scary...

 

[cocobingo]

Well, I could do that, but this query is only meant for verification. So it would be too much pain for the result...
In fact, I think that Access cannot tell what is the datatype of my function, so it cannot compare the fields. Even if I force my fields to be strings with CStr(Mid...), it doesn't work. Instead of considering that these fields are of Variant type, Access might consider them as Unknown or Undefined data type (or something similar), and cannot make any comparison on them...
That's a pain!

 

[GoDawgs]

The error occurs when it tries to evaluate the Instr function on the null fields (even though you are filtering out the null fields in the query, it still tries to evaluate the function first). I think you can just change your Mid functions to this:


Mid(website,InStr(1,nz([website]),&quot;

http://www.&quot;)+4)

and
Mid(email,InStr(1,nz(),&quot;@&quot+1)

Hope that helps.

Kevin

 

[cocobingo]

I had tried some queries with Nz(), but not inside the InStr! I'll do it right now, thanks for helping!

 

[cocobingo]

I works great, thank you both for helping!

Bingo