Like Operator in the Criteria in a Query

[Rob]

I have a database that will be looking at Work Requests numbers and if
certain criteria is met, it will add either an A or B or C or D to the end of
the Work Request #, What stipulates whether an A or B or C, etc. is added
depends on how many Work Requests are found that have the number, then one
that has the A, then one that has the B and so forth. I am trying to create
a query that will look at a text box that has the Work Request number in it
and counts the number of times that work request is in the table whether it
have an A, B, C, D. or none but just the number. I am jusing this in my
criteria but it doesn't find the requests with the letter extensions: "Like
[Forms]![frmchgExcOccur]![ChecklistID]". The [ChecklistID] is the Work
Request #. Then sometime this ChecklistID will be the Work Request # with
an A, or B, or C. Is there a way to find all the ChecklistID's that have the
number and or the number plus A, B, C, or D?

Thanks in Advance,

 

[Ken Snell \(MVP\)]

Perhaps this example will let you finish the query:

SELECT Count(*) As HowMany
FROM Tablename
WHERE Fieldname Like
[Forms]![frmchgExcOccur]![ChecklistID] & "*";

 

[Ken Snell \(MVP\)]

I may have read your question too quickly. You're saying that the form's
control may contain a value that ends with a letter sometimes, but not
always? May I assume that the "normal" part of the Work Request Number does
not contain letters? Assuming that this is the case:

SELECT Count(*) As HowMany
FROM Tablename
WHERE Fieldname Like
Left([Forms]![frmchgExcOccur]![ChecklistID],
Len([Forms]![frmchgExcOccur]![ChecklistID]) +
(Right([Forms]![frmchgExcOccur]![ChecklistID], 1) Like [A-Z])
& "*";

--

Ken Snell
<MS ACCESS MVP>

Perhaps this example will let you finish the query:

SELECT Count(*) As HowMany
FROM Tablename
WHERE Fieldname Like
[Forms]![frmchgExcOccur]![ChecklistID] & "*";

--

Ken Snell
<MS ACCESS MVP>

I have a database that will be looking at Work Requests numbers and if
certain criteria is met, it will add either an A or B or C or D to the
end of
the Work Request #, What stipulates whether an A or B or C, etc. is
added
depends on how many Work Requests are found that have the number, then
one
that has the A, then one that has the B and so forth. I am trying to
create
a query that will look at a text box that has the Work Request number in
it
and counts the number of times that work request is in the table whether
it
have an A, B, C, D. or none but just the number. I am jusing this in my
criteria but it doesn't find the requests with the letter extensions:
"Like
[Forms]![frmchgExcOccur]![ChecklistID]". The [ChecklistID] is the Work
Request #. Then sometime this ChecklistID will be the Work Request #
with
an A, or B, or C. Is there a way to find all the ChecklistID's that have
the
number and or the number plus A, B, C, or D?

Thanks in Advance,