What error message did you get? Or how do you know it is wrong? What did you expect it to do that it doesn't do?
Giving us code without an error message or tangible symptom is like going to a doctor and saying "Doctor, it hurts when I slam the door on my finger." So would you be surprised to hear the doctor say, "Don't slam the door on your finger." ?? How about some symptoms?
What error message did you get? Or how do you know it is wrong? What did you expect it to do that it doesn't do?
Giving us code without an error message or tangible symptom is like going to a doctor and saying "Doctor, it hurts when I slam the door on my finger." So would you be surprised to hear the doctor say, "Don't slam the door on your finger." ?? How about some symptoms?
If you're going to apply also Doc's suggestion, then maybe: "UPDATE [Order] SET DateOrdered = #" & Format(Date, "dd-MM-yyyy") & "# WHERE [Shopping Cart] = 0 And [PO]= 0;"
EDIT: I simplified it a bit... but it should have worked without the formatting, as Josef points out. However, I hate working with date objects in ALL languages, so if that makes it more explicit, then great.
It says in part: the VBA expression service offers over 100 VBA functions that you can use in SQL expressions
Alas, there is no list of functions that are supported that I know of. But Date() function is one of them, so use that rather than rolling your own date formatting.
Mike, as many have suggested, leave Date() as you had it originally - don't format it here.
If you really do want to format it into the a literal date in your SQL string then do not use the format suggested by Edgar:
Code:
strSQL = "UPDATE [Order] SET DateOrdered = " & "#" & Format(Date, "dd-MM-yyyy") & "#" & " WHERE [Shopping Cart] = 0 And [PO]= 0;"
^^^^^^^^^^
||||||||||
Do NOT use this format!
If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...
The so-called Expression Service has been available since Access 2000 / Jet 4.0. Through this functions
- Access functions (Nz, DLookup)
- VBA functions (Left, Instr, Date())
- custom functions
processed directly by Jet/ACE when those functions do not require arguments from outside the query.
In a way, this is a substitute for the fact that the actual language range of Jet-SQL is very narrow.
Date() as a function (with brackets) can therefore be used directly, additional formatting is only cumbersome (more effort) and more error-prone in design.
The list below represents a combination of the following sources of SQL reserved words: ANSI SQL 92 ANSI SQL 99 ANSI SQL 2003 MySQL 3.23.x MySQL 4.x MySQL 5.x MySQL 8.x PostGreSQL 8.1 MS SQL Server 2000 MS ODBC Oracle 10.2 There are undoubtedly more sources that we should add to this list, but...
www.drupal.org
A little more care in your own designation regime avoids such problems.
The QBE wastefully uses brackets to escape labels. Apparently not wasteful enough.
Such brackets, which are actually unnecessary, bother me because I look at and analyze queries and these brackets make the overview massively difficult.
This would not have happened with tblOrders instead of Order.
Code:
strSQL = "UPDATE tblOrders SET DateOrdered = Date() WHERE ShoppingCart = 0 And PO = 0;"
This should work without formatting the Date(). When you build SQL using VBA and it doesn't work, print the string out to the debug window, copy the string, and paste it into the QBE in SQL view and run it. You will frequently get a better error message.
It works because the query engine knows what the Date() function is and it obtains the date when it executes the query. Where you run into trouble with dates is when you include STRING values in the SQL. If the date is a STRING in the SQL, it MUST be in standard US date format - mm/dd/yyyy or the unambiguous yyyy/mm/dd format. So string values always need formatting. You can also refer to a form control in the query without formatting for the same reason that Date() and Now() work - objects that are defined as date data types do NOT contain dates that are STRINGS, they contain dates as they are stored by the database - double-precision numbers. The integer portion of the number is the number of days since Dec 30, 1899 (for Jet and ACE, SQL Server uses Jan 1, 1900 as the 0 date). The decimal portion is the elapsed time since midnight.
So,
12/30/1899 12:00 PM = 0.5
12/31/1899 06:00 PM = 1.75
12/29/1899 06:00 AM = -1.25