Problems with an ' in my data

R

Ray

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??
 
F

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] & """"
 
R

Ray

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

fredg said:
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] & """"
 
J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top