Special characters in Default Value and Query (1 Viewer)

blacksaibot

Registered User.
Local time
Today, 15:51
Joined
Jan 20, 2010
Messages
31
First of all, this is not my database design choice, it's just what I have to deal with....

The DB guy designed a table which has a field with a default value of 'No' (single quotes include). It's a textfield.

I am creating a form to input data into that table. I want the textbox that is associated with the above mentioned field to have its default value as 'No' (single quotes included). If I just type 'No' in the properties box for the text box, it removes the single quote when I view my Form in Form View.

ALSO, I then need to make my code below create a SQL query that will not have an error in it when inserting a string with a single quote in it.

Thanks
 

plog

Banishment Pending
Local time
Today, 14:51
Joined
May 11, 2011
Messages
11,611
Setting default values, writing INSERT queries; How come what you are doing isn't the job of this DB guy?

For your issue, you simply need to use double quotes to encapsulate the string that has single quotes. For the default value try this:

="'No'"

The same should work for an SQL string.
 

blacksaibot

Registered User.
Local time
Today, 15:51
Joined
Jan 20, 2010
Messages
31
Setting default values, writing INSERT queries; How come what you are doing isn't the job of this DB guy?

For your issue, you simply need to use double quotes to encapsulate the string that has single quotes. For the default value try this:

="'No'"

The same should work for an SQL string.

It's a long story... but basically I'm trying to fix his poor mistakes without taking over his "awesome" design. Politics..........

I'll try your suggestion.
 

blacksaibot

Registered User.
Local time
Today, 15:51
Joined
Jan 20, 2010
Messages
31
The textbox's default value worked... but this is the error I get when running my insert code after clicking the submit button

Run-time error '3705':
Syntax error (missing operator) in query expression "'No'".

This is the code that's used to grab the value in my query:

Code:
queryStr = "INSERT INTO [t_User_Ldr_Info] VALUES(" & _
"'" & dag_rfi & "', " & _

'....and some more code down here to finish the query

should there be another way I grab the text from my textbox named dag_rfi (whose value is 'No')??
 

plog

Banishment Pending
Local time
Today, 14:51
Joined
May 11, 2011
Messages
11,611
Sorry about that. For the SQL you need to 'escape' the single quotes with another set of single quotes. This means to insert 'No' into your database you need to pass it ''No''. Those aren't double quotes, those are 2 sets of single quotes. Its best to use the Replace function to accomplish this.

To insert 'No' into your database your SQL would look like this:

"INSERT INTO [t_User_Ldr_Info] VALUES ('" & Replace(dag_rfi, "'", "''") & "');"
 

Users who are viewing this thread

Top Bottom