Solved SQL quotes

[GUIDO22]

I am constructing an SQL string in code and am using the following to detect where a String contract reference is NULL / not stated - in which case I use empty string parentheses ..

Nz(txtContract, " '' ")
..

However, when the string is constructed the parentheses are being removed and I am getting an error when I try to run the SQL... what have I missed here..?

Thanks in advance

 

[arnelgp]

what is the purpose?
instead of Null, put a blank string?

([TxtContract] & "")

 

[CJ_London]

Show the whole code used to construct the sql string, the one bit you provided is insufficient to determine what to correct.

best guess is to remove the double quotes

 

[Mike Krailo]

Nz(txtContract, " '' ")
..

Try single quotes inside double quotes instead. You really should post the whole SQL string.

 

[Pat Hartman]

Are you sure the expression shouldn't be:

Nz(txtContract, "")

That would substitute a ZLS for the null if that is what you want. Converting the null to a string is only necessary in limited situations so as the others suggested, we probably need more context.

 

[The_Doc_Man]

If you are trying to pass a ZLS into a function, you might have to use this syntax: """" (that is 4 x double-quote). The outer layer of quotes gets stripped when a quoted string is passed into a string-oriented function. Worse yet, I could see an argument for SIX quotes depending on what is actually intended to be passed and to what it is being passed.

 

[GUIDO22]

what is the purpose?
instead of Null, put a blank string?

([TxtContract] & "")

Thanks, I found this worked for me...
(txtContract & " '' ") ' using single quotes

 

[GUIDO22]

Are you sure the expression shouldn't be:

Nz(txtContract, "")

That would substitute a ZLS for the null if that is what you want. Converting the null to a string is only necessary in limited situations so as the others suggested, we probably need more context.

Yes - perhaps an oversight on my part(including the single quotes as I did) ... but pretty sure that will work.

 

[strive4peace]

I am constructing an SQL string in code and am using the following to detect where a String contract reference is NULL / not stated - in which case I use empty string parentheses ..

Nz(txtContract, " '' ")
..

However, when the string is constructed the parentheses are being removed and I am getting an error when I try to run the SQL... what have I missed here..?

Thanks in advance

the problem with your example is the extra spaces and the string inside the string
Nz(txtContract, " '' ")
should simply be
Nz(txtContract, "")

> constructing an SQL string in code
so maybe
Nz(txtContract, """")
or
Nz(txtContract, '')

inside a string delimited with double quotes, if you want ONE quote mark, use TWO. Optionally, you can use single quote marks instead without adjusting them

HOWEVER ... txtContract indicates you're referring to the value of a control on a form ... so maybe you need to take it OUT of the SQL

sSQL = "blah blah" & "'" & Nz(me.txtContract, "") & "'" & 'blah blah"

it would help if you'd provide more context, thanks

 

[Pat Hartman]

If you are trying to pass a ZLS into a function, you might have to use this syntax: """"

Not when you use the Nz(SomeField, "") . That expression substitutes a ZLS for a null.

You are thinking about concatenating something in a string.