Updating dates in an update query
UPDATE is especially useful when you want to change many records or when the records that you want to change are in multiple tables. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom: Sub Update X() Dim dbs As Database Dim qdf As Query Def ' Modify this line to include the path to Northwind ' on your computer.Set dbs = Open Database("Northwind.mdb") ' Change values in the Reports To field to 5 for all ' employee records that currently have Reports To ' values of 2. Execute "UPDATE Employees " _ & "SET Reports To = 5 " _ & "WHERE Reports To = 2;" dbs.In the past on various databases, I've had it work both ways and I don't have a clue why it works or does not work. You will not be able to run an update query against a SQL Server that does not have a primary key How about a Time Stamp field?I've found that it is a good habit to include one of these in every table as well as the PK, whenever I'm working in a multi-user environment. Please paste the string from the debug window so we can see the actual query.Execute s Sql When I paste the statement into SQL Server and replace the # signs with single tic ', it executes perfectly in SQL Server this is the actual code: s Sql = "Update tbl Inst_Time Sheet Set Date1 =#" & TD1 & "#, Date2 =#" & TD2 & "#, Date3 =#" & TD3 & "#, Date4 =#" & TD4 & "#, Date5 =#" & TD5 & "#, Date6 =#" & TD6 & "#, Date7 =#" & TD7 & "#, Date8 =#" & TD8 & "#, Date9 =#" & TD9 & "#, Date10 =#" & TD10 & "#, Date11 =#" & TD11 & "#, Date12 =#" & TD12 & "#, Date13 =#" & TD13 & "#, Date14 =#" & TD14 & "#" With monday.com’s project management tool, you can see what everyone on your team is working in a single glance.
When I use add_months() I get an invalid column name error.Perhaps this statement is being executed on the server (SQL Server) and not the client (Access).Try replacing the pound signs, which are the Access delineator for dates, with single quote marks, which are the SQL Server delineator for dates, and execute.ID | AID | PID | Contained Data | To Date Time _________________________________________________ 1 | 123 | 1 | xxxx | NULL 2 | 123 | 2 | 7/21/2015 AM | NULL 3 | 234 | 2 | xyxy | NULL 4 | 123 | 2 | 19/07/2015 | NULL 5 | 123 | 2 | NULL | NULL 6 | 345 | 3 | zzzz | NULL 7 | 123 | 2 | bad Time String | NULL ID | AID | PID | Contained Data | To Date Time _________________________________________________ 1 | 123 | 1 | xxxx | NULL 2 | 123 | 2 | 7/21/2015 AM | 2015-07-21 .000 3 | 234 | 2 | xyxy | NULL 4 | 123 | 2 | 19/07/2015 | 2015-07-19 .000 5 | 123 | 2 | NULL | NULL 6 | 345 | 3 | zzzz | NULL 7 | 123 | 2 | bad Time String | NULL I have been trying a query similar to what is below but it is filling To Date Time with the same values for every row (in the case of my actual data, NULL.) I'm not sure if something similar to this query is the right approach, or if I'm way off.