Why does Access do this?

[Peter James]

Access 97
If I select New on the Query tab of the db window, and go staight to sql
view and type in the following for example:

INSERT INTO tblMyTable ( dtDate, txtAny)
VALUES (#2003-09-03#, 'blah');

and then save the query, close it, and then reopen by clicking the design
button so it opens in sql view. The sql has changed to:

INSERT INTO tblMyTable ( dtDate, txtAny)
SELECT #2003-09-03#, 'blah';

Both Access Help and ADH97 confirm that the _before saved_ version is the
correct syntax, and the query does what is expected of it in both forms, so
it isn't a life threatening situation. However, idle curiosity drives me to
ask the question in the subject line.

Anyone got any thoughts on the subject?

Cheers

Peter

[Marshall Barton]

Peter James wrote:

Access 97
If I select New on the Query tab of the db window, and go staight to sql
view and type in the following for example:

INSERT INTO tblMyTable ( dtDate, txtAny)
VALUES (#2003-09-03#, 'blah');

and then save the query, close it, and then reopen by clicking the design
button so it opens in sql view. The sql has changed to:

INSERT INTO tblMyTable ( dtDate, txtAny)
SELECT #2003-09-03#, 'blah';

Both Access Help and ADH97 confirm that the _before saved_ version is the
correct syntax, and the query does what is expected of it in both forms, so
it isn't a life threatening situation. However, idle curiosity drives me to
ask the question in the subject line.

Who knows? My guess is that it's Access' canonical form for
inserting constant values. The use of an illegal form of
the Select statement is kind of intriguing though!?

BTW, somewhere in Help there is a warning that they will do
this and that you should not worry about it.

--
Marsh

[Fletcher Arnold]

"Peter James" <ow****@yahoo.co.uk> wrote in message
news:bj**********@codas.jet.uk...

Access 97
If I select New on the Query tab of the db window, and go staight to sql
view and type in the following for example:

INSERT INTO tblMyTable ( dtDate, txtAny)
VALUES (#2003-09-03#, 'blah');

and then save the query, close it, and then reopen by clicking the design
button so it opens in sql view. The sql has changed to:

INSERT INTO tblMyTable ( dtDate, txtAny)
SELECT #2003-09-03#, 'blah';

Both Access Help and ADH97 confirm that the _before saved_ version is the
correct syntax, and the query does what is expected of it in both forms, so it isn't a life threatening situation. However, idle curiosity drives me to ask the question in the subject line.

Anyone got any thoughts on the subject?

Cheers

Peter

It can be even more frustrating when you have very long and complex queries
which (in order to make some sense out of them) you lay out in a legible
fashion with spaces, carriage returns, etc. Not only is all formatting
lost, but unnecessary extra stuff is often written in - such as prefixing
the field with the table name.

Where this is important, I save the SQL to a text file, edit it in Notepad
and paste it into the Access query.

Fletcher

[Trevor Best]

On Wed, 3 Sep 2003 12:14:46 +0100 in comp.databases.ms-access, "Peter
James" <ow****@yahoo.co.uk> wrote:

Access 97
If I select New on the Query tab of the db window, and go staight to sql
view and type in the following for example:

INSERT INTO tblMyTable ( dtDate, txtAny)
VALUES (#2003-09-03#, 'blah');

and then save the query, close it, and then reopen by clicking the design
button so it opens in sql view. The sql has changed to:

INSERT INTO tblMyTable ( dtDate, txtAny)
SELECT #2003-09-03#, 'blah';

Both Access Help and ADH97 confirm that the _before saved_ version is the
correct syntax, and the query does what is expected of it in both forms, so
it isn't a life threatening situation. However, idle curiosity drives me to
ask the question in the subject line.

Anyone got any thoughts on the subject?

One of the fundamental differences I find between writing queries in
Access and views in SQL Server is that Access nicely remembers the
positions, sizes, etc of the tables above the query grid, SQL Server
doesn't.

I know in SQL Server it saves the SQL text as the view and builds the
tables & joins diagram on the fly.

I think (and I may be wrong as I don't know the internals of it) that
Access does the opposite and stores all those attributes, table
positions, etc for the diagram and generates the SQL Text on the fly.

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

[TC]

Surely #2003-09-03# has got to be wrong?

#...# date constants must be expressed in American (month/day/year) order,
regardless of the system locale.

TC

"Peter James" <ow****@yahoo.co.uk> wrote in message
news:bj**********@codas.jet.uk...

Access 97
If I select New on the Query tab of the db window, and go staight to sql
view and type in the following for example:

INSERT INTO tblMyTable ( dtDate, txtAny)
VALUES (#2003-09-03#, 'blah');

and then save the query, close it, and then reopen by clicking the design
button so it opens in sql view. The sql has changed to:

INSERT INTO tblMyTable ( dtDate, txtAny)
SELECT #2003-09-03#, 'blah';

Both Access Help and ADH97 confirm that the _before saved_ version is the
correct syntax, and the query does what is expected of it in both forms, so it isn't a life threatening situation. However, idle curiosity drives me to ask the question in the subject line.

Anyone got any thoughts on the subject?

Cheers

Peter

[Peter James]

Thanks to everyone for your responses. I guess we'll have to call it a
hidden <feature> ;-)

Peter

[Steve Jorgensen]

On Thu, 4 Sep 2003 07:49:06 +0100, "Peter James" <ow****@yahoo.co.uk>
wrote:

"TC" <a@b.c.d> wrote in message news:1062645079.633666@teuthos...

Surely #2003-09-03# has got to be wrong?

#...# date constants must be expressed in American (month/day/year) order,
regardless of the system locale.

TC

Run the query and then look in the table and you'll find the date stored as
03/09/2003 (if you're the UK) -- yyyy-mm-dd is an acceptable input format,
and also works in SQL Server I believe.

That's not what he meant. The date isn't actually stored as any format,
it's stored as a floating point number. It's just that Access normally
insists on converting any date expressions in queries into USA format.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Peter James]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:p0********************************@4ax.com...

"TC" <a@b.c.d> wrote in message news:1062645079.633666@teuthos...

Surely #2003-09-03# has got to be wrong?

#...# date constants must be expressed in American (month/day/year) order, regardless of the system locale.

TC

That's not what he meant. The date isn't actually stored as any format,
it's stored as a floating point number. It's just that Access normally
insists on converting any date expressions in queries into USA format.

- Steve Jorgensen

I know that dates are stored as doubles, but you don't input a double. The
yyyy-mm--dd format is unambiguous.

Well here it is from a far more knowledgeable person than I:

http://groups.google.com/groups?dq=&...8&selm=uLm3ptW
cDHA.3872%40TK2MSFTNGP11.phx.gbl

Apologies for word wrap.

[Steve Jorgensen]

On Thu, 4 Sep 2003 12:47:33 +0100, "Peter James" <ow****@yahoo.co.uk>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:p0********************************@4ax.com.. .

>"TC" <a@b.c.d> wrote in message news:1062645079.633666@teuthos...
>>
>> Surely #2003-09-03# has got to be wrong?
>>
>> #...# date constants must be expressed in American (month/day/year)order, >> regardless of the system locale.
>>
>
>TC

That's not what he meant. The date isn't actually stored as any format,
it's stored as a floating point number. It's just that Access normally
insists on converting any date expressions in queries into USA format.

- Steve Jorgensen

I know that dates are stored as doubles, but you don't input a double. The
yyyy-mm--dd format is unambiguous.

Well here it is from a far more knowledgeable person than I:

http://groups.google.com/groups?dq=&...8&selm=uLm3ptW
cDHA.3872%40TK2MSFTNGP11.phx.gbl

Apologies for word wrap.

No argument with that information, but when I'm in the query editor, and I
type a date in yyy-mm-dd format (and yes, I do line that format because it
is unambiguous), Access rewrites it as #mm/dd/yy#. I'm wondering why it
does not mangle your query in the same way.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[David W. Fenton]

ow****@yahoo.co.uk (Peter James) wrote in
<bj**********@codas.jet.uk>:

Access 97
If I select New on the Query tab of the db window, and go staight
to sql view and type in the following for example:

INSERT INTO tblMyTable ( dtDate, txtAny)
VALUES (#2003-09-03#, 'blah');

and then save the query, close it, and then reopen by clicking the
design button so it opens in sql view. The sql has changed to:

INSERT INTO tblMyTable ( dtDate, txtAny)
SELECT #2003-09-03#, 'blah';

Both Access Help and ADH97 confirm that the _before saved_ version
is the correct syntax, . . .
"Correct syntax?"

Where, exactly, do you get this.

VALUES is something that Jet SQL supports, but I don't believe it's
native Jet SQL.
. . . and the query does what is expected of it
in both forms, so it isn't a life threatening situation. However,
idle curiosity drives me to ask the question in the subject line.

Why?

Because Access is getting parsing the query for the Jet query
optimizer, which probably expects SQL in a certain format. While I
see no reason why the Jet query optimizer could not accept both
forms, apparently it does not, or it did not at the time the Access
QBE interface was designed.

Why worry about it?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Thu, 4 Sep 2003 16:14:57 +0100, "Peter James" <ow****@yahoo.co.uk>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:9b********************************@4ax.com.. .

No argument with that information, but when I'm in the query editor, and I
type a date in yyy-mm-dd format (and yes, I do line that format because it
is unambiguous), Access rewrites it as #mm/dd/yy#. I'm wondering why it
does not mangle your query in the same way.

- Steve Jorgensen

Are you in the USA? If so Access won't have any regional settings to cope
with because the default is US format, and perhaps that is why the date
constant in your query gets changed. Just a guess.

Jet requires US format no matter where one is in the world, but Access uses
the regional settings to accept and display local date formats and does the
conversion to/from US format when sending/retrieving data to/from Jet.

There's a thread entitled regional differences over in m.p.a.queries that
might be of interest.

I see we're still not communicating. In previous threads on date values in
queries, it has come up that Access was rewriting date constant strings to
USA format no matter how you type them in. You could type an ISO date into
an expression, hit tab, and it got converted to USA format. This had
nothing to do with default settings for forms, reports, datasheet view, or
anything else. It was in the query editor only.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

[Peter James]

David

Responses in line.
"David W. Fenton" <dX********@bway.net> wrote

"Correct syntax?"

Where, exactly, do you get this. Access Help>>SQL Reference>>INSERT INTO
and Access97 Developers Handbook pages 176-177.
VALUES is something that Jet SQL supports, but I don't believe it's
native Jet SQL. Huh?

. . . and the query does what is expected of it
in both forms, so it isn't a life threatening situation. However,
idle curiosity drives me to ask the question in the subject line.

Why?

Because Access is getting parsing the query for the Jet query
optimizer, which probably expects SQL in a certain format. While I
see no reason why the Jet query optimizer could not accept both
forms, apparently it does not, or it did not at the time the Access
QBE interface was designed.

So, why to Microsoft and Messrs Getz, Litwin, Gilbert tell us otherwise?
That's a rhetorical question.
Why worry about it?

I don't.

Subject closed.

Peter

[Peter James]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ep********************************@4ax.com...

I see we're still not communicating.

So it would seem. Date formats other than US work for me. What more can I
say?

Peter

[David W. Fenton]

ow****@yahoo.co.uk (Peter James) wrote in
<bj**********@codas.jet.uk>:

"David W. Fenton" <dX********@bway.net> wrote

"Correct syntax?"

Where, exactly, do you get this.
Access Help>>SQL Reference>>INSERT INTO

You mean:

Access Help>>SQL>>INSERT INTO

There is no listing for "SQL Reference".
and Access97 Developers Handbook pages 176-177.

That's an almost verbatim quotation from the help file, so I don't
see it as an independent source.

VALUES is something that Jet SQL supports, but I don't believe
it's native Jet SQL.

Huh?

Often when multiple dialects for doing something are supported, one
is native and the other is ported behind the scenes to the native
version. So far as I can tell, the VALUES() method is not the
native method for Jet SQL, otherwise, it would be left alone by the
QBE SQL parser.

But I'm only guessing here.

> . . . and the query does what is expected of it
>in both forms, so it isn't a life threatening situation.
>However, idle curiosity drives me to ask the question in the
>subject line.

Why?

Because Access is getting parsing the query for the Jet query
optimizer, which probably expects SQL in a certain format. While
I see no reason why the Jet query optimizer could not accept
both forms, apparently it does not, or it did not at the time
the Access QBE interface was designed.

So, why to Microsoft and Messrs Getz, Litwin, Gilbert tell us
otherwise? That's a rhetorical question.

Do you really think that everything that appears in published
reference works on complex software like Access has been 100%
independently verified by the authors? Much of what they do is
rework the existing documentation and try things out for
themselves. I'm pretty certain the reason that there is nothing in
the ADH about SELECT value1, value2, value3 in place of
VALUES(value1, value2, value3) is because the authors simply
confirmed that the syntax in the help file worked and did not
investigate if there were other syntaxes that worked.

Also, keep in mind that the change occurs in a saved query. How
often do you save an insert query with hardwired values for the
inserted row? The VALUES() syntax is very clear for writing SQL in
code and it works. The fact that it gets changed to the SELECT
syntax really doesn't matter much.

Why worry about it?

I don't.

Subject closed.

Seems reasonable to me!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc