Enter Parameter Value

[PaulJL]

I have designed a query that has two calculated fields "expr1 and Expr2"
Expr2 is a formula
IIF(Expr1*constant>1,"True","false").
However every time i try to limit the display to show only the true rows it asks me to Enter the Parameter value "Expr1" IF i do not try to limit the result then the query shows both true and false rows.
What am i doing wrong? I have only started learning Access 2 days ago so please go easy on me!

 

[Bariloche]

PaulJL,

What you are experiencing is the result of "timing." That is to say, at the time you run your query the results of your calculations don't exist yet, so Access has nothing to compare your criterion to and so it asks you to provide what it doesn't know (the result of Expr1). The way around this is to create a new query that is based on the query in which you calculate your expressions. You'll then use this second query to get your data.

Also, you should rename the Expr1 and Expr2 fields to something more meaningful. In Access (and SQL in general) its called "creating an alias." In other words, Expr2 should be something like:

NewSalesRecord: IIF(Expr1*constant>1,"True","False")

Of course "Expr1" will need to be referred to by its alias also.

You're best off in Access if you run your field names together, like I've done with "NewSalesRecord." This makes it easier to reference.

have fun

 

[PaulJL]

Brilliant, but now when i try and filter on the new "sub" query "test" feild that i have created i get a message telling me i have got a Data Type Mismatch Error. I assume that this means that the properties of the new field in the sub query are different from the properties of the field it is trying to filter off. Am i right and if so how do i get the properties of these calculated fields to match

 

[Bariloche]

Paul,

Without seeing some sample data its hard to tell. One thing though, do you have "True" or "False" in quotes in the criteria row of your "sub" query? Check that first, that might be the problem.

 

[PaulJL]

Yes i was using true and false but also tried using 1 and 0 and that did not work either. I gave up in the end and split the database up into 4 chunks and put them into excel to look at the data as i needed the answers rather sharpish. I believe when the database was designed one of fields was set as text and it needed to be set to number but i couldn't change the field setting due to lack of memory and disk space. Thanks for the help as i'm sure i will need more as i attempt to make access do stuff that i know in excel

 

[Anne Troy]

Bariloche:

Thanks for this:

>You're best off in Access if you run your
>field names together, like I've done
>with "NewSalesRecord." This makes it easier
>to reference.

I rarely hear anyone telling this to anyone, and it makes such perfect sense. I hate it even more that the Access Wizard DBs do not follow this simple rule!

 

[Corticus]

Tru dat,

Its really frustrating when your first learning Access and you've used the wizard to help you make some queries. Than you wonder why you can't refer to 'Qry Tbl Students' in your code. without getting an error.
In general, whenever naming anything(in Access, Excel, VB, VBA etc), I think its best to avoid non-text symbols, no spaces, and really, to ensure compatibility, no caps. AKA HTML naming conventions.

Not that you asked, its just that Dreamboat's point about the wizard is very annoying(the point, not Dreamboat! ).

Corticus