wildcard syntax question

[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.

 

[Douglas J. Steele]

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.

 

[middletree]

Comments inline:

Wildcards only work with Text values, not numbers.

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.

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

 

[John Vinson]

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*"

I think you need quotemarks:

LIKE "*"

or

LIKE "16*"

 

[middletree]

even for an int?

 

[John Vinson]

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.

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.

 

[Douglas J. Steele]

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

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

 

[middletree]

Hmm, was not aware of this; sounds like it could work, thanks.

 

[middletree]

Interesting method. I'll try it out, along with the other suggestion. See
what works best

Thanks!

 

[John Vinson]

even for an int?

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).