Data type mismatch

[Me]

I am getting 'Data Type mismatch' error for the following -

Left(R.[text],Instr(R.[text],"/")-1) = I.[text]

the data type for [text] field on both tables is Text size 255.

Thank you,
-Me

 

[KARL DEWEY]

Where are you using this? In an update query?
Post complete SQL.

 

[Me]

No as part of query here is the where clause -

select R.*, I.* from table1 as R, table2 as I where
(Left(R.[text],Instr(R.[text],"/")-1)=I.[text] Or
Left(R.[text],Instr(R.[text],"doc")+2)=I.[text]) And ((R.Id=I.Id) Or
(R.Id=I.Id2));

Thank you for your help,
-Me

Where are you using this? In an update query?
Post complete SQL.

I am getting 'Data Type mismatch' error for the following -

Left(R.[text],Instr(R.[text],"/")-1) = I.[text]

the data type for [text] field on both tables is Text size 255.

Thank you,
-Me

 

[KARL DEWEY]

(R.[text],Instr(R.[text],"doc")+2)=I.[text]) will compare 'doc' to I.[Text]
or
'xyzwwwwwwdoc' to I.[Text] as it will pull all to the left
of 'doc' plus 2 characters beyond thefirst letter of 'doc'.

Is it possible the mismatch is in the ID's?

No as part of query here is the where clause -

select R.*, I.* from table1 as R, table2 as I where
(Left(R.[text],Instr(R.[text],"/")-1)=I.[text] Or
Left(R.[text],Instr(R.[text],"doc")+2)=I.[text]) And ((R.Id=I.Id) Or
(R.Id=I.Id2));

Thank you for your help,
-Me

Where are you using this? In an update query?
Post complete SQL.

I am getting 'Data Type mismatch' error for the following -

Left(R.[text],Instr(R.[text],"/")-1) = I.[text]

the data type for [text] field on both tables is Text size 255.

Thank you,
-Me

 

[Me]

Karl,

No, IDs are ok, when I remove the 'text' portion of the query and run with
only
IDs, it works fine.

I was trying other things, hence the delay in getting back.

Anything else you would like me to try?

Thank you,
-Me

(R.[text],Instr(R.[text],"doc")+2)=I.[text]) will compare 'doc' to I.[Text]
or
'xyzwwwwwwdoc' to I.[Text] as it will pull all to the left
of 'doc' plus 2 characters beyond thefirst letter of 'doc'.

Is it possible the mismatch is in the ID's?

No as part of query here is the where clause -

select R.*, I.* from table1 as R, table2 as I where
(Left(R.[text],Instr(R.[text],"/")-1)=I.[text] Or
Left(R.[text],Instr(R.[text],"doc")+2)=I.[text]) And ((R.Id=I.Id) Or
(R.Id=I.Id2));

Thank you for your help,
-Me

Where are you using this? In an update query?
Post complete SQL.

:

I am getting 'Data Type mismatch' error for the following -

Left(R.[text],Instr(R.[text],"/")-1) = I.[text]

the data type for [text] field on both tables is Text size 255.

Thank you,
-Me

 

[KARL DEWEY]

I have seen Access declare a criteria mismatch before where there are not.
I wind up doing a work around such as criteria in a preceding query or
following query.

Karl,

No, IDs are ok, when I remove the 'text' portion of the query and run with
only
IDs, it works fine.

I was trying other things, hence the delay in getting back.

Anything else you would like me to try?

Thank you,
-Me

(R.[text],Instr(R.[text],"doc")+2)=I.[text]) will compare 'doc' to I.[Text]
or
'xyzwwwwwwdoc' to I.[Text] as it will pull all to the left
of 'doc' plus 2 characters beyond thefirst letter of 'doc'.

Is it possible the mismatch is in the ID's?

No as part of query here is the where clause -

select R.*, I.* from table1 as R, table2 as I where
(Left(R.[text],Instr(R.[text],"/")-1)=I.[text] Or
Left(R.[text],Instr(R.[text],"doc")+2)=I.[text]) And ((R.Id=I.Id) Or
(R.Id=I.Id2));

Thank you for your help,
-Me

:

Where are you using this? In an update query?
Post complete SQL.

:

I am getting 'Data Type mismatch' error for the following -

Left(R.[text],Instr(R.[text],"/")-1) = I.[text]

the data type for [text] field on both tables is Text size 255.

Thank you,
-Me

 

[Bob Barrows]

I am getting 'Data Type mismatch' error for the following -

Left(R.[text],Instr(R.[text],"/")-1) = I.[text]

the data type for [text] field on both tables is Text size 255.

It might be the presence of Nulls in your data. Try using the Nz function to
eliminate Nulls, For example:
Nz(R.[text],"")

PS. "Text" is a horrible choice for a field name. As the name of a Jet
datatype, it is a reserved keyword, the use of which is going to cause
problems for you some time down the road. I strongly recommend that you
change the name of that field. A good way to avoid reserved keywords is to
make your field names more descriptive: what type of text is stored in that
field? CommentText? FurtherDescription?
You can find a list of reserved keywords that should be avoided here:
http://www.aspfaq.com/show.asp?id=2080