asked on

Using Chr(34) in VBA Recordset Where's and Joins

I was giving the great advice from capricorn1 to use Chr(34) in VBA Recordset Where and Join conditions. It works great. I'm thinking of using it for all strings that are in Joins or Where conditions. Does anyone know of downsided (other than my time) in doing this. My users keep wanting to use single quotes and other special characters and I'm sick of trying to explain that they can't do it. Below is an example of how I am modifying my queries to make them safe. Does anyone know of any problems with this?

Note: the other part of this fix is that I don't allow the user to enter double quotes into my forms. My warning message tells them to use two single quotes next to each other instead of double quotes.

'  strQuery = "SELECT Bulk, FDG FROM Components" & _
'             " WHERE [Component_Num] = " & "'" & strComponentNum & "'"   'Unsafe with single quotes
  strQuery = "SELECT Bulk, FDG FROM Components" & _
    " WHERE [Component_Num] = " & Chr(34) & strComponentNum & Chr(34) & ""  'Safe with single quotes

Open in new window

lee555J5

No problems that I know of. It makes the code much cleaner and much more explicit. I use Chr(39) and Chr(34)
Lee

lee555J5

It also makes your JOINs and WHEREs much more consistent between strings and numerics.

Lee

lee555J5

Why is the "" at the end of the safe example? That looks like a holdover from deleting the single quote. You do not need the last & "", but you might need a ;
Lee

Jim P.

There is not really a downside other than coding time. I use it all the time depending on what I'm doing.

Helen Feddema

I agree. I always use Chr(39) and Chr(34) (and sometimes Chr(35) as well, for date delimiters). It makes your code more readable, and avoids problems with entering quotes into strings.

schmir1

ASKER

From Lee
>Why is the "" at the end of the safe example? That looks like a holdover from deleting the single quote. You do not >need the last & "", but you might need a ;
Your right. I don't need a the "" at the end. Thanks. That will clean up my code a bit.
Question: Why would I need a ;? I never use them in recordset code for the last 10 years. I never noticed anything bad happening. Is this going to get me into trouble at some point? How would I put them in my example above.

From Helen
>I always use Chr(39) and Chr(34) (and sometimes Chr(35) as well, for date delimiters).
When would I use chr(39) and chr(35)?

ASKER CERTIFIED SOLUTION

lee555J5

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schmir1

ASKER

Thanks Lee. Good info.

schmir1

ASKER

Great Answer