Using field reference prevents using criteria

[fhickler]

When designing a query, having a column use a reference to another field works fine until I try and put a criterion on it at which point running the query brings up the "enter parameter value" box.

So this works:

Field	fieldname
Table	tablename
Criteria	>5

And this works:

Field	User Field Name: [tablename.fieldname]
Table
Criteria	>5

And this works:

Field	User Field Name: [tablename.fieldname]	Other User Field Name:[User Field Name] + 1
Table
Criteria

But this doesn't:

Field	User Field Name: [tablename.fieldname]	Other User Field Name:[User Field Name] + 1
Table
Criteria		>5

The actual query I'm making is more complex, with both fields involving formulas and I'd rather not repeat the formula in the second field - I want to just refer to it. I don't understand why this wouldn't work. Doesn't the second field simply represent a value that can have a criterion?

[Micron]

since you didn't post the actual names, we can only guess that you've misspelled one of them - the table or field name you're being prompted for.

[orange]

fhickler,
We only know what you tell us and you have told us nothing about the application/database involved. Your issue is specific to use of or mechanics of the Access query grid (or so it seems).
If you overview your issue in plain English, you may find it more efficient to getting a focused response.
I have a database dealing with X people, Y orders and Z products. I want to find out How many Y(Orders) have more than 4 Products.

Good luck with your project.

[CJ_London]

just guessing perhaps you are grouping the data

[fhickler]

Since nobody seems to understand what I'm saying, I made a simple example and am posting screenshots:





The question is: why doesn't the second criterion work?

[Micron]

I'd say it's because you're using an alias rather than the field name. The calculations shouldn't involve alias names but rather the field names. On my phone so can't verify right now.

[fhickler]

The calculations shouldn't involve alias name

As you can see from the example, the calculation works fine when using an alias name. It's only when I add a criterion that it doesn't work.

[ssanfu]

One thing I never do is use spaces in object names.... just say'n.

Have you tried switching to SQL view and compare the SQL for the queries?
What does the SQL look like for for the queries?

[June7]

You got it.

Calculation referencing another calculated field in same query cannot have filter criteria. Would have to repeat the first calc.

[Micron]

As you can see from the example, the calculation works fine when using an alias name. It's only when I add a criterion that it doesn't work.

BUT you are trying to compare/filter against an unknown. It's like being one level removed. Eliminate the alias and it will likely work

[June7]

Tried with and without alias. Does not work. Access just throws in an alias Expr1 even when the Show box is not checked.

[Micron]

Then the "unknown" isn't the alias - it's the first calculation? That would make sense.

[CJ_London]

why doesn't the second criterion work?

rather than looking at the querygrid, look at the sql it generates. Criteria work on the original source not alias. Your Alias is a calculation. Create a simple query

SELECT fld1*2 as Num
FROM myTable
WHERE fld1*2>10

Note that the alias is not used in the criteria

The workaround is to have two queries the second to use the first

SELECT A.*, Num*2 as Num2
FROM (SELECT *, fld1*2 as Num
FROM myTable) AS A INNER JOIN myTable ON A.ID=myTable.ID
WHERE Num*4>20

[fhickler]

Ok. Thanks all. I obviously have some things to learn about Access, especially SQL. I should train myself to think of the query designer as just a way to generate SQL code rather than an end in itself. It just seemed odd, since the alias resolved to a value, so why not set a criterion on it? A similar thing in excel would work. But, of course, Access != Excel.

The reason I wanted to use the alias name is that in the actual database, is that the formula is more complex, and I wanted to refer to it many times in other columns - I didn't want to have to repeat the formula. What if the formula changed, for example? Maybe a VBA function is in order (another area I need to improve on). Or I can just export the query to excel and do the calculation there.

[CJ_London]

The reason I wanted to use the alias name is that in the actual database, is that the formula is more complex, and I wanted to refer to it many times in other columns - I didn't want to have to repeat the formula. What if the formula changed, for example?

or do as I suggested in post #13 - a technique I often use for these situations. VBA will probably be slower and exporting to Excel will probably be slower as part of an overall process (time required to export, open excel etc).

Access != Excel

agreed, but it is more like

Excel != database (of any sort)