Apostrophe in the Middle of a String

[wilderfan]

I am working on a db for a list of films (including the company name and year of shooting).

One of the fields is Production which is used for the titles of the films. And of course, sometimes film titles include apostrophes or other special characters. eg. Look Who's Talking

When the user selects a Production (film title) which does NOT have an apostrophe, the code below works perfectly.


Private Sub cmdPreviewReport_Click()

DoCmd.OpenReport "rptFilmListByCorpYear", acViewPreview, , "CorpName ='" & Forms!frmSelect!cboCorpName & _
"' AND RptgPeriod =#" & Forms!frmSelect!cboRptgPeriod & _
"# AND Production ='" & Forms!frmSelect!cboProduction & "'"

End Sub


But when there is an apostrophe in the film title, then an error message pops up.

Is there a workaround to prevent this problem?

 

[boblarson]

Change this:

DoCmd.OpenReport "rptFilmListByCorpYear", acViewPreview, , "CorpName ='" & Forms!frmSelect!cboCorpName & _
"' AND RptgPeriod =#" & Forms!frmSelect!cboRptgPeriod & _
"# AND Production ='" & Forms!frmSelect!cboProduction & "'"


to this:

DoCmd.OpenReport "rptFilmListByCorpYear", acViewPreview, , "CorpName =" & Chr(34) & Forms!frmSelect!cboCorpName & _
Chr(34) & " AND RptgPeriod =#" & Forms!frmSelect!cboRptgPeriod & _
"# AND Production =" & Chr(34) & Forms!frmSelect!cboProduction & Chr(34)

 

[wilderfan]

Thank you, Bob.

I'll give it a try.


-- Robert