Solved

Correct VBA / SQL statement please

Posted on 2012-04-04
2
212 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:RossDagley1
2 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 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:RossDagley1
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

828 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