Help with SQL

[TeeSee]

Could someone please critique the following pice of code? The SQL was
copied directly from the query

Private Sub cmdDeSelect_Click()
Dim strSQL As String
strSQL = "UPDATE tblCDMRvalues SET tblCDMRvalues.[Select] = No _
WHERE (((tblCDMRvalues.[Select])=Yes) AND
((tblCDMRvalues.ynDelete)=No));"

Debug.Print strSQL

DoCmd.RunSQL strSQL
End Sub

Thanks

 

[Albert D. Kallal]

Could someone please critique the following pice of code? The SQL was
copied directly from the query

Private Sub cmdDeSelect_Click()
Dim strSQL As String
strSQL = "UPDATE tblCDMRvalues SET tblCDMRvalues.[Select] = No _
WHERE (((tblCDMRvalues.[Select])=Yes) AND
((tblCDMRvalues.ynDelete)=No));"

Debug.Print strSQL

DoCmd.RunSQL strSQL
End Sub

Does the above even compile?? (go debug->compile EVERY time after you make a
code change.

Anway, I would try:


Dim strSQL As String
strSQL = "UPDATE tblCDMRvalues SET Select = No " & _
" WHERE Select = Yes AND ynDelete = No"

Debug.Print strSQL
DoCmd.RunSQL strSQL

Also, I notice that you have a debug.print. Try cutting/pasting your
debug-print results into the query builder and see if it works
The is *why* you
have a debug print -- to show you what the sql you are making in code looks
right. You usually be able to see that by cut/paste the results from
the debug window into the query builder, you can find your problem.

 

[TeeSee]

Could someone please critique the following pice of code? The SQL was
copied directly from the query

Private Sub cmdDeSelect_Click()
   Dim strSQL As String
   strSQL = "UPDATE tblCDMRvalues SET tblCDMRvalues.[Select] = No _
WHERE (((tblCDMRvalues.[Select])=Yes) AND
((tblCDMRvalues.ynDelete)=No));"

       Debug.Print strSQL

   DoCmd.RunSQL strSQL
End Sub

Does the above even compile?? (go debug->compile EVERY time after you makea
code change.

Anway, I would try:

 Dim strSQL As String
 strSQL = "UPDATE tblCDMRvalues SET Select = No " & _
          " WHERE Select = Yes  AND ynDelete = No"

  Debug.Print strSQL
  DoCmd.RunSQL strSQL

Also, I notice that you have a debug.print. Try cutting/pasting your
debug-print results into the query builder and see if it works
The  is *why* you
have a debug print -- to show you what the sql you are making in code looks
right. You usually be able to see that by cut/paste the results from
the debug window into the query builder, you can find your problem.

Thanks a lot Albert. I Learned a great deal from your comments. Have a
related question .......
When I cut/paste my strSQL output into the SQL window of a query build
the code works as long as it is in one line. As soon as I split it
into two or three lines it comes up witth all kinds of errors. Are
there rules for where and how you break these lines????

Thanks again

 

[Albert D. Kallal]

Thanks a lot Albert. I Learned a great deal from your comments. Have a
related question .......
When I cut/paste my strSQL output into the SQL window of a query build
the code works as long as it is in one line. As soon as I split it
into two or three lines it comes up witth all kinds of errors. Are
there rules for where and how you break these lines????

--------

If you look closely at that code, you'll see that the string in code is
always the result of one line of text. it must be that way to function
correctly.

What that means is the debug print is actually only one line of text output
in the debug window, and while it may wrap in the debug window, it is still
physically one line of text.

Are there rules for where and how you break these lines????

The rule as to where you break things actually applies to the visual basic
programming language, not the actual SQL line itself.

strSql = "select "

strSql = strSql & " * from "

strSql = strSql & " tblCustomers"

You can see in the above I can use many many lines of code to build up the
string into a variable.

You can use what is called a line continuation character in visual basic,
but the use of that feature has nothing to do with breaking up the SQL into
multiple lines. Because we often mess things up, that's why we use the debug
print to test and see if we going along ok.

The above 3 lines could be written as:

strSql = "select " & _
" * from " & _
"" tblCustomers"

about the only rule to keep in mind for visual basic and the line
continuation character is that you must break in a place that allows you to
do such. That place is quite much anywhere in the command..

So there's a BIG difference between building up a string over several lines
of code, and that of what is a legal spot in terms of using the line
continuation character when you're using an actual visual basic commands. In
the case of a visual basic commands, you useally must break ONLY where a
Parameter change occurs, such as right before or after a comma ( ",")

DoCmd.OpenForm "abc" _
, , , "id = 34"

or

DoCmd.OpenForm "abc", _
, , "id = 34"

is ok