Criterior in Select Query

[Keith166]

Here's an easy one for you Access experts. I have a Select query from which I wish to extract values either less than or more than Zero.
The two fields generating the value are [Paid] and GF: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) The value field is UO: [GF]-[Paid]
I put <>0 in the criterior field of UO but I get asked for the parameter value of GF ?????
Hope someone can help
Thanks
Keith

 

[namliam]

unless you so query over query you cannot re-use alias inside a query.

Try this:
[Paid] and GF: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) The value field is UO: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) -[Paid]

WHY in heavens name do you have an IIF on your ID?? This seems wrong in so many ways!

 

[Keith166]

Smart Ass

Listen Mailman. If you can't reply to my thread without being insulting then don't bother. And your mumbo jumbo reply is crap and quite worthless to a beginner like me. Thanks for nothing!

 

[namliam]

unless you so query over query you cannot re-use alias inside a query.

Try this:
[Paid] and GF: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) The value field is UO: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) -[Paid]

How is this not helpfull? I even supplied your solution ??

I was in no way beeing insulting (dont think so), sorry if I was... It is just that is highly irregular to have an IIF on an ID (usually Primary Key or Foreign Key) field.

 

[Keith166]

Ok no worries!

OK! I'm a bit twitchy after having worked on this for some time and then I get your reply with the comment "unless you so query over query you cannot re-use alias inside a query" What does that all mean? It looked to me like programmers' speak. I thought this Forum was to assist mug beginners like me. Anyway I entered GF: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) and GF: IIf([Greenfee Payments].[PaymentID]=6,0,24.5) with <>0 as the criterior and it works fine. Please forgive my frustration in not being able to understand language you guys take for granted.

 

[namliam]

The basic idea is you are using an alias column (GF) then you cannot re-use that GF column again in the same query.

Ie.
Test: 1
Now I cannot make a column that says:
Test2: Test + 1
This will generate an error, like you experienced. Instead Test2: 1 + 1 will work.

Also you can save your query that makes the Test column, query1.
Now make a new query, query2, that selects everything from query1. Now Test is a given column and not an Alias anymore. This means in the "query over a query" (query2) you can use Test2: Test + 1

Also my first post contains a typo...
Instead of: unless you so query over query you cannot re-use alias inside a query.
It should read: unless you do query over query you cannot re-use alias inside a query.