wildcard syntax question

  • Thread starter Thread starter middletree
  • Start date Start date
M

middletree

I had a query like this:

select P.PersonalID,P.FName,P.LName,P.PreferredName,PG.GiftID,PA.AreaID,
PAB.AbilityID, PP.PeopleID
FROM Personal P
INNER JOIN PersonalGift PG ON P.PersonalID = PG.PersonalID
INNER JOIN PersonalArea PA ON P.PersonalID = PA.PersonalID
INNER JOIN PersonalAbility PAB ON P.PersonalID = PAB.PersonalID
INNER JOIN PersonalPeople PP ON P.PersonalID = PP.PersonalID
WHERE PG.GiftID LIKE *
AND PA.AreaID LIKE *
AND PAB.AbilityID LIKE *
AND PP.PeopleID LIKE *
(where the * could conveivably be concatenated with integers. Ex: "AND
PP.PeopleID LIKE 16*"

and it gave me an error about a missing operator. After some experimenting,
I found that if I just dropped the asterisk, and had an integer there, like
"AND PP.PeopleID LIKE 16" it would work fine. But I was really hoping
that there would be a way to allow for the fact that for the WHERE and the
AND lines you see above, there may be nothing or there may be one integer
for each one.

So is there something I am missing in the syntax? (BTW, I got this idea
from an MVP who responded to a post a few days ago.)

This is for a classic ASP app, and I have found a way to code around it by
requiring people to select an item from the Gift dropdown (where you see the
word WHERE in the query above), and leaving the other things optional. But
I'd like to have all 4 of them optional.
 
Wildcards only work with Text values, not numbers.

Assuming you're building your SQL in code, simply leave out the criteria
when you don't have a value to compare to.
 
Comments inline:

Douglas J. Steele said:
Wildcards only work with Text values, not numbers.
Click to expand...

Yes, I figured that out after I posted this
Assuming you're building your SQL in code, simply leave out the criteria
when you don't have a value to compare to.
Click to expand...

Unfortunately, I can't figure out a way to do that, because of the need for
a WHERE clause.

If I have 4 dropdowns, numbered A,B,C, and D, I woudl have this:
(psuedocode)

SELECT Fname FROM Personal
If dropdownA <> "" then
WHERE GiftID = strGiftID
End if
If dropdownB <> "" then
AND AreaID = strAreaID
End if
If dropdownC <> "" then
AND AbilityID = strAbilityID
End if
If dropdownD <> "" then
AND PeopleID = strPeopleID
End if

See the problem? If nobody selects from dropdown A, I don't have the word
WHERE in my query. But I can't put the word WHERE in one of the others,
because of somebody selects something from both dropdown A and another one,
then I have the word WHERE twice.

I think my solution is going to be to require that the first dropdown gets
selected, with the others being optional
 
I had a query like this:

select P.PersonalID,P.FName,P.LName,P.PreferredName,PG.GiftID,PA.AreaID,
PAB.AbilityID, PP.PeopleID
FROM Personal P
INNER JOIN PersonalGift PG ON P.PersonalID = PG.PersonalID
INNER JOIN PersonalArea PA ON P.PersonalID = PA.PersonalID
INNER JOIN PersonalAbility PAB ON P.PersonalID = PAB.PersonalID
INNER JOIN PersonalPeople PP ON P.PersonalID = PP.PersonalID
WHERE PG.GiftID LIKE *
AND PA.AreaID LIKE *
AND PAB.AbilityID LIKE *
AND PP.PeopleID LIKE *
(where the * could conveivably be concatenated with integers. Ex: "AND
PP.PeopleID LIKE 16*"
Click to expand...

I think you need quotemarks:

LIKE "*"

or

LIKE "16*"
 
See the problem? If nobody selects from dropdown A, I don't have the word
WHERE in my query. But I can't put the word WHERE in one of the others,
because of somebody selects something from both dropdown A and another one,
then I have the word WHERE twice.
Click to expand...

One thing you can do is to start the SQL WHERE clause with

WHERE TRUE

This generic WHERE clause will retrieve all records. You can then use
your code to insert other limiting criteria, e.g.

"WHERE TRUE AND AND AreaID = " & strAreaID

and so on.
 
middletree said:
Unfortunately, I can't figure out a way to do that, because of the need for
a WHERE clause.

If I have 4 dropdowns, numbered A,B,C, and D, I woudl have this:
(psuedocode)

SELECT Fname FROM Personal
If dropdownA <> "" then
WHERE GiftID = strGiftID
End if
If dropdownB <> "" then
AND AreaID = strAreaID
End if
If dropdownC <> "" then
AND AbilityID = strAbilityID
End if
If dropdownD <> "" then
AND PeopleID = strPeopleID
End if
Click to expand...

You're assigning the SQL to a variable, right?

strSQL = "SELECT Fname FROM Personal "

If dropdownA <> "" Then
strWhere = "GiftID = strGiftID AND "
End if
If dropdownB <> "" Then
strWhere = strWhere & "AreaID = strAreaID AND "
End if
If dropdownC <> "" Then
strWhere = strWhere & "AbilityID = strAbilityID AND "
End if
If dropdownD <> "" Then
strWhere = strWhere & "PeopleID = strPeopleID AND "
End if

If Len(strWhere) > 0) Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If
 
Interesting method. I'll try it out, along with the other suggestion. See
what works best

Thanks!
 
even for an int?
Click to expand...

Sorry... I see elsethread that the field is numeric. LIKE doesn't work
for numerics (at least not the way you want; LIKE 1* will retrieve
156, 11, and any other number starting with 1).
 
Back
Top