Criteria is not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marco - 31 Oct 2007 14:18 GMT

Hi. A criteria is not working on my query.

I have a query that loads somes queries. In one, I have the department code
field/column.

so, in this final qury appears me all department codes.

I need to have a criteria that shows only a specific department, so, I put a
criteria, witch is "05" (yes, text) to show only the deptarment that the code
is 05 but for some reason showing all departements. it seems to be ingoring
the criteria.

Why is this happening?

regards,
Marco

Reply to this Message

Ofer Cohen - 31 Oct 2007 14:41 GMT

Can you post the SQL?

Do you use a union query? mybe the criteria works on the last query in the
UNION.

Signature

Good Luck
BS"D

> Hi. A criteria is not working on my query.
>
[quoted text clipped - 12 lines]
> regards,
> Marco

Reply to this Message

Marco - 31 Oct 2007 15:00 GMT

here it goes:

SELECT cons_Pessoal.ID, cons_Pessoal.Nome, cons_Pessoal.CentroCusto,
CentroCustos.Descricao, Nz([hpga],0)-Nz([hga],0) AS TotalHorasGozarA,
[8_cons_Sum_Horas_Adaptabilidade_Por_Departamento].Codigo,
[9_cons_Sum_Horas_Gozadas_Por_Departamento].Codigo,
IIf([8_cons_Sum_Horas_Adaptabilidade_Por_Departamento]![Descricao]<>"",[8_cons_Sum_Horas_Adaptabilidade_Por_Departamento]![Descricao],[9_cons_Sum_Horas_Gozadas_Por_Departamento]![Descricao])
AS DescricaoDept,
[8_cons_Sum_Horas_Adaptabilidade_Por_Departamento].TotalHoras AS
TotalHorasHPGA, [9_cons_Sum_Horas_Gozadas_Por_Departamento].TotalHoras AS
TotalHorasHGA, Fix([TotalHorasGozarA]) & Format([TotalHorasGozarA]/24,":nn")
AS TotalHorasLiquidaGozarA, IIf(([TotalHorasGozarA])<0,"COMPENSAR ADAPT.","")
AS Mensagem, Nz([he],0)-Nz([hge],0) AS TotalHorasGozarE,
Fix([TotalHorasGozarE]) & Format([TotalHorasGozarE]/24,":nn") AS
TotalHorasLiquidaGozarE,
[49_qry_Sum_Horas_Extra_por_Departamento_qry].TotalHoras AS TotalHorasHE,
[52_qry_Sum_Horas_Gozadas_Extra_por_Departamento].TotalHoras AS TotalHorasHgE
FROM CentroCustos INNER JOIN ((((cons_Pessoal LEFT JOIN
9_cons_Sum_Horas_Gozadas_Por_Departamento ON cons_Pessoal.ID =
[9_cons_Sum_Horas_Gozadas_Por_Departamento].NumFuncionario) LEFT JOIN
8_cons_Sum_Horas_Adaptabilidade_Por_Departamento ON cons_Pessoal.ID =
[8_cons_Sum_Horas_Adaptabilidade_Por_Departamento].NumFuncionario) LEFT JOIN
49_qry_Sum_Horas_Extra_por_Departamento_qry ON cons_Pessoal.ID =
[49_qry_Sum_Horas_Extra_por_Departamento_qry].NumFuncionario) LEFT JOIN
52_qry_Sum_Horas_Gozadas_Extra_por_Departamento ON cons_Pessoal.ID =
[52_qry_Sum_Horas_Gozadas_Extra_por_Departamento].NumFuncionario) ON
CentroCustos.ID = cons_Pessoal.CentroCusto
WHERE (((cons_Pessoal.CentroCusto)="05") AND
((Nz([hpga],0)-Nz([hga],0))<>0)) OR (((Nz([he],0)-Nz([hge],0))<>0))
ORDER BY cons_Pessoal.ID;

> Can you post the SQL?
>
[quoted text clipped - 17 lines]
> > regards,
> > Marco

Reply to this Message

Jerry Whittle - 31 Oct 2007 16:26 GMT

Look at the WHERE clause. It first says "05" AND <> 0 .

Next it says OR he - hge <> 0

Therefore even though CentroCusto isn't = 05, the other CentroCusto's can be
returned if he - hge <> 0.

Try this:
WHERE (((cons_Pessoal.CentroCusto)="05")
AND ((Nz([hpga],0)-Nz([hga],0))<>0))
OR (((cons_Pessoal.CentroCusto)="05")
AND ((Nz([he],0)-Nz([hge],0))<>0))

Hopefully I put in the correct number and locations of the ( ) 's.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> here it goes:
>
[quoted text clipped - 48 lines]
> > > regards,
> > > Marco

Reply to this Message