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?
 
ldunscombeConnect With a Mentor Commented:
[] 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
 
BadotzAuthor Commented:
I understand how fields are enclosed in [...], but what about the following period and space? What does *that* signify?
0
 
ldunscombeConnect With a Mentor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
All Courses

From novice to tech pro — start learning today.