Delete Function

[sarah2855]

I'm reading Vb code of my ex-colleague and here is what I see

strSQL = "DELETE FROM tblA WHERE fieldA = """ & name & Chr(34)
DoCmd.RunSQL strSQL, -1

My question is why this is working !!! isn't it lacking one double quotation?? there is one quotation before DELETE but there is no ending quotation!!

[debasisdas]

Read again.

[NeoPa]

Thanks for that helpful comment Debas :D

Chr(34) actually resolves to a double-quote character ("). So you see this is perfectly sensible SQL.

The VBA is pretty poor though. Whoever wrote it was either very sloppy or trying to show they were clever. If this code is in a production system anywhere though (as opposed to a class and designed to make the students think) then they've only shown the opposite.

[NeoPa]

PS. A handy trick for seeing what a Chr() call resolves to is to hold the Alt key down while typing the number out on the numeric keypad. If you try this with 34 you'll see the result is ".

[sarah2855]

Thanks Neopa, I changed it to the following code, is that correct? it didn't throw any error message.
p.s: What kind of moderator is Debas!!! No one forced you to reply on the threads !

strSQL = "DELETE FROM tblA WHERE fieldA =" & Chr(34) & name & Chr(34)
DoCmd.RunSQL strSQL, -1

[sarah2855]

Oh and thanks for the trick , it was really helpful.

[colintis]

i think using Chr(34) would be preferred for easier reading of code, as using """ may sometime confuse about the quotes with a quick look through (e.g. Did i just see 5 quotes or 6? Did this part is including the double quotes to output?) . Anyway, it depends which method coder preferred, easy indicate or shorter coding. And using a unified standard of coding, as your ex-colleague's code was a bad example. (or as NeoPa said he was just showing off)

[NeoPa]

I would take a different view (without arguing with Colintis. His view is equally valid. Just different from mine).

I never saw a good reason for using Chr(34) to create a string with quotes (') in. It is not easier to read unless you already use it and recognise it. I would say rather that it was harder to read as a form of translation is necessary, where it is not necessary for using the character natively. This is clearly demonstrated by the question being required in the first place. Quotes (') and double-quotes (") (See Quotes (') and Double-Quotes (") - Where and When to use them) are easily distinguished when used correctly, either in the code window or, when posting, in the code tags, and have the benefit of showing exactly what is expected and required in the string. Only proportional fonts make '' look like ".

Only proportional fonts make '' look like ".

I would write your code as :

strSQL = "DELETE FROM tblA WHERE fieldA ='" & Me.Name & "'"
DoCmd.RunSQL strSQL, True

[OldBirdman]

All of the above is true. Different approaches and preferences.
If the value of Me.name contains a quote imbedded within it, all of the above will produce errors. If these are person's names, a single quote would be expected [James O'Reily]. Names of movies or music albums might contain either single or double quotes.

strSQL = "DELETE FROM tblA WHERE fieldA ='" & Me.Name & "'"

This assigns to strSQL the string [DELETE FROM tblA WHERE fieldA ='James O'Reily']
Note: Square brackets [] are used to show actual text string to avoid confusing quotes.

[NeoPa]

That's absolutely right OB. Further help with this can be found at SQL Injection Attack.