We help IT Professionals succeed at work.

Breaking up SQL Lines

larkahn
larkahn asked
on
I have the following code which works.

Data1.recordSource = "Select word, phonetic from phonetics where phonetic like ""*" & "?" & "*""  and phonetic not like ""*" & "a?" & "*"" and phonetic not like ""*" & "\?" & "*"" and phonetic not like ""*" & "?\" & "*"" order by word"

1. Is there any way I can shorten it e.g. do I need all of the     and phonetic not like   statements repeated?

2. In my Visual Basic environment the code is on one line. Is there some way to break it up into 3 lines. I've tried fooling around with the & _ but still haven't figured out if I am misplacing the quotation marks, etc.
Comment
Watch Question

Commented:
larkahn,

I have replaced the double quotes inside your SQL statement with single quotes and put the SQL statement into a string using several rows of code, making the code easier to read and edit.

I hope that this helps you.

Dim strSql As String

strSql = ""
strSql = strSql & "select word, phonetic"
strSql = strSql & " from phonetics"
strSql = strSql & " where phonetic like '*?'"
strSql = strSql & " and phonetic not like '*a?*'"
strSql = strSql & " and phonetic not like '*\?*'"
strSql = strSql & " and phonetic not like '*?\*'"
strSql = strSql & " order by word"

Data1.recordSource = strSql

Ture Magnusson
Karlstad, Sweden

Commented:
It is difficult to see the single and double quotes in the code above. It's easier to read if you copy the code and paste it into a VB module.

/Ture

Author

Commented:
Thanks. This is just what I was looking for. It took me a minute or two after inserting the code to see that there was a missing *  wild-card after the ? in the following.

strSql = strSql & " where phonetic like '*?'"

I appreciate the prompt answer in easy-to-understand manner.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.