Single apostrophe in search textbox

[JoseO]

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:

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.

 

[isladogs]

This link should get you there ...

http://allenbrowne.com/casu-17.html

 

[arnelgp]

" Where [SongChords] Like " Chr(34) & "*" & me.searchtbx & "*" & Chr (34)

 

[Orthodox Dave]

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]

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]

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]

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

Dim myFilter as String

myFilter = "[SongChords] LIKE ""*" & Me.SearchTbx & "*"""
Me.Filter = myFilter
Me.FilterOn = True

or for (b),

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:

Me.FilterOn = False

or

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]

 

[JoseO]

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!

 

[JoseO]

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!