Help with Nested Query syntax

  • Thread starter Thread starter A Coyle
  • Start date Start date
A

A Coyle

Hi
I have converted a 2000 access database to 97 and have had
trouble with a nested SQL statement.
I could not get the converted database to work so I had to
import everything into a blank 97 database (when I opened
it i got a message about converting to my version of VB,
but the code would not compile and I could not view the
references).

The statement was along the lines of (greatly simplified):
select OrderNumber from (SELECT * from jobs)

I get an error in the from clause. I get this error if I
copy and paste the above into a blank query as well as
trying to open a recordset on it (which is how I was
running it on my 2000).
Is this syntax not valid in 97? What is the valid syntax
for nesting queries like this?


I have also had trouble with the following in my VB:

me.<somecontrolName>
I had to convert to me!<somecontrolName>

dim buttons as vbMessageBoxStyle
caused an error, i had to replace with dim buttons as
integer

I was wondering if there was a reference issue that I
should be looking for, and if someone could point me to
where I can get info on this.

Thanks in advance
A Coyle
 
The expression:

select OrderNumber from (SELECT * from jobs)

looks to me like you are naming a table "(SELECT * from jobs)".

Is something missing?

Good luck

Jeff Boyce
<Access MVP>
 
???
Thanks for replying Jeff, but I dont quite understand what you mean..

Perhaps I should rewrite:

In Access 97 I would like to create a query in the following format

SELECT <various items and expressions etc here> FROM
(SELECT <various items and expresions here, WHERE clause here)

I have this working in A2000, it does not work in my converted database.


The example I gave in my initial post was a greatly simplified, and plain
silly, example just to demonstrate. But the overall point remains that this
exact silly example syntax works in A2000 but not A97. The nested SELECT
clause used in my application is not a simple table or query return, it is a
'complicated' SQL statement that is created on the fly depending on user
applied filters and other things.


Thanks in advance
A
 
?What happens if you:
1. open the query in A97, in design mode, select the SQL view
2. copy it
3. close A97 and open A2K
4. create a new query (empty), and select SQL view
5. paste
6. run it.

And are you certain you had compacted & repaired the A97 version before
converting? ... and the new version after ...?

I'm not there so these are just some thoughts.

Jeff Boyce
<Access MVP>
 
Runs no problems.
As an aside I will be running this SQL string from VB, I generate it on the
fly.

Here is a good example of the issue I am having:
..Create a table called t_table1.
..Give it 1 field, called myField
..Close
..Open query in design, select SQL view
..Type the following:
..SELECT myField from (SELECT myField from t_table1)

Run under A2000 = OK
Run under A97 = not OK (error in From clause)

So it looks like either
- A97 does not like nested select statements
- A97 does like nested select statements and I have the syntax wrong

If its the first (and I really hope it isnt) then I will have to rework how
I am doing things, possibly creating a dummy query def for the sub-select
statement which I later delete again. If it is the later can someone tell
me what I need to add to the syntax

Thanks
A
 
Best of luck -- I've run through possibles I'd be checking. Given the
length of our thread, consider reposting, to see if one of the other 'group
readers can help.

Jeff Boyce
<Access MVP>
 
In Access97 you can use, in some cases, nested queries. There are limitations.

SELECT A.myField from [SELECT myField from t_table1]. As A

Note: The SQUARE Brackets around the nested query and the period after and the Alias.

-- no brackets within the brackets of the nested query and therefore no spaces
or special characters in field and table names. This syntax might also work in
2000 (you would be the tester).
 
Back
Top