#Error in Query Results (1 Viewer)

GrahamK

Registered User.
Local time
Today, 12:34
Joined
Aug 5, 2008
Messages
25
Hi Folks,

I'm having a couple of issues with a query I'm running through MSAccess. It has in it a couple of "calculated" fields, for one of them, it all works fine, the second has some #Error data entries being returned it it.

The Query is Below:
==============
SELECT tblContractType.Type AS Contract,
tblSim.[Phone number] AS [Service Ref],
tblBlackberry.BBerry_Mobile AS [Cost Centre 1],
tblEmployeeList.[Home Job],
tblEmployeeList.[Home Task],
tbluser.AltBudget,
tbluser.AltTask,
tblEmployeeList.Name AS [Cost Centre 4],
tbluser.User AS [Cost Centre 5],
tblSite.Name AS [Address Details],

IIf([Home Job]=[AltBudget],[Home Job],IIf([altbudget]=0,[Home Job],IIf([Home Job]<>[AltBudget],[Altbudget],[Home Job]))) AS [Cost Centre 2],

IIf([Home Task]=[AltTask],[Home Task], IIf([alttask]=0,[Home Task],IIf([Home Task]<>[AltTask],[AltTask],[Home task]))) AS [Cost Centre 3]

FROM (tblSite RIGHT JOIN (tblBlackberry RIGHT JOIN (tblEmployeeList RIGHT JOIN tbluser ON tblEmployeeList.[No] = tbluser.User) ON tblBlackberry.[IMEI Number] = tbluser.Handset) ON tblSite.SiteID = tbluser.Site) RIGHT JOIN ((tblContractType RIGHT JOIN tblSim ON tblContractType.TypeID = tblSim.[Contract Type]) RIGHT JOIN tblUserSim ON tblSim.SimID = tblUserSim.SimID) ON tbluser.User = tblUserSim.UserID

WHERE (((tblContractType.Type)="BT O2"))

ORDER BY tbluser.User;
=============================

The theory behind it is that each user has a perpetual Home "Job" and "Task" Code pulled out of Payroll. If they wish to declare another then this is recorded in "AltBudget" and "AltTask" columns.

The Calculated Cost Centre 2 takes the user's perpetual one and uses it if it's the same as the Alternative, or uses the Alternative if it is different to the home, or uses the home if alternative is 0.

The Calculated Cost Centre 3 (should) work the same for the Task Codes.

I have attached a screenshot of what is happening - I am struggling to understand why it's working for the budget codes but not my task codes...

Many Thanks for any assistance!
Graham
 

Attachments

  • MPTExtract.jpg
    MPTExtract.jpg
    103.6 KB · Views: 225

neileg

AWF VIP
Local time
Today, 12:34
Joined
Dec 4, 2002
Messages
5,975
The error occurs when Home Task and Alt Task are different and Alt Task isn't a null value. Your final Iif statement is redundant and this is where the error occurs. Try changing the sql to
Code:
IIf([Home Task]=[AltTask],[Home Task], IIf([alttask]=0,[Home Task],[AltTask])) AS [Cost Centre 3]
Also notice that you are testing for Alt Task to be zero. Since it's a text field, it won't ever be zero so you need to test for a null or a zero length string or a space (which ever allpies).
 

GrahamK

Registered User.
Local time
Today, 12:34
Joined
Aug 5, 2008
Messages
25
Neil - Many thanks for your assistance, as a result it's all sorted!

Thanks
Graham
 

Users who are viewing this thread

Top Bottom