Criteria IIf

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have a querie that list only employees from a specific Departement. I
have a text in the Swithboard that indicates the specific Department. Imagine
that in that textbox appears the name IT, that means that only appear people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that when the
Department is HR the criteria turns off, so that means that HR department can
see all employees.

Is it possible??

Thanks in advance,
Marco
 
Could you post the query (in design view select View: SQL from the menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not null.
If you do have cases where Department field is null, you can use a solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






John Spencer said:
Could you post the query (in design view select View: SQL from the menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not null.
If you do have cases where Department field is null, you can use a solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi. I have a querie that list only employees from a specific Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that when
the
Department is HR the criteria turns off, so that means that HR department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
 
What you posted had a missing quotation mark. I think you want:
IIF([forms]![Switchboard]![Departamento]="HR";"*";[forms]![Switchboard]![Departamento])

Try

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
WHERE tbl_Departamentos.Descricao LIKE
IIf([forms]![Switchboard]![Departamento]="HR";"*";([forms]![Switchboard]![Departamento])
ORDER BY Pessoal.ID;


---
John Spencer
Access MVP 2001-2005, 2007

Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






John Spencer said:
Could you post the query (in design view select View: SQL from the menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not null.
If you do have cases where Department field is null, you can use a solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi. I have a querie that list only employees from a specific Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that when
the
Department is HR the criteria turns off, so that means that HR department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 
Hi John. Well, I already had tried with the quotations mark. But in my
opinion when I use quotations marks it means that queries will look for a
department with the name of *, because when something is between qiotations
mark is identify as text??

I thought I could use something like IsNotNull or All, I don't know.

Want you you think?

Marco



John Spencer said:
What you posted had a missing quotation mark. I think you want:
IIF([forms]![Switchboard]![Departamento]="HR";"*";[forms]![Switchboard]![Departamento])

Try

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
WHERE tbl_Departamentos.Descricao LIKE
IIf([forms]![Switchboard]![Departamento]="HR";"*";([forms]![Switchboard]![Departamento])
ORDER BY Pessoal.ID;


---
John Spencer
Access MVP 2001-2005, 2007

Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






John Spencer said:
Could you post the query (in design view select View: SQL from the menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not null.
If you do have cases where Department field is null, you can use a solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I have a querie that list only employees from a specific Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that when
the
Department is HR the criteria turns off, so that means that HR department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 
Criteria of
= "*"
would look for a department named "*"

Criteria of
Like "*"
would look for any department. The "*" character is the wildcard character
meaning any number of characters. If you are using an Access project (.adp
or .ade) then the wildcard character is "%".

As a test, just type
LIKE "*" into the criteria of the query and see if it returns ALL the
records. If not, try LIKE "%" and see if that returns all the records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi John. Well, I already had tried with the quotations mark. But in my
opinion when I use quotations marks it means that queries will look for a
department with the name of *, because when something is between
qiotations
mark is identify as text??

I thought I could use something like IsNotNull or All, I don't know.

Want you you think?

Marco



John Spencer said:
What you posted had a missing quotation mark. I think you want:
IIF([forms]![Switchboard]![Departamento]="HR";"*";[forms]![Switchboard]![Departamento])

Try

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
WHERE tbl_Departamentos.Descricao LIKE
IIf([forms]![Switchboard]![Departamento]="HR";"*";([forms]![Switchboard]![Departamento])
ORDER BY Pessoal.ID;


---
John Spencer
Access MVP 2001-2005, 2007

Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






:

Could you post the query (in design view select View: SQL from the
menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not
null.
If you do have cases where Department field is null, you can use a
solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the
low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I have a querie that list only employees from a specific
Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that
when
the
Department is HR the criteria turns off, so that means that HR
department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 
Hi John, we are almost there. :)

if in criteria ao put only this ALike "%" it returns all. If I put Alike"*"
it returns none. So i put this in criteria:
IIf(([forms]![Switchboard]![Departamento])="HR";ALike
"%";[forms]![Switchboard]![Departamento]), and it returns none, not even with
*.

This are making me nuts. Please help me.
Regards,
Marco


John Spencer said:
Criteria of
= "*"
would look for a department named "*"

Criteria of
Like "*"
would look for any department. The "*" character is the wildcard character
meaning any number of characters. If you are using an Access project (.adp
or .ade) then the wildcard character is "%".

As a test, just type
LIKE "*" into the criteria of the query and see if it returns ALL the
records. If not, try LIKE "%" and see if that returns all the records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi John. Well, I already had tried with the quotations mark. But in my
opinion when I use quotations marks it means that queries will look for a
department with the name of *, because when something is between
qiotations
mark is identify as text??

I thought I could use something like IsNotNull or All, I don't know.

Want you you think?

Marco



John Spencer said:
What you posted had a missing quotation mark. I think you want:
IIF([forms]![Switchboard]![Departamento]="HR";"*";[forms]![Switchboard]![Departamento])

Try

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
WHERE tbl_Departamentos.Descricao LIKE
IIf([forms]![Switchboard]![Departamento]="HR";"*";([forms]![Switchboard]![Departamento])
ORDER BY Pessoal.ID;


---
John Spencer
Access MVP 2001-2005, 2007


Marco wrote:
Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






:

Could you post the query (in design view select View: SQL from the
menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not
null.
If you do have cases where Department field is null, you can use a
solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the
low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I have a querie that list only employees from a specific
Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that
when
the
Department is HR the criteria turns off, so that means that HR
department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I did it, I was so dummy, now I have this:

ALike
IIf(([forms]![Switchboard]![Departamento])="HR";"%";[forms]![Switchboard]![Departamento])



Marco said:
Hi John, we are almost there. :)

if in criteria ao put only this ALike "%" it returns all. If I put Alike"*"
it returns none. So i put this in criteria:
IIf(([forms]![Switchboard]![Departamento])="HR";ALike
"%";[forms]![Switchboard]![Departamento]), and it returns none, not even with
*.

This are making me nuts. Please help me.
Regards,
Marco


John Spencer said:
Criteria of
= "*"
would look for a department named "*"

Criteria of
Like "*"
would look for any department. The "*" character is the wildcard character
meaning any number of characters. If you are using an Access project (.adp
or .ade) then the wildcard character is "%".

As a test, just type
LIKE "*" into the criteria of the query and see if it returns ALL the
records. If not, try LIKE "%" and see if that returns all the records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi John. Well, I already had tried with the quotations mark. But in my
opinion when I use quotations marks it means that queries will look for a
department with the name of *, because when something is between
qiotations
mark is identify as text??

I thought I could use something like IsNotNull or All, I don't know.

Want you you think?

Marco



:

What you posted had a missing quotation mark. I think you want:
IIF([forms]![Switchboard]![Departamento]="HR";"*";[forms]![Switchboard]![Departamento])

Try

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
WHERE tbl_Departamentos.Descricao LIKE
IIf([forms]![Switchboard]![Departamento]="HR";"*";([forms]![Switchboard]![Departamento])
ORDER BY Pessoal.ID;


---
John Spencer
Access MVP 2001-2005, 2007


Marco wrote:
Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






:

Could you post the query (in design view select View: SQL from the
menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not
null.
If you do have cases where Department field is null, you can use a
solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the
low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I have a querie that list only employees from a specific
Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that
when
the
Department is HR the criteria turns off, so that means that HR
department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 
THANKS JOHN, I appreciatted.

Best Regards,
Marco


Marco said:
Hi John, we are almost there. :)

if in criteria ao put only this ALike "%" it returns all. If I put Alike"*"
it returns none. So i put this in criteria:
IIf(([forms]![Switchboard]![Departamento])="HR";ALike
"%";[forms]![Switchboard]![Departamento]), and it returns none, not even with
*.

This are making me nuts. Please help me.
Regards,
Marco


John Spencer said:
Criteria of
= "*"
would look for a department named "*"

Criteria of
Like "*"
would look for any department. The "*" character is the wildcard character
meaning any number of characters. If you are using an Access project (.adp
or .ade) then the wildcard character is "%".

As a test, just type
LIKE "*" into the criteria of the query and see if it returns ALL the
records. If not, try LIKE "%" and see if that returns all the records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi John. Well, I already had tried with the quotations mark. But in my
opinion when I use quotations marks it means that queries will look for a
department with the name of *, because when something is between
qiotations
mark is identify as text??

I thought I could use something like IsNotNull or All, I don't know.

Want you you think?

Marco



:

What you posted had a missing quotation mark. I think you want:
IIF([forms]![Switchboard]![Departamento]="HR";"*";[forms]![Switchboard]![Departamento])

Try

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
WHERE tbl_Departamentos.Descricao LIKE
IIf([forms]![Switchboard]![Departamento]="HR";"*";([forms]![Switchboard]![Departamento])
ORDER BY Pessoal.ID;


---
John Spencer
Access MVP 2001-2005, 2007


Marco wrote:
Hi John. Thanks for your quick reply. I tried your yourcreiteria but is
doen't work.

My SQL code is this:

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal ON tbl_Departamentos.ID =
Pessoal.CentroCusto
WHERE
(((tbl_Departamentos.Descricao)=IIf(([forms]![Switchboard]![Departamento])="HR","*",([forms]![Switchboard]![Departamento]))))
ORDER BY Pessoal.ID;

In this crteria
IIF(([forms]![Switchboard]![Departamento])="HR";"*;([forms]![Switchboard]![Departamento]))

It returns an error: "it contains an invalid expression".

Any idea?

Regards,
Marco






:

Could you post the query (in design view select View: SQL from the
menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not
null.
If you do have cases where Department field is null, you can use a
solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the
low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I have a querie that list only employees from a specific
Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that
when
the
Department is HR the criteria turns off, so that means that HR
department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 
Hi John, is it possible to make goups? I mean, imagine that the user XPTO
must have acces to two or more departments, I can I make that??

Is it possible?

Marco




John Spencer said:
Could you post the query (in design view select View: SQL from the menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not null.
If you do have cases where Department field is null, you can use a solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi. I have a querie that list only employees from a specific Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that when
the
Department is HR the criteria turns off, so that means that HR department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
 
Ways to do that are to create the SQL string in VBA and use that string

Or to create a table that ties the Departments together with the Users and
insert that into the query.

TableUserDepartments
UserID
DeptID

One record per user and department combination. Then add that table to the
query using a join.

SELECT Pessoal.ID, Pessoal.Nome, tbl_Departamentos.Descricao
FROM tbl_Departamentos INNER JOIN Pessoal
ON tbl_Departamentos.ID = Pessoal.CentroCusto
INNER JOIN tbl_UserDepartments
ON tbl_UserDepartments.DeptID = tbl_Departamentos.ID
WHERE tbl_UserDepartments.UserID = [forms]![Switchboard]![User]
ORDER BY Pessoal.ID;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi John, is it possible to make goups? I mean, imagine that the user XPTO
must have acces to two or more departments, I can I make that??

Is it possible?

Marco




John Spencer said:
Could you post the query (in design view select View: SQL from the
menu).?

Guessing you have something like the following for your criteria

Field: Department
Criteria: [Forms]![Switchboard]![SomeControl]

You MIGHT get the desired results with

Field: Department
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That will probably work in all cases where the Department field is not
null.
If you do have cases where Department field is null, you can use a
solution
like
Field: SearchThis: Department & ""
Criteria: LIKE
IIF([Forms]![Switchboard]![SomeControl]="HR","*,[Forms]![Switchboard]![SomeControl])

That is not the most efficient search, but if your records are in the low
thousands, the performance will probably be acceptable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi. I have a querie that list only employees from a specific
Departement.
I
have a text in the Swithboard that indicates the specific Department.
Imagine
that in that textbox appears the name IT, that means that only appear
people
from the IT Department. That's all ok and till here no problem.

I need that for Human Resources appears all employees, imagine that
when
the
Department is HR the criteria turns off, so that means that HR
department
can
see all employees.

Is it possible??

Thanks in advance,
Marco
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

cross over a criteria from a querie. 3
Criteria is not working 3
Excel Wildcard in Excel array formula 0
Query with most recent applicable criteria? 1
help me 2
Looping through records 1
Issues with Left Join 1
Query Difficulty 9

Back
Top