Solved Build Query With Single/Double Quotes Potentially In Free Text Field (1 Viewer)

LGDGlen

Member
Local time
Today, 14:40
Joined
Jun 29, 2021
Messages
229
Hi All

I am building an insert query from fields on a form and everything was working ok until a user added an apostrophe in a free text field which caused the query to not build correctly. So something like this:

Code:
db.Execute "INSERT INTO [TABLE] (freeTextField) VALUES ('" & Me.freeTextField & "')", dbFailOnError

So in the text field the user had entered: "This can't be done"

And this caused the query to fail. So my question is, is there a good way to santise the data in such circumstances or a way to cope with it where if the user enters nothing it still works as well. I tried with double quotes instead of single quotes but then when the freeTextField was empty things didn't work either.

Hope that all makes sense.

Kind regards

Glen
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
Try using the Replace() function. For example:
Code:
db.Execute "INSERT INTO [TABLE] (freeTextField) VALUES ('" & Replace(Me.freeTextField, "'", "''") & "')", dbFailOnError
 

LGDGlen

Member
Local time
Today, 14:40
Joined
Jun 29, 2021
Messages
229
@Minty ok so that looks like it is going to do what i want, thank you that helps alot. always learning something new thank you for pointing me in (what i'm hoping is) the direction
 

LGDGlen

Member
Local time
Today, 14:40
Joined
Jun 29, 2021
Messages
229
@Minty / @theDBguy wondering: Set qdf = CurrentDb.CreateQueryDef("", strSQL)

does the "" mean that the query is only temporarily created and i do not have have to delete any temporary queries after usage?
 

Minty

AWF VIP
Local time
Today, 14:40
Joined
Jul 26, 2013
Messages
10,355
Correct It's effectively simply stored in memory, then deleted once the procedure is run.
 

LGDGlen

Member
Local time
Today, 14:40
Joined
Jun 29, 2021
Messages
229
@Minty thank you, i'll get on that as it definitely look like it will solve my current and my potential future issues to do with writing this data as it is free text i'm sure the users will think of neat and interesting ways to cause issues ;-)
 

Users who are viewing this thread

Top Bottom