Re: Calculate days between 2 dates ignoring weekends?

On 11 Sep, 15:02, Klatuu <Kla...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Try not to get your feathers up and have a wonderful day.

I'll take your good advice :) Have a good one yourself...

You and Microsoft are
overly optimistic about ansi 92 compliance. For example, try to create a
temporary table using DDL with Jet. It doesn't work. Or try SELECT %
FROM... OOOPs! Syntax error. There are other issues I have run accross, but
can't remember them at the moment.

Access 2007 Help: SQL reference
CREATE TABLE Statement
http://office.microsoft.com/en-gb/access/HA012314411033.aspx

[Quote]
Syntax

CREATE [TEMPORARY] TABLE table (...
[Unquote]

Yes that's SQL-92 syntax but Jet doesn't support it! AFAIK this is
merely poor editorship. And it's not the only 'fabricated' feature on
this page e.g.

"You can use NOT NULL ... within a named CONSTRAINT clause that
applies ... to a multiple-field named CONSTRAINT." Erm, no you can't!

Also, why is the DEFAULT clause not mentioned here?!

And it's not the only page with such errors e.g.

CONSTRAINT Clause
http://office.microsoft.com/en-gb/access/HA012314371033.aspx

" NOT NULL (notnull1[, notnull2 [, ...]])" -- no (again!)
"[ON UPDATE ... SET NULL]" -- no, that's not supported.
Also, where are CHECK constraints?!

OK, then, so there's some shockingly bad technical authorship
throughout the Jet SQL Help, especially considering it's been like
this for two releases now (I've given feedback to Microsoft, I've told
Albert and Larry, what else can a mere mortal do <g>?)

Dunno what you mean by SELECT %. If you mean the MOD operator, it was
omitted from SQL-92 by design (probably because there were too many
and varied implementations already in existence). If you mean the
wildcard, I've recently been advised that using ALIKE with the '%' and
'_' wildcards in Jet yields the same behaviour in both Query Modes so
I hope everyone would be happy with this.

Bloopers aside, what's the issue with ANSI SQL? Microsoft do NOT claim
entry level SQL-92 for Jet; I refute their claim that it is 'near'
entry level because there are some serious omissions. Most notable,
IMO, is the UPDATE syntax which should be:

UPDATE table
SET column1 = (<scalar subquery>)

For example, this works fine:

UPDATE Employees
SET has_sales = 1
WHERE EXISTS
(
SELECT *
FROM Orders AS O1
WHERE Employees.EmployeeID = O1.EmployeeID
);

....but this does not:

UPDATE Employees
SET has_sales = IIF(EXISTS
(
SELECT *
FROM Orders AS O1
WHERE Employees.EmployeeID = O1.EmployeeID
), 1, 0)

The failure message is the oddly applied, "Operation must use an
updateable query." Mind you, they use the same message to mean, "We
intentionally crippled updating Excel using Jet SQL in Access" ;-)
We're supposed to use a JOIN syntax which gives unpredictable results
but that's another story...

Yes, I would like entry level SQL-92 compliance for Jet. Yes, I would
like full SQL-92 compliance for Jet. Yes, I would like a truly
relational (e.g. D compliant) language for Jet. And yes, none of these
wishes will ever come to fruition: Jet is currently in maintenance
mode and anyhow is too badly flawed... erm, I mean, has a distinctive
design :)

For me, the bottom line is that ANSI-92 Query Mode is richer than
ANSI-89 ('traditional') Query Mode, making it more suited to newsgroup
posts e.g. easier say CREATE VIEW than to say, "In the Access user
interface, create a Query object whose SQL View comprises a non-
parameterized SELECT query that returns a resultset..." etc. You would
try to deny its existence at your peril; rather, you should IMO code
for it even if you don't personally use it e.g. avoid ANSI-89
('traditional') Query Mode-only wildcard characters otherwise
*someone* could use ANSI-92 Query Mode to insert those characters as
literals.

It may look like a stored procedure, but as Access
is a file server application and all processing is done on th workstation, I
don't believe it qualifies as a true Stored Procedure.

And for the very same reasons some people say that Jet SQL may look
like the SQL language but do not believe it qualifies as a true SQL
implementation (and in turn some do not believe SQL DBMS qualifies as
relational technology); the weapons of choice here are the DISTINCTROW
qualifier and the FIRST and LAST set <sniggers> functions. If Jet
isn't a SQL DBMS then can the last person please turn off the
lights...

I understand where you are coming from in that the 'stored procedure'
moniker is a little controversial (I usually avoid it in favour of SQL
PROCEDURE) but there is no denying it, it is what it is: a persisted
SQL object (better IMO for ten applications to call a shared proc than
to have ten local implementations of the same logic using ad hoc SQL)
whose resultset (if it returns one) cannot be used as a table (i.e.
cannot be JOINed to another table, cannot be operated on using INSERT/
UPDATE/DELETE), giving us a distinction between a proc and a VIEW
(viewed table) with increased ability to manage permissions: removing
update privileges from base tables, hiding and deriving columns in
VIEWS, managing CRUD operations via SQL procs. Better practice which
leads naturally to easier maintenance, IMO.

Jamie.

--


.