Problems with an ' in my data

[Guest]

I have exported a list of all my music in Windows Media Player into Excel and
then imported the Excel info into a table in Access. I created a Form with a
combo box based on a query to get the names of all the artists. I use the
combo box to chose an artist to use as link criteria when opening a report so
I can see all songs done by that artist. Below is the code I have used.

Dim stDocName As String
Dim stLinkCriteria
stLinkCriteria = "[Artist]=" & "'" & Me![Combo0] & "'"
stDocName = "Songs"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I have used this code dozens of times in the past with no problems.
Unfortunately this data in the combo box contains an ' in many of the artists
names (example: Herman's Hermits). This is causing problems with the
stLinkCriteria variable. Can I rewrite the code to keep the ' from causing
problems??

 

[fredg]

I have exported a list of all my music in Windows Media Player into Excel and
then imported the Excel info into a table in Access. I created a Form with a
combo box based on a query to get the names of all the artists. I use the
combo box to chose an artist to use as link criteria when opening a report so
I can see all songs done by that artist. Below is the code I have used.

Dim stDocName As String
Dim stLinkCriteria
stLinkCriteria = "[Artist]=" & "'" & Me![Combo0] & "'"
stDocName = "Songs"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I have used this code dozens of times in the past with no problems.
Unfortunately this data in the combo box contains an ' in many of the artists
names (example: Herman's Hermits). This is causing problems with the
stLinkCriteria variable. Can I rewrite the code to keep the ' from causing
problems??

stLinkCriteria = "[Artist]= """ & Me![Combo0] & """"

 

[Guest]

I did everything to screw it up (unintentionally). After I fixed MY mistakes,
your code worked perfectly....... Thanks.......

I have exported a list of all my music in Windows Media Player into Excel and
then imported the Excel info into a table in Access. I created a Form with a
combo box based on a query to get the names of all the artists. I use the
combo box to chose an artist to use as link criteria when opening a report so
I can see all songs done by that artist. Below is the code I have used.

Dim stDocName As String
Dim stLinkCriteria
stLinkCriteria = "[Artist]=" & "'" & Me![Combo0] & "'"
stDocName = "Songs"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I have used this code dozens of times in the past with no problems.
Unfortunately this data in the combo box contains an ' in many of the artists
names (example: Herman's Hermits). This is causing problems with the
stLinkCriteria variable. Can I rewrite the code to keep the ' from causing
problems??

stLinkCriteria = "[Artist]= """ & Me![Combo0] & """"

 

[John Vinson]

I have exported a list of all my music in Windows Media Player into Excel and
then imported the Excel info into a table in Access. I created a Form with a
combo box based on a query to get the names of all the artists. I use the
combo box to chose an artist to use as link criteria when opening a report so
I can see all songs done by that artist. Below is the code I have used.

Dim stDocName As String
Dim stLinkCriteria
stLinkCriteria = "[Artist]=" & "'" & Me![Combo0] & "'"
stDocName = "Songs"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I have used this code dozens of times in the past with no problems.
Unfortunately this data in the combo box contains an ' in many of the artists
names (example: Herman's Hermits). This is causing problems with the
stLinkCriteria variable. Can I rewrite the code to keep the ' from causing
problems??

In two ways:

1. Use " to delimit the string rather than '. You can insert " in a
string delimited by " by using two consecutive ":

stLinkCriteria = "[Artist]=""" & Me![Combo0] & """"

2. Use the Replace() function to change all ' to '' (two consecutive
singlequotes) in the criterion:

stLinkCriteria = "[Artist]='" & Replace(Me![Combo0],"'", "''") &
"'"


John W. Vinson[MVP]