add criteria to field that is based on named field

[Guest]

Sorry if this posting shows up twice, I think my first post attempt crashed...
I think I am missing something fundamental but I do not know what. I don't
know what this issue could potentially be called so searching forums has not
been helpful.

Setup
1.Start new select query, add any table so that query can be run
2.setup the following
----------------|---------------|---------------
|field1 |field2
----------------|---------------|---------------
field |Expr1: 1+1 |Expr2: [Expr1]
Criteria | |
----------------|---------------|---------------
3.Run (works fine)

4.Change setup to the following by adding criteria
----------------|---------------|---------------
|field1 |field2
----------------|---------------|---------------
field |Expr1: 1+1 |Expr2: [Expr1]
Criteria | |2
----------------|---------------|---------------
5.Run (get a popup window "Enter Parameter Value" for [Expr1]

I would like to understand why this is not working and what I can do to fix
it. From my perspective, this seems like it is legal to do this. Are there
any work-arounds or
should I be thinking about the situation from a different angle. Thank you
in advance.

 

[Guest]

1. Create a new query based on the first query that as the field Expr1: 1+1,
and in the new query add the criteria

Or

2. Write the criteia directly under Expr1, without creating Expr2

 

[Guest]

Ofer,

Thank you for your prompt reply. Those are the 2 solutions that I came up
with also. Drawback for solution 1 is having multiple queries, and for 2 is
that expressions would have to be maintained in multiple fields, rather than
just one "variable" field that other fields look at. These expressions could
also get pretty long. Do you or anyone else happen to know if this is the way
access was designed or are there any hot fixes for this? This is more just
curiousity at this point, I think I have to decide on one of the 2 solutions.
Thanks again.

1. Create a new query based on the first query that as the field Expr1: 1+1,
and in the new query add the criteria

Or

2. Write the criteia directly under Expr1, without creating Expr2



--
Good Luck
BS"D

Sorry if this posting shows up twice, I think my first post attempt crashed...
I think I am missing something fundamental but I do not know what. I don't
know what this issue could potentially be called so searching forums has not
been helpful.

Setup
1.Start new select query, add any table so that query can be run
2.setup the following
----------------|---------------|---------------
|field1 |field2
----------------|---------------|---------------
field |Expr1: 1+1 |Expr2: [Expr1]
Criteria | |
----------------|---------------|---------------
3.Run (works fine)

4.Change setup to the following by adding criteria
----------------|---------------|---------------
|field1 |field2
----------------|---------------|---------------
field |Expr1: 1+1 |Expr2: [Expr1]
Criteria | |2
----------------|---------------|---------------
5.Run (get a popup window "Enter Parameter Value" for [Expr1]

I would like to understand why this is not working and what I can do to fix
it. From my perspective, this seems like it is legal to do this. Are there
any work-arounds or
should I be thinking about the situation from a different angle. Thank you
in advance.

 

[Gary Walter]

Hi Mike,

It has to do with the order that the query is parsed.

http://groups.google.com/group/comp.databases.ms-sqlserver/msg/0c20229366261b8d

A typical MS Access query parses in this order:
{this order (and behavior of aliases) is different than SQL Server}

first the FROM clause (JOIN(s))

then the WHERE clause

then GROUP BY clause (if exists)

then HAVING clause (if exists)

then (FINALLY) the SELECT clause...

i.e., any ALIAS in SELECT clause does not yet "exist"
when parsing the WHERE clause

I don't know if this will help your situation or not,
but it may be possible to use a subquery in the
FROM clause that introduces the expression within
the subquery.

But subqueries in FROM clause are "fickle" in that
you cannot have any further brackets within them.

hth,

gary


"MikeR"wrote:

<snip>
Do you or anyone else happen to know if this is the way
access was designed or are there any hot fixes for this? This is more just
curiousity at this point, I think I have to decide on one of the 2
solutions.
Thanks again.

1. Create a new query based on the first query that as the field Expr1:
1+1,
and in the new query add the criteria

Or

2. Write the criteia directly under Expr1, without creating Expr2



--
Good Luck
BS"D

Sorry if this posting shows up twice, I think my first post attempt
crashed...
I think I am missing something fundamental but I do not know what. I
don't
know what this issue could potentially be called so searching forums
has not
been helpful.

Setup
1.Start new select query, add any table so that query can be run
2.setup the following
----------------|---------------|---------------
|field1 |field2
----------------|---------------|---------------
field |Expr1: 1+1 |Expr2: [Expr1]
Criteria | |
----------------|---------------|---------------
3.Run (works fine)

4.Change setup to the following by adding criteria
----------------|---------------|---------------
|field1 |field2
----------------|---------------|---------------
field |Expr1: 1+1 |Expr2: [Expr1]
Criteria | |2
----------------|---------------|---------------
5.Run (get a popup window "Enter Parameter Value" for [Expr1]

I would like to understand why this is not working and what I can do to
fix
it. From my perspective, this seems like it is legal to do this. Are
there
any work-arounds or
should I be thinking about the situation from a different angle. Thank
you
in advance.

 

[Jamie Collins]

subqueries in FROM clause are "fickle" in that
you cannot have any further brackets within them.

Such a subquery is called a 'derived table' in standard SQL. FWIW the
problem with brackets was fixed in ACE (version of Jet in Access 2007,
offically named 'Access database engine') e.g. to demonstrate the
difference between Jet 4.0 and ACE:

Sub QueryBrackets()
Dim counter As Long
For counter = 0 To 1
Kill Environ$("temp") & _
Array("\DropMe.mdb", "\DropMe.accdb")(counter)
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
Array("Provider=Microsoft.Jet.OLEDB.4.0;", _
"Provider=Microsoft.ACE.OLEDB.12.0;")(counter) & _
"Data Source=" & _
Environ$("temp") & _
Array("\DropMe.mdb", "\DropMe.accdb")(counter)
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Test (col1 INTEGER);"
.Execute sql
sql = _
"INSERT INTO Test VALUES (0);"
.Execute sql
sql = _
"CREATE VIEW TestView" & vbCr & "AS " & vbCr & "SELECT" & _
" [name has spaces]" & vbCr & "FROM (" & vbCr & "SELECT" & _
" DISTINCT 1 AS [name has spaces]" & vbCr & "FROM" & _
" Test) AS DT1;"
.Execute sql
Dim rs
Set rs = .OpenSchema( _
adSchemaViews)
MsgBox rs.Fields(3).value
End With
Set .ActiveConnection = Nothing
End With
Next
End Sub

Jamie.

--