 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2006SQL Update Query and variables
Good afternoon, I'm having trouble getting my SQL Update Query to read a variable value into the field specified (or any field for that matter). This is my first Access application in over a year (still working on the Vacation Planner, but have backtracked to the setup phase... where HR runs a form and fills in all the Mondays of the bid year), so I'm horribly rusty. I need to populate the "WeekStart" field in the table. Can someone please help? The SQL statement was created by creating a query in the query builder and converting it to SQL, then changing as needed. Code is: Private Sub cmdSubmit_Click() Dim dsetdate As Date dsetdate = DateValue(Forms!frmCalSetup.txtdate) Dim intcurweek As Integer For intcurweek = 1 To 51 DoCmd.RunSQL "UPDATE [tblVacRestrict-Updated] SET [tblVacRestrict-Updated].WeekStart = dsetdate WHERE ((([tblVacRestrict-Updated].WeekNo) = intcurweek))" dsetdate = DateAdd("d", 7, dsetdate) Next intcurweek End Sub Thank you for helping! I appreciate it! Derek Nothing like thinking it through with some help from the AccessWeb (MVP) pages! The revised code is: Private Sub cmdSubmit_Click() Dim dsetdate As Date dsetdate = DateValue(Forms!frmCalSetup.txtdate) Dim intcurweek As Integer For intcurweek = 1 To 51 DoCmd.RunSQL "UPDATE [tblVacRestrict-Updated] SET[tblVacRestrict-Updated].WeekStart = #" & Format$(dsetdate, "mm/dd/yyyy") & "# WHERE ((([tblVacRestrict-Updated].WeekNo) = " & intcurweek & "))" dsetdate = DateAdd("d", 7, dsetdate) Next intcurweek End Sub It was ALL in the SQL statement with the formatting of the date and the way I was handling the quotations... those little things will be my demise! Derek > Good afternoon, > I'm having trouble getting my SQL Update Query to read a variable value into [quoted text clipped - 21 lines] > Thank you for helping! I appreciate it! > Derek Try this DoCmd.RunSQL "UPDATE [tblVacRestrict-Updated] SET [tblVacRestrict-Updated].WeekStart = #" & dsetdate & "# WHERE ((([tblVacRestrict-Updated].WeekNo) = " & intcurweek & "))" dsetdate = DateAdd("d", 7, dsetdate) =================================== For future reference If you have a string variable you add single quote before and after " Where FieldName = '" & Var & "'" For date, you add # " Where FieldName = #" & Var & "#" For number, nothing " Where FieldName = " & Var  SignaturePlease respond to the group if your question been answered or not, so other can refer to it. Thank you and Good luck > Good afternoon, > I'm having trouble getting my SQL Update Query to read a variable value into [quoted text clipped - 21 lines] > Thank you for helping! I appreciate it! > Derek > For date, you add # > " Where FieldName = #" & Var & "#" <ahem> not for the other 94% of the world that don't use USA dates... Tim F I don't follow, what do you mean? What is the connection to USA and #, I don't use USA date and still use # before and after the date. Unless you use it with SQL Signature\\// Live Long and Prosper \\// > > For date, you add # > > " Where FieldName = #" & Var & "#" > > <ahem> not for the other 94% of the world that don't use USA dates... > > Tim F >> > For date, you add # >> > " Where FieldName = #" & Var & "#" [quoted text clipped - 4 lines] > What is the connection to USA and #, I don't use USA date and still > use # before and after the date. If you really mean that the way you have typed it, then you are almost certainly in trouble. Using a phrase like WHERE SomeDate = #01/03/2006# will return dates in January -- wherever you are in the world. And the danger is that a VBA expression like "WHERE SomeDate = #" & MyDate & "#" will use whatever format is typed into the control panel Regional Settings. If you are lucky, that is a USA or ISO format (the latter extremely unlikely, I should guess). If you are a bit lucky, then it might be one with english fullnames (like "dd mmmm yyyy") which will also be parsed correctly. Otherwise, you'll put a dmy date into the string which may or may not be parsed correctly. In any case, it's out of your control as the developer and that is a Bad Thing. The only safe way to do things is to manage the formatting yourself: "WHERE SomeDate = " & Format(SomeDate, "\#yyyy\-mm\-dd\#") > Unless you use it with SQL What else? From the original post: >>> I'm having trouble getting my SQL Update Query to read <<< Remember that DLookup() and related functions pass their criterion strings straight through to the db engine, so the same syntax applies. Best wishes Tim F It's just that I'm a part of the 94%, and I never needed to set up the format, unless sending parameters to the SQL Server, then I needed to change the format on the date. In my organization, the Regional Settings are set automatically as we want when the user log on, so they all set the same, so I assume this is why I never had the need to set the format. Thanks for clarifying it for me. Signature\\// Live Long and Prosper \\// > >> > For date, you add # > >> > " Where FieldName = #" & Var & "#" [quoted text clipped - 38 lines] > > Tim F > It's just that I'm a part of the 94%, and I never needed to set up the > format, unless sending parameters to the SQL Server, then I needed to > change the format on the date. I am very suspicious about how this is working; unless you are using exclusively GUI methods like DoCmd.RunSQL and so on, where the Access expression evaluator will intercept and make silent changes to the queries. Although this usually works out okay, it tends to creep me out: I like to get the thing debugged and working and then left alone! > I ment SQL Server then you use single quote (I'm not sure about other DB) I am not an SQL Server expert, but with the little work I have done using ADP-ADO-MSSQLS the date format seems to be "\'yyyymmdd\'" -- I rather think that you have Access doing this for you behind your back. All the best Tim F I ment SQL Server then you use single quote (I'm not sure about other DB) Signature\\// Live Long and Prosper \\// > I don't follow, what do you mean? > What is the connection to USA and #, I don't use USA date and still use # [quoted text clipped - 7 lines] > > > > Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.