Wild Card Search

D

dadrew

Registered User.
Local time
Today, 16:17
Joined
Sep 15, 2003
Messages
186
I know that this piece of code works in my search engine. And I know that in order to make it a Wild Card Search must put in *'s at some point(s). Can somebody tell me where I must put them, please!

If Not IsNull(Me.Artist) Then
sCriteria = sCriteria & " AND [Artist] = " & """" & Me.Artist & """"
End If
 
Code: 
If Not IsNull(Me.Artist) Then 
    sCriteria = sCriteria & " AND [Artist] = ""*" & Me.Artist & "*""" 
End If
 
Tried this but it keeps auto inserting a space and didnt work, any ideas!

If Not IsNull(Me.Artist) Then
sCriteria = sCriteria & " AND [Artist] = " & "" * " & Me.Artist & " * """"
End If
 
What I've given you does work.
 
Wild Card

Just noticed my error I left in one of the &'s that you removed, my appologies, no offence ment!!
 
Wild Cards

OK, I copied the code in exactly as you said, (I copied and pasted it). In my search engine, in the Artist Field I typed *r* and got no hits. I know there are Artist in the database with r in the name, so clearly im doing something wrong or I have to change smething somewhere else in my form!
 
Before your query is run, put a MsgBox command in:

MsgBox sCriteria


Look for the irregularities.
 
Wild Cards

OK, Im being a bit of a biff here, this is my code so where do insert the message box criteria?

Private Sub cmdSearch_Click()
On Error Resume Next


Dim sCriteria As String
sCriteria = "WHERE ID Is Not Null"

'CDData SearchQry

If Not IsNull(Me.CDNo) Then
sCriteria = sCriteria & " AND [CDNo] = " & """" & Me.CDNo & """"
End If

If Not IsNull(Me.TrackNo) Then
sCriteria = sCriteria & " AND [TrackNo] = " & """" & Me.TrackNo & """"
End If

If Not IsNull(Me.Album) Then
sCriteria = sCriteria & " AND [Album] = " & """" & Me.Album & """"
End If

If Not IsNull(Me.Style) Then
sCriteria = sCriteria & " AND [Style] = " & """" & Me.Style & """"
End If

If Not IsNull(Me.Artist) Then
sCriteria = sCriteria & " AND [Artist] = " & """" & Me.Artist & """"
End If

If Not IsNull(Me.SongTitle) Then
sCriteria = sCriteria & " AND [SongTitle] = " & """" & Me.SongTitle & """"
End If

If Not IsNull(Me.Year) Then
sCriteria = sCriteria & " AND [Year] = " & """" & Me.Year & """"
End If

If Not IsNull(Me.Quality) Then
sCriteria = sCriteria & " AND [Quality] = " & """" & Me.Quality & """"
End If

sSql = "SELECT [ID], [CDNo], [TrackNo], [Album], [Style], [Artist], [SongTitle], [Quality], [Year] FROM CDData " & sCriteria & ";"
'MsgBox sSql 'Used for debugging purposes only
Forms![Search]![SearchResults].Form.RecordSource = sSql
Forms![Search]![SearchResults].Form.Requery

End Sub
 
You have not dimensioned the string sSQL.

Comment out the On Error Resume Next to see which line the code falters at.

You do have the MsgBox statement in the correct position.
 
Wild Card

Sorry this is going over my head now, my VB is poor to say the least and I received considerable help with this coding from this Forum, could you explain. Sorry if Im being a pain
 
Hold for 5 and I'll give you a whole new method to simplify matters.
 
Sorry, you must use Like with a wildcard.

Code: 
If Not IsNull(Me.Artist) Then 
    sCriteria = sCriteria & " AND [Artist] Like ""*" & Me.Artist & "*""" 
End If


I feel stupid for not spotting it earlier.
 
Wild Cards

Ok thats working to a degree, if I put in *the, in my seach field, I get results showing words in front of and behind, the*.

For instance groove theory

Can I set it up for instance to do just words in front of or behind or both. Like this,

the* or *the or *the*
 
You can, it will just involve more coding to build your SQL.

I suppose the easiest way to do this would be to add two checkboxes to you form: chkBefore and chkAfter.

Code: 
If Not IsNull(Me.Artist) Then
    If Me.chkBefore And Me.chkAfter Then
        sCriteria = sCriteria & " AND [Artist] Like ""*" & Me.Artist & "*"""
    ElseIf Me.chkBefore Then
        sCriteria = sCriteria & " AND [Artist] Like ""*" & Me.Artist & """"
    ElseIf Me.chkAfter Then
        sCriteria = sCriteria & " AND [Artist] Like """ & Me.Artist & "*"""
    Else
        sCriteria = sCriteria & " AND [Artist] = """ & Me.Artist & """"
    End If
End If
 
Going back a bit - here's another search idea - just to offer other ideas.
 

Attachments

Come on, Mile. Surely you didn't just pull that off the top of your head. Nobody can just come up with that unless they are a freak of nature.;) Just wanted to say it was pretty cool stuff.
 
jon98548 said:
Come on, Mile. Surely you didn't just pull that off the top of your head. Nobody can just come up with that unless they are a freak of nature.;) Just wanted to say it was pretty cool stuff.
Click to expand...

The idea had been floating about in my head for a day or two, just hadn't tried it out till now. ;)

Forgot to put date criteria builder in but the idea is there. :cool:
 
Wild Card

OK got all of that in, but when I run the search I get this compile error Block If without End If
And I find the End Sub is highlighted, trouble is I cant find anything wrong

Private Sub cmdSearch_Click()
On Error Resume Next


Dim sCriteria As String
sCriteria = "WHERE ID Is Not Null"

'CDData SearchQry

If Not IsNull(Me.CDNo) Then
sCriteria = sCriteria & " AND [CDNo] = " & """" & Me.CDNo & """"
End If

If Not IsNull(Me.TrackNo) Then
sCriteria = sCriteria & " AND [TrackNo] = " & """" & Me.TrackNo & """"
End If

If Not IsNull(Me.Style) Then
sCriteria = sCriteria & " AND [Style] = " & """" & Me.Style & """"
End If

If Not IsNull(Me.Year) Then
sCriteria = sCriteria & " AND [Year] = " & """" & Me.Year & """"
End If

If Not IsNull(Me.Quality) Then
sCriteria = sCriteria & " AND [Quality] = " & """" & Me.Quality & """"
End If

If Not IsNull(Me.Album) Then
If Me.chkBeforeAlb And Me.chkAfterAlb Then
sCriteria = sCriteria & " AND [Album] Like ""*" & Me.Album & "*"""
ElseIf Me.chkBeforeAlb Then
sCriteria = sCriteria & " AND [Album] Like ""*" & Me.Album & """"
ElseIf Me.chkAfterAlb Then
sCriteria = sCriteria & " AND [Album] Like """ & Me.Album & "*"""
Else
sCriteria = sCriteria & " AND [Album] = """ & Me.Album & """"
End If

If Not IsNull(Me.Artist) Then
If Me.chkBeforeArt And Me.chkAfterArt Then
sCriteria = sCriteria & " AND [Artist] Like ""*" & Me.Artist & "*"""
ElseIf Me.chkBeforeArt Then
sCriteria = sCriteria & " AND [Artist] Like ""*" & Me.Artist & """"
ElseIf Me.chkAfterArt Then
sCriteria = sCriteria & " AND [Artist] Like """ & Me.Artist & "*"""
Else
sCriteria = sCriteria & " AND [Artist] = """ & Me.Artist & """"
End If

If Not IsNull(Me.SongTitle) Then
If Me.chkBeforeSong And Me.chkAfterSong Then
sCriteria = sCriteria & " AND [SongTitle] Like ""*" & Me.SongTitle & "*"""
ElseIf Me.chkBeforeSong Then
sCriteria = sCriteria & " AND [SongTitle] Like ""*" & Me.SongTitle & """"
ElseIf Me.chkAfterSong Then
sCriteria = sCriteria & " AND [SongTitle] Like """ & Me.SongTitle & "*"""
Else
sCriteria = sCriteria & " AND [SongTitle] = """ & Me.SongTitle & """"
End If

sSql = "SELECT [ID], [CDNo], [TrackNo], [Album], [Style], [Artist], [SongTitle], [Quality], [Year] FROM CDData " & sCriteria & ";"
'MsgBox sSql 'Used for debugging purposes only
Forms![Search]![SearchResults].Form.RecordSource = sSql
Forms![Search]![SearchResults].Form.Requery

End If
End Sub
 
You are not closing the If Not IsNull() statement with an End If for these wildcard fields.

Put another End If at the ends of each of these blocks.
 
Wild Cards

Hurrah! thanks Milo it works a real treat, You've been more than helpfull
Now this search engine is working its gonna allow me to do so much more
Again Thanks for your help this afternoon:)
 
You must log in or register to reply here.

Similar threads

J
Im an absolute beginner. Please help
Replies
6
Views
479
Pat Hartman
P
amorosik
How to read IP Address of 'actually used' lan nic?
Replies
12
Views
783
Jason Lee Hayes
Jason Lee Hayes
G
Relationships and Tables conundrums
Replies
9
Views
960
MajP
MajP
R
Can you out perform ChatGTP?
2 3
Replies
44
Views
2,559
CarlettoFed
C
Z
Search Fields
Replies
3
Views
425
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom