Re: Calculate days between 2 dates ignoring weekends?

I have reviewed the articles you posted and there is something there to
learn; however, I do take a couple of exceptions. 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.

As to stored procedures, 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.

English is my only language and I am pretty good at it. I see you examples,
but as I have previously stated, you knowledge of SQL is exceptioinal;
howver, the OP can be confused by generic answers.

I consider you knowledgable and intelligent. We do have differences in our
approach. I have never said you are wrong about anything. I consider our
discussions more a philosophical exchange.

Try not to get your feathers up and have a wonderful day.
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On 10 Sep, 16:02, Klatuu <Kla...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
The majority of your posts are based on SQL server.

I would like to be sensitive here because I'm conscious that English
might not be your first language. However, I must point out that
"majority" means "better than 50%". On this basis, I emphatically
reject your claim. I sometimes compare Jet to SQL Server as an aside
to add (IMO) interest; if you are not interested, it's your right to
ignore my asides.

Rather than me
forwarding them, read them.

You want me to do the work? Why am I not surprised <g>? OK then,
here's a review of my last ten posts:

1) SCALING OF DECIMAL VALUE RESULTED IN DATA TRUNCATION
http://groups.google.co.uk/group/microsoft.public.access/msg/edeb6a7de1caa190

JamieC on Jet: "If this were Jet... when trying to force a value with
excess
scale into a Jet column of type DECIMAL... For example, Jet DDL... Jet
does error when the integer portion won't fit..."

JamieC on SQL Server: [None].
--

2) Database Design and Relationships
http://groups.google.co.uk/group/microsoft.public.access.tablesdbdesign/msg/5e2cd1d962ff58f5

JamieC on Jet: [None].

JamieC on SQL Server: [None].

[Note: I described a design principle here].

--

3) table with no primary key
http://groups.google.co.uk/group/microsoft.public.access.tablesdbdesign/msg/56e7f741298d4b52

JamieC on Jet: "...because Jet is a SQL engine... For Jet, PRIMARY KEY
designation determines physical ordering on disk... bold text in the
Relationships window in the Access
user interface..."

JamieC on SQL Server: [a link to a SQL Server MVP's blog but no
explicit mention].
--

4) Parameter query in which you can search for any word
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/13a896422923f642

JamieC on Jet: "Better to be ANSI mode neutral: ALike '%' & [Your
Parameter] & '%'
[note: ANSI Query Modes and the ALike operator are exclusive to Jet].

JamieC on SQL Server: [None].
--

5) Creating a complicated query
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/2e8e11f9b706aede

JamieC on Jet: "Here's a simple example using northwind to divide
employees into three
'classes' using the MOD operator" [Note SQL Server has no MOD
operator].

JamieC on SQL Server: [None].
--

JamieC on Jet: "replace 'Date()' with 'Now()'..."
[Note SQL Server uses getdate() and CURRENT_TIMESTAMP];
".. DATEADD('M', DATEDIFF('M',..."
[Note the quotes around 'M' would cause an error on SQL Server].
"...#1990-05-01 00:00:00#..."
[Note the # delimiters would cause an error in SQL Server; the
equivalent 'safe' SQL Server format would be '1990-05-01T00:00:00.000'
which would in turn error on Jet].

JamieC on SQL Server: [None].
--

6) If... Else Statement in SQL
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/bed588027357663a

JamieC on Jet:
"SELECT ID, Product, Trading_ID, SWITCH (..."
[Note SQL Server has no SWITCH() expression].

JamieC on SQL Server: [None].
--

7) have query based from a left join, now need to exclude records....
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/7467c6f4594b39c5

JamieC on Jet: [None].

JamieC on SQL Server: [None].

[Note: I posted a 'vanilla' WHERE clause here].
--


8) how would I write this criteria for a query?
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/ecc8a59d7e0b338c

JamieC on Jet: "...use IsNull([Date Approve])..."
[Note SQL Server has no IsNull() expression].

JamieC on SQL Server: [None].
--

9) Sort Last 4 Numbers of SSN
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/5b8a57a19dc7108d

JamieC on Jet:
"...use a stored proc (Access Query object with a PARAMETERS
clause)..." [Note SQL Server has no PARAMETERS clause]
"...Example using ANSI-92 Query Mode syntax SQL DDL..."
[Note ANSI Query Modes are exclusive to Jet].
"...CREATE PROCEDURE GetMember (arg_EIN CHAR(8) = NULL,..."
[Note SQL Server parameter names must be prefixed with the commercial
at ('@') character].

JamieC on SQL Server: [None].
--

10) What function will return the most recent entry date for a record?
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/c41c0cc07416d7c6

JamieC on Jet: ...SELECT ID, Date1 AS result1, IIF(Date2 > result1...
[Note SQL Server has no IIF() expression].

JamieC on SQL Server: [None].
--

SQL (the language) is supported in Jet

Not fully. No stored procs in Jet, for example.

Get with the 1990's ROFL! See:

Microsoft Office Access 2003 Help
CREATE PROCEDURE Statement
http://office.microsoft.com/en-gb/access/HP010322191033.aspx
"Creates a stored procedure ."

Seriously, take my advice: read the following two articles:

Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Advanced Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

Having read them, you may be better placed to spot Jet SQL syntax in
the 'groups.

Please also see:

Microsoft Communities Home
Rules of Conduct
http://www.microsoft.com/communities/conduct/default.mspx

"Please avoid personal attacks [and] slurs"

Note I'm not the only regular in the 'groups who has recommended using
a Calendar table:

Allen Browne Access MVP e.g.
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/a126329ced95ef8
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/a96e9cc47c54fbb7

Ken Sheridan e.g.
http://groups.google.co.uk/group/microsoft.public.access.gettingstarted/msg/7d2c5e8827905e0a

Michael Gramelspacher e.g.
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/ac8f99fc86e7c26d

Gary Walter e.g.
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/e8512b21da59dd71

Bob Quintal e.g.
http://groups.google.co.uk/group/microsoft.public.access/msg/c413dc7013a44b40

Just to be clear, I am NOT urging you to give these people a hard
time; I'm asking you to cease posting your spurious "client server"
"SQL Server" responses to me simply because a calendar table is not
your style.

Dave, you're an MVP now. You are expected to set a good example rather
than lower the tone.

Jamie.

--



.

Loading