Searching a string

[Guest]

I'm trying to use a query to a search for values that begin with certain
characters and the values with vary in the number of characters. I was using
the Like statement and that isn't doing what I need it to. When do a query
using Like "w_*", it returns anything with a w and an underscore in it; not
just values that begin with the w and underscore. I was trying to look for
something else but maybe I just overlooked it. Please help.

 

[Duane Hookom]

I find it difficult to believe that using
Like "w_*"
would return a value like
"ABC w_DEF"
In my tests, the previous didn't return the record in a query.

 

[PC Datasheet]

Like "*" & "w_" & "*" will returns anything with a w and an underscore in
it.

 

[Guest]

When I use the Like "w_*" or Like "w_" & "*", I've been getting the following
values back and I only want to focus on getting only the values that start
with w_:

w_attach
w_attachlv
w_wfcoresi840
wps_attachpi840
wps_hrspatti840
wst_provcontract_i840

Here's my whole query statement in SQL view if it helps:

SELECT IMAGING_BATCH_T.PROCESS_NM, IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_BATCH_T.SCAN_DT, Count(IMAGING_FOLDER_T.ICN) AS CountOfICN
FROM IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON IMAGING_BATCH_T.BATCH_NM
= IMAGING_FOLDER_T.BATCH_NM
WHERE (((IMAGING_BATCH_T.CLM_ATTACH_IND)="A") AND
((IMAGING_FOLDER_T.UPLOAD_FLAG)="Y" Or (IMAGING_FOLDER_T.UPLOAD_FLAG)="N"))
GROUP BY IMAGING_BATCH_T.PROCESS_NM, IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_BATCH_T.SCAN_DT
HAVING (((IMAGING_BATCH_T.PROCESS_NM) Like "w_*") AND
((IMAGING_BATCH_T.SCAN_DT)=[Enter the scan date (mm/dd/yy):]));

 

[Guest]

But I'm not starting with an asterik in the Like statement. Isn't there a
way to search for values that specifically start with a "w_" in it??

 

[Guest]

Forget it. I got it. I set the criteria to
=IIf(Left([PROCESS_NM],2)="w_",[PROCESS_NM]) instead of using the Like
statement. I was in a hurry before and now that I had time to sit down with
it, I figured it out.

 

[Guest]

Forget it. I got it. I set the criteria to
=IIf(Left([PROCESS_NM],2)="w_",[PROCESS_NM]) instead of using the Like
statement. I was in a hurry before and now that I had time to sit down with
it, I figured it out.

 

[Duane Hookom]

I'm not sure why you would have the "Like..." in the HAVING... section. I
would use something like:

SELECT IMAGING_BATCH_T.PROCESS_NM,
IMAGING_BATCH_T.BUCKET_NBR, IMAGING_BATCH_T.SCAN_DT,
Count(IMAGING_FOLDER_T.ICN) AS CountOfICN

FROM IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T
ON IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM

WHERE IMAGING_BATCH_T.CLM_ATTACH_IND="A" AND
IMAGING_FOLDER_T.UPLOAD_FLAG IN ("Y","N")
AND IMAGING_BATCH_T.PROCESS_NM Like "w_*" AND
IMAGING_BATCH_T.SCAN_DT=[Enter the scan date (mm/dd/yy):]

GROUP BY IMAGING_BATCH_T.PROCESS_NM, IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_BATCH_T.SCAN_DT;

Also, SQL Server uses the underscore as a wildcard to match a single
character.
--
Duane Hookom
MS Access MVP
--

When I use the Like "w_*" or Like "w_" & "*", I've been getting the
following
values back and I only want to focus on getting only the values that start
with w_:

w_attach
w_attachlv
w_wfcoresi840
wps_attachpi840
wps_hrspatti840
wst_provcontract_i840

Here's my whole query statement in SQL view if it helps:

SELECT IMAGING_BATCH_T.PROCESS_NM, IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_BATCH_T.SCAN_DT, Count(IMAGING_FOLDER_T.ICN) AS CountOfICN
FROM IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM
= IMAGING_FOLDER_T.BATCH_NM
WHERE (((IMAGING_BATCH_T.CLM_ATTACH_IND)="A") AND
((IMAGING_FOLDER_T.UPLOAD_FLAG)="Y" Or
(IMAGING_FOLDER_T.UPLOAD_FLAG)="N"))
GROUP BY IMAGING_BATCH_T.PROCESS_NM, IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_BATCH_T.SCAN_DT
HAVING (((IMAGING_BATCH_T.PROCESS_NM) Like "w_*") AND
((IMAGING_BATCH_T.SCAN_DT)=[Enter the scan date (mm/dd/yy):]));

I find it difficult to believe that using
Like "w_*"
would return a value like
"ABC w_DEF"
In my tests, the previous didn't return the record in a query.