Syntax Error

[mike60smart]

Hi Everyone

What is wrong with the following please:-

strSQL = "UPDATE Order SET DateOrdered = Date() WHERE [Shopping Cart] = 0 And [PO]= 0;"

 

[The_Doc_Man]

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?

 

[mike60smart]

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?

My apologies Doc_Man
The error is as follows:
The Immediate window shows the following:-

UPDATE Order SET DateOrdered = Date() WHERE [Shopping Cart] = 0 And [PO]= 0;

 

[The_Doc_Man]

Might have to write that as

UPDATE Order SET DateOrdered = "#" & Format( Date(), "dd-MMM-yyyy" ) & "#" WHERE ...

Not going to swear on it, but the Date() function might return something unexpected in that context. So coerce it with the type coercion punctuation.

 

[Edgar_]

Isn't Order a reserved word?
Maybe try

UPDATE [Order] SET DateOrdered = Date() WHERE [Shopping Cart] = 0 And [PO]= 0;

 

[mike60smart]

Hi Doc_Man & Edgar

Now get the following error and it highlights the first #

 

[Josef P.]

Have you tested Edgar's suggestion without the string concatenation with format?

 

[Edgar_]

Hi Doc_Man & Edgar

Now get the following error and it highlights the first #

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.

 

[The_Doc_Man]

OK, nested quoting problem.

Set StrSQL = "UPDATE Order SET DateOrdered = '#' & Format( Date(), 'dd-MMM-yyyy' ) & '#' WHERE ...

The stuff after WHERE doesn't need quotes because they are numeric.

 

[Josef P.]

Formatting as a date string is not necessary. Date() can be processed by Jet/ACE.#
I bet: Code in #5 will work

 

[mike60smart]

Hi Everyone

Edgar's suggestion did the trick, another snippet to add to the Library

strSQL = "UPDATE [Order] SET DateOrdered = " & "#" & Format(Date, "dd-MM-yyyy") & "#" & " WHERE [Shopping Cart] = 0 And [PO]= 0;"

Many thanks for the help

 

[Josef P.]

BTW:

strSQL = "UPDATE [Order] SET DateOrdered = " & "#" & Format(Date, "dd-MM-yyyy") & "#" & " WHERE [Shopping Cart] = 0 And [PO]= 0;"
vs.
strSQL = "UPDATE [Order] SET DateOrdered = " & Format(Date, "\#yyyy-mm-dd\#") & " WHERE [Shopping Cart] = 0 And [PO]= 0;"

Allways format a SQL date string in ISO-Format (or as Format(Date, "\#mm\/dd\/yyyy\#"))=> then it works with any country setting.

In this case, however, I think the variant of #5 is better, as it is much easier to read.

UPDATE [Order] SET DateOrdered = Date() WHERE [Shopping Cart] = 0 And [PO]= 0;

 

[tvanstiphout]

Date() works. Why?
Check this out:

SQL expressions (Access desktop database reference)

Office developer client VBA reference documentation

learn.microsoft.com

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.

 

[cheekybuddha]

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:

strSQL = "UPDATE [Order] SET DateOrdered = " & "#" & Format(Date, "dd-MM-yyyy") & "#" & " WHERE [Shopping Cart] = 0 And [PO]= 0;"
                                                                   ^^^^^^^^^^
                                                                   ||||||||||
                                                            Do NOT use this format!

Use the format suggested by @Josef P.:

strSQL = "UPDATE [Order] SET DateOrdered = " & Format(Date, "\#yyyy-mm-dd\#") & " WHERE [Shopping Cart] = 0 And [PO]= 0;"

 

[MajP]

Put the CSQL in your library and not worry about it.

Get Filter from Controls, CSQL, and Combine Multiple Filters

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...

www.access-programmers.co.uk

 

[ebs17]

Formatting as a date string is not necessary. Date() can be processed by Jet/ACE.

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.

Isn't Order a reserved word?

I agree. Has this been checked for yourself?

List of SQL reserved words

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.

strSQL = "UPDATE tblOrders SET DateOrdered = Date() WHERE ShoppingCart = 0 And PO = 0;"

 

[Pat Hartman]

"Doctor, it hurts when I slam the door on my finger."

It's worse than that, it is more like
"Doctor, It hurts when I slam the door."

strSQL = "UPDATE Order SET DateOrdered = Date() WHERE [Shopping Cart] = 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.

Date() works. Why?

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