Can someone please explain this odd Access SQL syntax?

Friends,

I have two queries in Access 2003 that I use sometimes instead of Autonumber fields. <get_next_number> retrieves the next number for a specific key value, and <set_next_number> updates the next number for a specific key value.

Being curious, I wondered if I could combine the two queries?

Here is my first attempt:

UPDATE (SELECT current_value FROM 90000_next_number WHERE key=[@key]) AS SRC,
90000_next_number AS nn
SET nn.current_value = IIF((nn.current_value Mod nn.maximum_value) = 0, nn.initial_value, ((nn.current_value Mod nn.maximum_value) + nn.step_value))
WHERE nn.key=[@key];

I saved it as <test_query> and Access did not complain. I added a "test" key into table <next_number> and ran the query. It asked for the key, and I supplied "test".

When I checked the table, the current value had been updated. Too cool! (It doesn't take much to make me happy ;-)

However, when I opened the query in design view, Accss had changed the syntax slightly:

UPDATE [SELECT current_value FROM 90000_next_number WHERE key=[@key]]. AS SRC,
90000_next_number AS nn
SET nn.current_value = IIF((nn.current_value Mod nn.maximum_value) = 0, nn.initial_value, ((nn.current_value Mod nn.maximum_value) + nn.step_value))
WHERE nn.key=[@key];

I could not view this odd syntax in the Query Designer (no big deal), but I have never before seen  <[..].> expressed in any query.

So my questions are:

    1. What is this syntax called?
    2. Is it portable to SQL Server or any other database?
    3. Is this a well-known SQL expression?

As always, thanks for taking a look.
LVL 29
BadotzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ldunscombeCommented:
[] represents a field in Access. It is not always required unless the field name contains spaces but as a general rule it's better to use them to be on the safe side.

Your SQL is basically to queries in one with the first query using the results of your second query as a field value hence the []

Leigh
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BadotzAuthor Commented:
I understand how fields are enclosed in [...], but what about the following period and space? What does *that* signify?
0
ldunscombeCommented:
I'm guessing but I would think that it indicates that it should use the Result of the expression (or in this case a sub query) as opposed to the expression itself.

Leigh
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

BadotzAuthor Commented:
I'm not convinced - Access *changed* the original query to this specific syntax, yet I can find *nothing* about it.
0
BadotzAuthor Commented:
OK - here's the skinny (from an enquiry made elsewhere):

=====================
1. What is this syntax called?

Goofy-Access-Jet-SQL-Without-Telling-The-User-SubQuery-Reconstruction

2. Is it portable to SQL Server or any other database?

Not that I know of.

3. Is this a well-known SQL expression?

The only person who knows about it is the little guy sitting inside of the Jet engine. (one of my pet peeves about the Designer) But it is common and there isn't too much of a reason to be alarmed.
=====================

And a bit more clarification from our LPurvis:


=====================
It is indeed (as Walter says) a Jet SQL syntax - it was the preferred subquery syntax before Jet4.
Unfortunately queries are still sometimes parsed back into this older syntax when saved.

FWIW I've found some success (whereby Access actually manages to hang on to the syntax and some formatting) when I've formatted the SQL (as opposed to the common all-in-one-long-line-QBE-generated SQL :-)

But ultimately it is something that has been requested for improvements in future Access versions.

Alas the parsing which can be very helpful (and indeed an inovative means of interpretation from a QBE query representation when you think about how long ago Access was doing this) still defaults to certain old rules.
The plethora of brackets I can forgive (were I constructing a SQL generating functionality I'd bracket it a plenty yoo) but this old subquery syntax will hopefully fall by the wayside soon.
=====================
0
BadotzAuthor Commented:
Thanks for participating.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.