Query Filter on Criteria; how? 1

[Utreg]

Hi,

Have created a query wich combines serveral tables.
When I run the query without a filter it shows all data correctly.
When I try to filter out 'unwanted data' via Criteria I'm prompted to input 'FirstName'?

I have a column [Name] which is filled via an IFF statement on Firstname and LastName (Only if FistName is blank the value from Lastname appears).
Example:
[FirstName] [LastName] [Name] [NameCheck]
last last last
first first first
first1 last1 first1 first1
User last2 User <Null>

So far so good, data is displayed correctly and antoher column [NameCheck] is also filled with data via
NameCheck: IIf(Instr([Name];"User"));Null;[Name])

Query runs fine but when I add Criteria "Is Not Null" to [NameCheck] to filter out unwanted data I'm prompted for 'Firstname'?

Do not know why my Access 97 does this?

My Query is far more complicated but think all relevant info is posted. Thanks 4 help on this one.

 

[dhookom]

First, Name is a property of every object in Access so it is not good to use name as the name of anything. Consider changing it to "ForLName" or something similar. Also, I always try to avoid using a derived alias in another expression in the query. For instance, if you have:

ForLName: Nz([FirstName]; [LastName])

then use

NameCheck: IIf(Instr(Nz([FirstName]; [LastName]); "User"));Null;Nz([FirstName],[LastName]))

This assumes your delimiter is ; rather than ,.

Duane
Hook'D on Access
MS Access MVP

 

[Utreg]

Thank you, it works fine now.

I've updated all (derived alias) queries and used the 'Nz' command where possible.
The parameter prompt is gone now.