Single apostrophe in search textbox (1 Viewer)

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Hello -

I am using Access 2010.

I have a form in which I am using a textbox (SearchTbx) as a search textbox to search through my song records. I have seen posts where single apostrophes are dealt with in the DCount function but I am using an SQL statement. I am not super new at Access VBA but I am still learning.

The following SQL string works fine:

Code:
Dim SQL as String

SQL = "SELECT SongT.*, ArtistT.* FROM SongT INNER JOIN ArtistT ON SongT.ArtistID = ArtistT.ArtistID" _
        & " WHERE [SongChords] LIKE '*" & Me.SearchTbx & "*'"
All is well with the above until I use a single quote such as if a song title starts with "I'm". I have seen where folks suggest the use of either Chr(34) or Chr(39) to overcome this issue but I cannot figure out how/where to install these Chr suggestions.

Any assistance is greatly appreciated.

Thank you.
 

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,629
" Where [SongChords] Like " Chr(34) & "*" & me.searchtbx & "*" & Chr (34)
 

Orthodox Dave

Home Developer
Joined
Apr 13, 2017
Messages
218
The link from ridders gives a simpler solution than Chr(34) - the use of double quotes twice instead of double and single quotes. In that link, Allen Browne says:
The single-quote character can be used in some contexts for quotes within quotes. However, we do not recommend that approach: it fails as soon as a name contains an apostrophe
 

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Thank you for all the replies! :)

arnelgp, your suggestion threw an error. I attempted to correct it but no go.

Ridders and Orthodox Dave, I had come across Allen Browne's website, in fact the link that you provided. A couple of things where the DLOOKUP function is a bit cumbersome for how I am running the particular form I am using:

As soon as the user opens the form that displays all the songs, all 307 songs automatically load to the "songs" textbox for the user to "thumb through". Within this form I have the search control textbox. If I place the DLOOKUP function in the songs textbox I surrender the loading of all songs upon load. The user is now relegated to using the search box to find their song and they must enter the entire song title for the function to work/display the song.

Under the current VBA structure, using the Dim SQL as String way, I am able to use the LIKE operator that will pull all songs according to the search box entry. I yet to use the DLOOKUP function embedded in VBA code let alone within an SQL statement. Is this doable?
 

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,629
you gotta be kiddin' me.
just tried it and it worked.

you have error there because of these:

"SELECT SongT.*, ArtistT.* From SongT ...


should be:

"SELECT * FROM SongT
 

Orthodox Dave

Home Developer
Joined
Apr 13, 2017
Messages
218
OK JoseO,

There are various ways you could achieve what you want, but this is what I would do.

1. Allow the form to load all the songs as usual (i.e. no SQL WHERE clause).
2. The user then enters part of the [SongChords] into the search filter box.
3. In the AfterUpdate event of the Search box, you can do one of two things:
(a) add a filter to the Form and turn the filter on. OR
(b) change the Form's RowSource to a new SQL including the WHERE clause.

So for (a), in the AfterUpdate event you'd put something like
Code:
Dim myFilter as String

myFilter = "[SongChords] LIKE ""*" & Me.SearchTbx & "*"""
Me.Filter = myFilter
Me.FilterOn = True
or for (b),
Code:
Dim mySQL as String

mySQL = "SELECT * FROM SongT INNER JOIN ArtistT ON SongT.ArtistID = ArtistT.ArtistID" & _
        " WHERE [SongChords] LIKE ""*" & Me.SearchTbx & "*"";"
Me.RowSource = mySQL
Me.Requery
That's a start hopefully, but you may need more help with clearing the filter or rowsource if the person wants a second go. I haven't tested this.

You can add a button to clear the filter, with its OnClick event containing one of these as appropriate:
Code:
Me.FilterOn = False
or
Code:
mySQL = "SELECT * FROM SongT INNER JOIN ArtistT ON SongT.ArtistID = ArtistT.ArtistID;"
Me.RowSource = mySQL
Me.Requery
[I modified this after arnelgp's post - he's right about the SQL]
 
Last edited:

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Thank you arnelgp and Dave!!

I will thoroughly test the attached. By the time I get home it'll pretty late for you Dave :) I'm not sure where "somewhere out" is for arnelgp so it may be the same for him :)

I honestly cannot thank you both enough! So very thankful for forums like Access-Programmers!
 
Last edited:

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Once again arnelgp and Dave, THANK YOU!

I am happy to report that your suggestions worked beautifully. A bit of tweaking here and there on my part but I definitively could not have done it with the solid foundation you both provided.

Really appreciate it fellows!
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom