Solved Build Query With Single/Double Quotes Potentially In Free Text Field

[LGDGlen]

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:

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]

Try using the Replace() function. For example:

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

 

[Minty]

The best way is to use a parameterised query.

This https://www.access-programmers.co.u...w-parameteres-expected-6.281472/#post-1453812 post demonstrates a simple way to set it up

 

[LGDGlen]

@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]

@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]

Correct It's effectively simply stored in memory, then deleted once the procedure is run.

 

[LGDGlen]

@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 ;-)