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.

buckbova · Accepted Answer · 2010-07-13 18:02:25Z

3

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, "'", "''")

edited Jul 13, 2010 at 18:02

answered Jul 13, 2010 at 17:35

buckbova

1,2336 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jeff Over a year ago

What would the exact code for this be? When I try & Replace([Location],"'","''") LIKE Replace([tblCourseDetails].[Location],"'","''") & or & Replace([Location],"'","''") = Replace([tblCourseDetails].[Location],"'","''") & my query doesn't work properly (it'll just give 0 for the count).

Fionnuala Over a year ago

Just the one Replace: Location = '" & Replace(tblCourseDetails.Location,"'","''") & "' AND

Jeff Over a year ago

Perfect, that works now. I realized right after I posted my last comment that the first shouldn't be outside the string. Then I went on a wild goose chase trying to convert Location = '" & Replace(Location...) to Replace(Location, "'", "''") = '" & Replace(Location...) but I see why that's not needed now. For future reference, is there any way to get Replace(Location, "'", "''") = '" & Replace(Location,"'","''" & ... to work? Ie: Replace within the DCount criteria string

buckbova Over a year ago

@Jeff I'm sorry I'm a bit confused by your last question.

Jeff Over a year ago

It's nothing important, but whenever I run Repalce(Location, "'", "''") within the DCount criteria string, it gives me a syntax error. I believe it's because it's going outside of the string with the first ". But I'm not sure

|

Collectives™ on Stack Overflow

Sanitizing a string in Access 2003 SQL: Problem with '

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related