Sanitizing a string in Access 2003 SQL: Problem with '

Question

I'm writing a query that has to count the number of students enrolled in a course, and the way I'm doing it is like this:

DCount("[student/course table].[Student ID]","[student/course table]","[StartDate] = #" & [Course Start Date] & "# AND Location = '" & tblCourseDetails.Location & "' AND [Course Number] = '" & [Course Number] & "'")

The problem is that Location can contain apostrophes, which throws errors into my results. This seems to screw up a lot of things (like it asks for me to enter a parameter twice). Is there any simple way to get around apostrophes? I was thinking maybe using Replace()=Replace() which would be a pretty simple solution, but if there's any other ways around this I'd like to know.

I'm not too worried about SQL injection. To use this query you'd have pretty much access to the database anyways.

This isn't my whole query, if you think I should post it, tell me.

Answer 1

You need to add an escape character. For access I believe it is '' for '.

So find and replace ' with '' and you should be good to go.

Edit: That is an extra apostrophe, not a double quotation mark.

Replace(tblCourseDetails.Location, "'", "''")