Data type mismatch

  • Thread starter Thread starter Me
  • Start date Start date
M

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

KARL DEWEY said:
Where are you using this? In an update query?
Post complete SQL.

Me said:
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
Click to expand...
Click to expand...
 
(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?

Me said:
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

KARL DEWEY said:
Where are you using this? In an update query?
Post complete SQL.

Me said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
(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?

Me said:
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

KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Me said:
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

KARL DEWEY said:
(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?

Me said:
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
Click to expand...
Click to expand...
Click to expand...
 
Me said:
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.
Click to expand...

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
 
Back
Top