Limit results to StrComp that produce errors

[Ann Scharpf]

I am trying to create a case sensitive query that identifies when users have
made typos when entering the word TO. I read postings here and have gotten
the StrComp to get me most of the way I need to go. My query is giving
correct 1 and 0 results depending on whether there is a typo in the line.

I am having trouble getting to the next step of LIMITING my results to lines
that have the error condition. My query is producing a 1065 line result.
Only 5 of these lines have a TO typo.

Here is my SQL:

SELECT tbl.Fld, InStr(1,[fld],"-To-") AS ToPos, StrComp(Mid([Fld],InStr([Fld],
"to"),2),"TO",0) AS ToError
FROM tbl
GROUP BY tbl.Fld, InStr(1,[fld],"-To-"), StrComp(Mid([Fld],InStr([Fld],"to"),
2),"TO",0)
HAVING (((tbl.Fld) Like "*to*"));

I tried to change the ToError so that it had a "where >0" condition, so that
I'd only see the rows that have a 1, indicating the error. When I try the
"where", I get error "Data type mismatch in criteria expression."

Is there any way to limit my results so I only see the error rows?

Thanks for your help.

Ann

 

[John Spencer]

I would try the following.

SELECT tbl.Fld,
InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) AS ToError
FROM tbl
WHERE tbl.Fld Like "*to*" AND
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) <> 0

Note that I dropped the group by. You may want to include Distinct in the
Select clause. That will make your query not updateable.

SELECT DISTINCT tbl.Fld,
InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) AS ToError
FROM tbl
WHERE tbl.Fld Like "*to*" AND
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) <> 0

 

[Ann Scharpf]

John:

Thanks for taking the time to reply. I tried pasting your first SQL into a
new query in my database and I got the same error ... "Data type mismatch in
criteria expression."

Ann

 

[John Spencer]

My bad, I didn't fully read your posting

First question: Is the value you are checking '"to" or "-To-" or " to " or
some other variant?


SELECT tbl.Fld,
InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) AS ToError
FROM tbl
WHERE tbl.Fld Like "*to*" AND
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) <> 0

The Mid function is generating an error when fld does not have To in it. It
is trying to return Mid(Your field,0) which is a problem.

I might actually try the following

SELECT tbl.Fld
FROM tbl
WHERE tbl.Fld like "*" & [Find This] & "*" and Instr(1,tbl.fld,[Find
This],0) = 0

When you get prompted for Find This, you can enter whichever value you are
looking for. So if you are looking for cases where the value should be -TO-
but isn't all you need to enter is -TO- . If you lower case is the valid
entry then enter -to- . If mixed case is valid then enter -To-.

I would try the following.

SELECT tbl.Fld,
InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) AS ToError
FROM tbl
WHERE tbl.Fld Like "*to*" AND
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) <> 0

Note that I dropped the group by. You may want to include Distinct in the
Select clause. That will make your query not updateable.

SELECT DISTINCT tbl.Fld,
InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) AS ToError
FROM tbl
WHERE tbl.Fld Like "*to*" AND
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) <> 0

I am trying to create a case sensitive query that identifies when users
have
made typos when entering the word TO. I read postings here and have
gotten
the StrComp to get me most of the way I need to go. My query is giving
correct 1 and 0 results depending on whether there is a typo in the line.

I am having trouble getting to the next step of LIMITING my results to
lines
that have the error condition. My query is producing a 1065 line result.
Only 5 of these lines have a TO typo.

Here is my SQL:

SELECT tbl.Fld, InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],
"to"),2),"TO",0) AS ToError
FROM tbl
GROUP BY tbl.Fld, InStr(1,[fld],"-To-"),
StrComp(Mid([Fld],InStr([Fld],"to"),
2),"TO",0)
HAVING (((tbl.Fld) Like "*to*"));

I tried to change the ToError so that it had a "where >0" condition, so
that
I'd only see the rows that have a 1, indicating the error. When I try
the
"where", I get error "Data type mismatch in criteria expression."

Is there any way to limit my results so I only see the error rows?

Thanks for your help.

Ann

 

[Ann Scharpf]

The text that is VALID is TO. (Never changes.) I have been tasked to
identify any lines where the user has inadvertently entered To, tO, or to.
Really, the valid string is xx-TO-xx (xx's are any text) but I have also been
tasked to identify places where the user has entered spaces by the dashes, so
I realized I could not use -TO- as my search string because, if the user made
a space dash mistake, I still need to test for the uppercase condition.

I am trying to avoid the necessity for any user input. I have created a
switchboard that allows the user to clear out old data, import the new data
and run each error report by just clicking on the switchboard buttons in
sequence.

I'm confused about your comment about the MID function. When I run the query
WITHOUT trying to limit the results to error lines, the MID function works
just fine. My original table is over 14,000 lines and I am getting results
of 205 lines that show some form of TO in them. The ToError field correctly
shows a 1 when there is an error and a 0 for lines that have the correct case.
The problem only occurs when I try to change the group by to a where.

I will try your SQL suggestion and see if I can figure out how to strip out
the prompting for the search value.

Thanks.

Ann

My bad, I didn't fully read your posting

First question: Is the value you are checking '"to" or "-To-" or " to " or
some other variant?

SELECT tbl.Fld,
InStr(1,[fld],"-To-") AS ToPos,
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) AS ToError
FROM tbl
WHERE tbl.Fld Like "*to*" AND
StrComp(Mid([Fld],InStr([Fld],"to"),2),"TO",0) <> 0

The Mid function is generating an error when fld does not have To in it. It
is trying to return Mid(Your field,0) which is a problem.

I might actually try the following

SELECT tbl.Fld
FROM tbl
WHERE tbl.Fld like "*" & [Find This] & "*" and Instr(1,tbl.fld,[Find
This],0) = 0

When you get prompted for Find This, you can enter whichever value you are
looking for. So if you are looking for cases where the value should be -TO-
but isn't all you need to enter is -TO- . If you lower case is the valid
entry then enter -to- . If mixed case is valid then enter -To-.

I would try the following.

[quoted text clipped - 49 lines]

 

[Ann Scharpf]

Thank you, John! The InStr function did the trick. Now I have to read up on
this function.

Here's the final SQL. It does EXACTLY what I need.

SELECT tbl.Fld
FROM tbl
WHERE (((tbl.Fld) Like "*TO*" And (tbl.Fld) Like "*-*") AND ((InStr(1,[tbl].
[fld],"TO",0))=0));

Thanks so much for your help.

Ann

 

[John Spencer]

I'm glad it worked, but depending on the data in the field you could still
have some problems. But if you are just using this to find records that may
have problems, then you are fine. Once the records are identified it is
obviously time to apply the human brain to check them out.

For instance, if the field had
Tomato Palace - AgentA
then the record would be returned since it meets the criteria

Thank you, John! The InStr function did the trick. Now I have to read up
on
this function.

Here's the final SQL. It does EXACTLY what I need.

SELECT tbl.Fld
FROM tbl
WHERE (((tbl.Fld) Like "*TO*" And (tbl.Fld) Like "*-*") AND
((InStr(1,[tbl].
[fld],"TO",0))=0));

Thanks so much for your help.

Ann

I might actually try the following

SELECT tbl.Fld
FROM tbl
WHERE tbl.Fld like "*" & [Find This] & "*" and Instr(1,tbl.fld,[Find
This],0) = 0

When you get prompted for Find This, you can enter whichever value you are
looking for. So if you are looking for cases where the value should
be -TO-
but isn't all you need to enter is -TO- . If you lower case is the valid
entry then enter -to- . If mixed case is valid then enter -To-.