Improve company productivity with a Business Account.Sign Up

x
?
Solved

Correct VBA / SQL statement please

Posted on 2012-04-04
2
Medium Priority
?
235 Views
Last Modified: 2012-04-04
super quick one. I don't know how to use the update statement in sql

This is the essence of what I want:


strSql = "UPDATE " & SettingsTable & "SET [LastOOSLAImportDate] = (#" & Format(Date, "yyyy-mm-dd") & "#)"

SettingsTable is a variable I've defined. LastOOSLAImportDate is a field in the SettingsTable. The table doesn't have a bunch or rows, it's a simple non-indexed table with a few settings on it I use to retrieve settings from.

I want to update the one row thats in there to set LastOOSLAImportDate to today.

Cheers
0
Comment
Question by:Ross
2 Comments
 
LVL 37

Accepted Solution

by:
Norie earned 2000 total points
ID: 37805357
Is SettingsTable the name of the table or the name of a variable with the table name?

If it's the table name:
strSql = "UPDATE SettingsTable SET [LastOOSLAImportDate] = (#" & Format(Date, "yyyy-mm-dd") & "#)"

Open in new window

If it's a variable with the table name:
strSql = "UPDATE [" & SettingsTable & "] SET [LastOOSLAImportDate] = (#" & Format(Date, "yyyy-mm-dd") & "#)"

Open in new window

Which is basically the same as your code, but wiht [] added in case the table name has spaces.
0
 
LVL 2

Author Comment

by:Ross
ID: 37805363
strSql = "UPDATE [" & SettingsTable & "] SET [LastOOSLAImportDate] = (#" & Format(Date, "yyyy-mm-dd") & "#)"
                                            

Open in new window


That's the ticket - thanks very much. I just couldn't get the formatting right!
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question