MS Access Query using calculated field - Access ignores criteria.

[Derek]

When I run the below calculated field with the criteria <>"Yes" Access seems
to ignore this and still include records where there is a "Yes". I know the
problem is with the field "LastOfBudgetLimit" which is a field from another
query. If I take this out, it works fine. I have a feeling that because the
record I want excluded does not exist in the LastOfBudgetLimit output query
thats why the below code is not working. However dont completely understand
as I have put another condition (Seperate calculated field not shown here)
where if the record doesnt exist (ie is null) then put in a 0, so the
formula: [BudgetLimit]-[LastOfBudgetLimit]=0 is effectively 0-0=0
So dont understand why that would be a problem. See code below...

IIf([dbo_tblStatementOfWork].[DateValidUntil] Is Not Null And
[dbo_vwRptSoW1SoWList].[Status]="At Risk" And
[dbo_tblStatementOfWork].[BudgetLimit]-[LastOfBudgetLimit]=0,"Yes","No")

Output of the above still returns records with "Yes" even where all the
above criteria is met and the criteria is <>"Yes"

 

[Dale Fye]

Derek,

Did you ever get this working the way you wanted? If not, post the entire
SQL from your query and I'll take a look.

Dale

 

[Derek]

Dale, yes it did, I had to add some other criteria and it worked fine. I
think the problem was that it was referencing a table where this particular
record did not exist even though another query produced a 0 it still seems to
link back to the original table.
Thanks for replying and your offer of help.
--
Regards
Derek

Derek,

Did you ever get this working the way you wanted? If not, post the entire
SQL from your query and I'll take a look.

Dale
--

email address is invalid
Please reply to newsgroup only.

When I run the below calculated field with the criteria <>"Yes" Access seems
to ignore this and still include records where there is a "Yes". I know the
problem is with the field "LastOfBudgetLimit" which is a field from another
query. If I take this out, it works fine. I have a feeling that because the
record I want excluded does not exist in the LastOfBudgetLimit output query
thats why the below code is not working. However dont completely understand
as I have put another condition (Seperate calculated field not shown here)
where if the record doesnt exist (ie is null) then put in a 0, so the
formula: [BudgetLimit]-[LastOfBudgetLimit]=0 is effectively 0-0=0
So dont understand why that would be a problem. See code below...

IIf([dbo_tblStatementOfWork].[DateValidUntil] Is Not Null And
[dbo_vwRptSoW1SoWList].[Status]="At Risk" And
[dbo_tblStatementOfWork].[BudgetLimit]-[LastOfBudgetLimit]=0,"Yes","No")

Output of the above still returns records with "Yes" even where all the
above criteria is met and the criteria is <>"Yes"