Problems with an ' in my data

  • Thread starter Thread starter Guest
  • Start date Start date
G

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??
 
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??
Click to expand...

stLinkCriteria = "[Artist]= """ & Me![Combo0] & """"
 
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??
Click to expand...

stLinkCriteria = "[Artist]= """ & Me![Combo0] & """"
Click to expand...
 
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??
Click to expand...

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]
 
Back
Top