Simple Query Criteria- Plz Solve it!

[paulcherianc]

Hi..

I am trying to filter a list by using the below formula in a query. But it doesnt work especially when i input <> and the result is nothing. Could anyone fix this issue for me.

IIf([Table1].[Location]="0217",<>"075",Last([EmployeeMaster].[empno]))

Regards,

Paul C

 

[Minty]

You can't use <>"075" as the result of a IIf

The syntax is IIF(test expression, result if true, result if false)
Results can be calculated but <>"075" makes no sense.

 

[RainLover]

<>"075",

Can you explain this.

Also it might help if you wrote your statement in simple English for us to see.

e.g.

IIf([Table1].[Location]="0217",<>"075",Last([EmployeeMaster].[empno]))

If Location = 0217, then ??????, Else empno.

Look forward to your reply.

 

[paulcherianc]

If Location = 0217, then it should exclude the number 075 from empno and show the remaining numbers, Else empno.

Hope this information is ok with you.

Thanks and Best Regards,

Paul C

 

[paulcherianc]

You can't use <>"075" as the result of a IIf

The syntax is IIF(test expression, result if true, result if false)
Results can be calculated but <>"075" makes no sense.

Hi Minty,

Is there any solution to this. Plz advise.:banghead:

 

[Minty]

I don't think you are approaching your problem from the right direction.
From your description:
You have a table with locations and an employees table.
You are trying to list employees against locations ? or Locations with available employees ?

Take a step back and tell us the slightly bigger picture and perhaps a sample of the data & tables you currently have.

 

[paulcherianc]

I don't think you are approaching your problem from the right direction.
From your description:
You have a table with locations and an employees table.
You are trying to list employees against locations ? or Locations with available employees ?

Take a step back and tell us the slightly bigger picture and perhaps a sample of the data & tables you currently have.

All right. Could you please look in to the attachment. I have explained it for your better understanding. Thanks in advance!

 

[Minty]

Okay - I have to ask the question if 075 should not be used for location 0217, why store that combination in the table?

The table should only have valid combinations in it - or if you need to disable a combination for some reason add a field for Enabled yes/no and filter the query results to only include enabled combinations.

 

[paulcherianc]

Can you explain this.

Also it might help if you wrote your statement in simple English for us to see.

e.g.

IIf([Table1].[Location]="0217",<>"075",Last([EmployeeMaster].[empno]))

If Location = 0217, then ??????, Else empno.

Look forward to your reply.

Hi..Rain,

Do you have some advises in this regards. Plz help me.

 

[stopher]

Maybe you can do something like:

(Location='0217' AND empno='075') OR (Location<>'0217')

 

[RainLover]

Where do you put this code?

 

[paulcherianc]

Where do you put this code?

Hi Rain,

Its in a Report Query.

 

[RainLover]

Hi Rain,

Its in a Report Query.

I have been away. Sorry.

The Criteria for this expression is simply the "Location" e.g. 0217.

Go back to your query and select the fields that you require. Then on the line that says Criteria go to the field Location and enter 0217.

This should return all the records where the criteria for the location is equal to 0217.

I am not sure that this is what you need or not but it is all I could think of based upon what you have stated.