Expr1 in Query 
Author Message
 Expr1 in Query

I am trying to create a query that has a criteria that depends on a
parameter.  My problem is that when I setup my select query and lets say I
have two columns in my query: Orders and ShipCountry, and I want my query to
return all records when
the parameter is blank. The parameter would be "which country". I have my
Orders Column, my Ship Country Column which has [Enter the country] as the
"or" crtieria. I then setup a third column which has the filed name [Enter
the country] and has Is Null as the "criteria" and Is Not Null as the "or"
and is not shown.  My problem is when I add the [Enter the country] to the
field name of the thrid column, Access automatically adds Expr1: in front of
[Enter the country], which looks like Expr1:[Enter the country].  When it
adds the Expr1 to the name the query will not work.  How do I stop Access
from adding this or how do I work around this?

Thanks,

Pete



Mon, 20 Jan 2003 03:00:00 GMT  
 Expr1 in Query
Hrm. Try going into SQL view and editing the WHERE clause to

WHERE [Country] = [Enter the country:] OR ([Enter the country:] IS
NULL)

Not sure how that will come out in the grid; maybe you just need to
uncheck the display checkbox under Expr1.

           John W. Vinson[MVP]    
http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 20 Jan 2003 03:00:00 GMT  
 Expr1 in Query
Tried this but Access keeps adding the "Expr1:" in front of the field.
Any other suggestions.

Thanks,

Pete


Quote:
> Hrm. Try going into SQL view and editing the WHERE clause to

> WHERE [Country] = [Enter the country:] OR ([Enter the country:] IS
> NULL)

> Not sure how that will come out in the grid; maybe you just need to
> uncheck the display checkbox under Expr1.

>            John W. Vinson[MVP]
> http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 20 Jan 2003 03:00:00 GMT  
 Expr1 in Query
The fact that Access is adding "Expr1: " in front of the field is irrelevant
to your problem. This is by design and does not effect the results.

When you use an expression instead of an actual field name in the field name
cell of query design, Access gives the expression an alias name. In SQL,
this looks like:

Select fldFieldName, (3 * 8) AS Expr1, [Your Parameter] as Expr2,
fldOtherField . . . .

In design mode, the field name cells would have this in them:

fldFieldName
Expr1: (3 * 8)
Expr2: [Your Parameter]
fldOtherField

You can change the alias to any text you wish (consistent  with Access
naming rules); just edit the text to the left of the colon in design mode.

I just tested this SQL and it works fine:

SELECT Table2.orders, Table2.ShipCountry, [Enter the country] AS Expr1
FROM Table2
Where [Enter the country] Is Null OR (Table2.ShipCountry =[Enter the
country]  AND [Enter the country] Is Not Null);

Paste this into a new query, SQL view, replacing  "Table2" with your table
name, and see what happens. If it doesn't work as you expect, tell us
exactly how it does not work, i.e., what you typed in response to the prompt
and what your result was.

HTH,
Jon Myers

Quote:

>Tried this but Access keeps adding the "Expr1:" in front of the field.
>Any other suggestions.

>Thanks,

>Pete



>> Hrm. Try going into SQL view and editing the WHERE clause to

>> WHERE [Country] = [Enter the country:] OR ([Enter the country:] IS
>> NULL)

>> Not sure how that will come out in the grid; maybe you just need to
>> uncheck the display checkbox under Expr1.

>>            John W. Vinson[MVP]
>> http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 20 Jan 2003 03:00:00 GMT  
 Expr1 in Query


Quote:
>Tried this but Access keeps adding the "Expr1:" in front of the field.
>Any other suggestions.

So? Let it. You can change Expr1: to anything you want. I don't
understand why this is a problem; it's a calculated field and it needs
an alias name - Access is just giving it one arbitrarily!

           John W. Vinson[MVP]    
http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 20 Jan 2003 03:00:00 GMT  
 Expr1 in Query
I think, and I could be wrong but his query is running to find certain
fields.  If that is the problem then he needs to change that field name to
what he called it in his code, if that is what he is using.  Just highlight
the name and change Exp1: to whatever you want it to say to get your end
result.  Otherwise, I have no clue!

Nina

Quote:


> >Tried this but Access keeps adding the "Expr1:" in front of the field.
> >Any other suggestions.

> So? Let it. You can change Expr1: to anything you want. I don't
> understand why this is a problem; it's a calculated field and it needs
> an alias name - Access is just giving it one arbitrarily!

>            John W. Vinson[MVP]
> http://go.compuserve.com/msdevapps?loc=us&access=public



Thu, 23 Jan 2003 03:00:00 GMT  
 Expr1 in Query

No, you do not have to call the Expr anything. Whatever you want to appear as
the label in the query cell should be followed by a colon, followed by the
expression, i.e., My Calculation: ([fldA]*[fldB])-([fldC]+[fldD])

Quote:

> I think, and I could be wrong but his query is running to find certain
> fields.  If that is the problem then he needs to change that field name to
> what he called it in his code, if that is what he is using.  Just highlight
> the name and change Exp1: to whatever you want it to say to get your end
> result.  Otherwise, I have no clue!

> Nina




> > >Tried this but Access keeps adding the "Expr1:" in front of the field.
> > >Any other suggestions.

> > So? Let it. You can change Expr1: to anything you want. I don't
> > understand why this is a problem; it's a calculated field and it needs
> > an alias name - Access is just giving it one arbitrarily!

> >            John W. Vinson[MVP]
> > http://go.compuserve.com/msdevapps?loc=us&access=public



Thu, 23 Jan 2003 03:00:00 GMT  
 Expr1 in Query

Quote:

>If that is the problem then he needs to change that field name to
>what he called it in his code, if that is what he is using.

Good suggestion, Nina: just overtype Expr1 with whatever name you
want. You listening, PETE?

           John W. Vinson[MVP]    
http://go.compuserve.com/msdevapps?loc=us&access=public



Thu, 23 Jan 2003 03:00:00 GMT  
 Expr1 in Query
I had the same problem when I wanted to combine the value of two
fields to use this in a report.

I solved the problem by overwriting the word EXPR1 by a usefulll name
and now I can use the usefull name in the report.

I hope this can help you.

Regards,

Bij voorbaat dank.

Vriendelijke groeten,

P. Oosterhof.

PETE heeft geschreven in bericht

Quote:
>I am trying to create a query that has a criteria that depends on a
>parameter.  My problem is that when I setup my select query and lets say I
>have two columns in my query: Orders and ShipCountry, and I want my query
to
>return all records when
>the parameter is blank. The parameter would be "which country". I have my
>Orders Column, my Ship Country Column which has [Enter the country] as the
>"or" crtieria. I then setup a third column which has the filed name [Enter
>the country] and has Is Null as the "criteria" and Is Not Null as the "or"
>and is not shown.  My problem is when I add the [Enter the country] to the
>field name of the thrid column, Access automatically adds Expr1: in front
of
>[Enter the country], which looks like Expr1:[Enter the country].  When it
>adds the Expr1 to the name the query will not work.  How do I stop Access
>from adding this or how do I work around this?

>Thanks,

>Pete



Tue, 04 Mar 2003 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Expr1: Trim([Owner_Lastname])

2. Duplicate output alias 'Expr1' continued

3. Duplicate output alias 'Expr1'

4. Expr1 changes column name in query, not in form???

5. "Duplicate output alias 'Expr1'" additional info

6. query of query fails when records in first query filtered

7. Queries of queries with subqueries of queries of....

8. Can queries query queries?

9. Queries Queries Queries!!!!

10. sub queries / nested queries / what queries problem


 
Powered by phpBB® Forum Software © phpBB Group