UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting SQL Update Query To Delimit Correctly In VBA, Access 2016    
 
   
ozdave
post Dec 29 2017, 03:29 AM
Post#1



Posts: 20
Joined: 5-April 17



I hope I haven't put too much information below. The problem I'm having is getting the strSQL line to work. I haven't successfully parsed it. It is an UPDATE query. The plan in this procedure is to loop through several tables and Update them using the code below. In the line that starts with the strSQL the Amount field is numeric, unfortunately the values in the Amount field are negative and they need to be multiplied by -1 to show correctly as positive values. I haven't been able to correctly apply Quotes to the SQL, this is a real frustration for me and I would really love to be able to get this right. In fact I would really like to master this process of delimiting strings in SQL and VBA. i would sincerely appreciate your help, thanks.

CODE
[font="Courier New"]

Sub UpdateAmountSign()

   On Error GoTo UpdateAmountSign_Error

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strSQL As String
Dim strNameShort As String
Dim strNameLong As String

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    With tdf
        strNameLong = tdf.Name
        strNameShort = Left(strNameLong, 3)
        
        Select Case strNameShort
            Case "CBA", "HUM"
             [SQL]   strSQL = "UPDATE " & Chr(34) & strNameLong & Chr(34)  SET [Amount] = " & [Amount] * -1 [/SQL]
                Debug.Print strSQL
                
                dbs.Execute strSQL, dbFailOnError
                
            Case Else
                'Update not required
        End Select
    End With
    
Next tdf

    MsgBox "Update Complete"

CleanExit:
dbs.Close
Set dbs = Nothing
Set tdf = Nothing
   Exit Sub

UpdateAmountSign_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateAmountSign of Sub basAdmin"
    
    Resume CleanExit
    
End Sub
[/font]

Go to the top of the page
 
cheekybuddha
post Dec 29 2017, 06:33 AM
Post#2


UtterAccess VIP
Posts: 9,415
Joined: 6-December 03
From: Telegraph Hill


Hi,

It would be handy if you C&P'd the out put from your Debug.Print line here! It's in the Immediate Window (Ctrl+G).

Looking at your code you probably want something more akin to:
CODE
' ...
                strSQL = "UPDATE [" & strNameLong & "] SET [Amount] = " & [Amount]  & "* -1;"
                Debug.Print strSQL
                dbs.Execute strSQL, dbFailOnError
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ozdave
post Dec 29 2017, 08:57 PM
Post#3



Posts: 20
Joined: 5-April 17



Hi David, thanks for your response.

I C&P your line of code, then ran it, but it came up with a Compile Error - External name not defined - and it highlighted the [Amount] field, the 2nd instance of it. I don't know what that was about. The only time I got a debug.print output was when I changed to using the Chr(34) delimiter, but it still wasn't right then either. I've read a fair bit about delimiting SQL strings in VBA topic, but I'm disappointed I haven't been able to get this working. I haven't found examples where there have been more than one instance of quotes being required. This little snippet has two instances and it seems to make the degree of difficulty that much harder! Anyway, I hope you can work with me on this one, I really want to get this update query working properly. If you need more info about the design, or any other help, please just let me know.
Much appreciated
Dave
Go to the top of the page
 
moke123
post Dec 29 2017, 09:41 PM
Post#4



Posts: 1,202
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



you could probably eliminate one set of quotes with

CODE
strSQL = "UPDATE '" & strNameLong & "' SET Amount  = " & Abs(Amount)

Go to the top of the page
 
ozdave
post Dec 30 2017, 03:45 AM
Post#5



Posts: 20
Joined: 5-April 17



Thanks moke123,

I would run your code but using the Abs() function, isn't that the absolute function? If I ran it it would make everything positive, whereas I need to reverse the signs of the Amount field for that table. So positives become negatives and negatives become positives. The way in which financial data is presented here (in Oz) means it is shown in a variety of formats. These are bank statements and there is no uniform way in which the data is presented so I have to do this update on certain tables. I'd do it manually but why not use VBA (if I can get it working). Anyway, if you could re-frame your code to multiplying it by -1 that'd be appreciated.
Thanks
Dave
Go to the top of the page
 
cheekybuddha
post Dec 30 2017, 05:54 AM
Post#6


UtterAccess VIP
Posts: 9,415
Joined: 6-December 03
From: Telegraph Hill


>> Compile Error - External name not defined <<

Do you have a field in your form's recordset named 'Amount'?

Otherwise, where are you getting the value from, which you are trying to write to the table?

**** AARGH! Sorry - I misread the intention of the SQL ****

OK, it's much simpler! Try:
CODE
' ...
                strSQL = "UPDATE [" & strNameLong & "] SET [Amount] = [Amount]* -1;"
                Debug.Print strSQL
                dbs.Execute strSQL, dbFailOnError
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
moke123
post Dec 30 2017, 07:03 AM
Post#7



Posts: 1,202
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



QUOTE
absolute function? If I ran it it would make everything positive,

Yes it would but thats what your original question asked for.
Go to the top of the page
 
ozdave
post Dec 30 2017, 11:37 PM
Post#8



Posts: 20
Joined: 5-April 17



Thanks cheekybuddha,

I'm currently in a nirvana state after running your updated code. It worked beautifully! In its simplicity you provided an answer that was less complicated than I thought it would be, thank you. This is part of my bigger issue which is with "knowing the rules" of how to correctly do these SQL, VBA, Delimiting problems with strings and combinations of them with other variables or data types. I wonder if you could point me in the direction of any resources that might help me understand this topic? If I'm going to progress with Access I've got to be able to do these problems, any help on that would be appreciated. You've spared me any further anguish on this problem so, thanks very much.
Kind regards
Dave
Go to the top of the page
 
ozdave
post Dec 30 2017, 11:44 PM
Post#9



Posts: 20
Joined: 5-April 17



Hi moke123,

Yes, I'm sorry, I re-read my intro and you were quite right, I hadn't made it clear that I wanted to reverse the signs, so, sorry, I guess it was a rookie mistake. I'll be more specific in the future.
Thanks
Dave
Go to the top of the page
 
moke123
post Dec 31 2017, 10:42 AM
Post#10



Posts: 1,202
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



here's a couple...

dynamic SQL

Parameter query
The nice thing about a parameter query, I believe, is it handles the delimeters for the parameters for you.

the best way to troubleshoot delimiters is to comment out the execution and use debug.print to see what the result is and then make adjustments as needed.
This post has been edited by moke123: Dec 31 2017, 10:46 AM
Go to the top of the page
 
ozdave
post Dec 31 2017, 07:58 PM
Post#11



Posts: 20
Joined: 5-April 17



Hi Moke123,

Thanks for the links. Just one final thing, I searched the help system for help on closing the topic, how do you do that? I couldn't find anything but my question has been answered, so I don't want to leave this topic open, I'd appreciate some help on doing that.
Have a Happy New Year for 2018,
Thanks again,
Dave
Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 12:17 PM
Post#12


UtterAccess VIP
Posts: 9,415
Joined: 6-December 03
From: Telegraph Hill


No need to close on UA, Dave. Your thread is a useful resource for others!

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st January 2018 - 03:39 PM