Wildcard parameter query not working

  • Thread starter Thread starter Ann Scharpf
  • Start date Start date
A

Ann Scharpf

I'm running Access 2003. I've looked at the posts here and thought I had
done the wildcard correctly but I am getting no results for data that I know
does exist. If I remove the wildcard and paste an exact string that exists
in the queried field, I do get results. If I add the wildcard and paste the
exact same string, I get a blank results table. Nor do I get any results if
I enter say only one word that is in the string.

Here is the SQL of my query. Can anyone see what I have done wrong?

Thanks in advance for your help!

SELECT [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML, [ABOMTR
XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
FROM [ABOMTR XML-phase2] LEFT JOIN [ABOMTR XML-phase1] ON [ABOMTR
XML-phase2].MainXHTML = [ABOMTR XML-phase1].MainXHTML
GROUP BY [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML,
[ABOMTR XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
HAVING ((([ABOMTR XML-phase2].ExtXHTML)="*" & [Enter code for which you want
to find linked programs:] & "*"));
 
Hi Ann,

For wild cards you need to use the Like operator. And I do not see why
you are using a summary query are you are not producing any sums, counts,
maximums or other summary types of data. So change it to a regular select.

SELECT [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML, [ABOMTR
XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
FROM [ABOMTR XML-phase2] LEFT JOIN [ABOMTR XML-phase1] ON [ABOMTR
XML-phase2].MainXHTML = [ABOMTR XML-phase1].MainXHTML
WHERE ((([ABOMTR XML-phase2].ExtXHTML) Like "*" & [Enter code for which you
want to find linked programs:] & "*"));

Incidentally, even if you need the summary query, you should use the
where clause instead of the having clause. The having clause is usually used
when comparing the results of the summarizations.

SELECT [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML, [ABOMTR
XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
FROM [ABOMTR XML-phase2] LEFT JOIN [ABOMTR XML-phase1] ON [ABOMTR
XML-phase2].MainXHTML = [ABOMTR XML-phase1].MainXHTML
GROUP BY [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML,
[ABOMTR XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
WHERE ((([ABOMTR XML-phase2].ExtXHTML) Like "*" & [Enter code for which you
want to find linked programs:] & "*"));

Hope that helps,

Clifford Bass
 
Replace the = sign with the LIKE operator.

Also for efficiency sake you might consider moving the criteria into a where
clause.

SELECT [ABOMTR XML-phase2].ExtXHTML
, [ABOMTR XML-phase2].MainXHTML
, [ABOMTR XML-phase1].XMLCode
, [ABOMTR XML-phase1].ProgramName
FROM [ABOMTR XML-phase2] LEFT JOIN [ABOMTR XML-phase1] ON [ABOMTR
XML-phase2].MainXHTML = [ABOMTR XML-phase1].MainXHTML

WHERE [ABOMTR XML-phase2].ExtXHTML LIKE
"*" & [Enter code for which you want to find linked programs:] & "*"

GROUP BY [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML,
[ABOMTR XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks so much for your help. This works perfectly now.

The Access classes I've taken didn't get into this level of query
functionality and I'm always trying to figure things out with the help or
this newsgroup.

As for the summary stuff (I guess you mean why I had the group by), I was
just trying to eliminate any duplicates there might be. I discovered that
there was no difference in the results when I eliminated the group by
entirely, so now it is gone.

Anyway, thanks again. I really appreciate your help.
 
Thanks for your help! The "LIKE" was exactly what I needed. (But you knew
that...) I removed the grouping section.

--
Ann Scharpf


John Spencer said:
Replace the = sign with the LIKE operator.

Also for efficiency sake you might consider moving the criteria into a where
clause.

SELECT [ABOMTR XML-phase2].ExtXHTML
, [ABOMTR XML-phase2].MainXHTML
, [ABOMTR XML-phase1].XMLCode
, [ABOMTR XML-phase1].ProgramName
FROM [ABOMTR XML-phase2] LEFT JOIN [ABOMTR XML-phase1] ON [ABOMTR
XML-phase2].MainXHTML = [ABOMTR XML-phase1].MainXHTML

WHERE [ABOMTR XML-phase2].ExtXHTML LIKE
"*" & [Enter code for which you want to find linked programs:] & "*"

GROUP BY [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML,
[ABOMTR XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ann said:
I'm running Access 2003. I've looked at the posts here and thought I had
done the wildcard correctly but I am getting no results for data that I know
does exist. If I remove the wildcard and paste an exact string that exists
in the queried field, I do get results. If I add the wildcard and paste the
exact same string, I get a blank results table. Nor do I get any results if
I enter say only one word that is in the string.

Here is the SQL of my query. Can anyone see what I have done wrong?

Thanks in advance for your help!

SELECT [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML, [ABOMTR
XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
FROM [ABOMTR XML-phase2] LEFT JOIN [ABOMTR XML-phase1] ON [ABOMTR
XML-phase2].MainXHTML = [ABOMTR XML-phase1].MainXHTML
GROUP BY [ABOMTR XML-phase2].ExtXHTML, [ABOMTR XML-phase2].MainXHTML,
[ABOMTR XML-phase1].XMLCode, [ABOMTR XML-phase1].ProgramName
HAVING ((([ABOMTR XML-phase2].ExtXHTML)="*" & [Enter code for which you want
to find linked programs:] & "*"));
Click to expand...
Click to expand...
 
Hi Ann,

Good to hear it is working perfectly now. You are quite welcome.

As to eliminating duplicates, when you need to do that, use the
"distinct" clause of the select statement:

select distinct column1, column2
from table1;

To specify this setting, right-click anywhere in query window while in
design mode and choose Properties. Click anywhere in blank part of the top
portion of the query design window. Change the Unique Values property to Yes.

Clifford Bass
 
Back
Top