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

schmir1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lee555J5Commented:
No problems that I know of. It makes the code much cleaner and much more explicit. I use Chr(39) and Chr(34)
Lee
0
lee555J5Commented:
It also makes your JOINs and WHEREs much more consistent between strings and numerics.

Lee
0
lee555J5Commented:
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


0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim P.Commented:
There is not really a downside other than coding time. I use it all the time depending on what I'm doing.
0
Helen FeddemaCommented:
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.
0
schmir1Author Commented:
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)?

0
lee555J5Commented:
Re: ; - Technically, SQL statements are terminated with the semicolon. Sometimes in Access, you do not need it; for example,  the RowSource property of a combobox. I always use one.
In your code,
strQuery = "SELECT Bulk, FDG FROM Components" & _
    " WHERE [Component_Num] = " & Chr(34) & strComponentNum & Chr(34) & ";"

Re: Chr(34) double-quote - not used to delimit strings in the same way as Chr(39), but you can use it to insert a double-quote into a quoted string. I have mostly used it in concatenations--it depends on need. Others may have better use examples or explanations.
Re: Chr(35) date delimiter - the same as using Chr(39) to delimit strings; for example, see paste attached code into module, click mouse in the new sub, and hit F5. You use the Chr(35) when you are building a date in code with concatenations
strQuery = "SELECT Bulk, FDG FROM Components" & _
    " WHERE [DateFieldName] = " & Chr(35) & date_as_string & Chr(35) & ";"
Re: Chr(39) single quote - used to delimit strings as in your example code
Lee


Public Sub subDateTest()

    Dim dtTest As Date
    dtTest = 4 / 4 / 2010
    MsgBox dtTest
    dtTest = #4/4/2010#
    MsgBox dtTest

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schmir1Author Commented:
Thanks Lee.  Good info.
0
schmir1Author Commented:
Great Answer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.