Wildcard parameter query not working

[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:] & "*"));

 

[Clifford Bass]

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

 

[John Spencer]

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

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.

 

[Ann Scharpf]

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

--
Ann Scharpf

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

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:] & "*"));

 

[Clifford Bass]

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