Wrong number of arguments used with function

[stuwallace]

I keep getting a "Wrong number of arguments used with function in query expression" when I try to run my query.

I'm no expert with Access but all I'm trying to do is join two tables containing some customer details (DT_CONTRACTOR,Movex Customers) and use part of the postcode from DT_CONTRACTOR to check which region the postcode is in. The regions are held in the Poscodes table. For info some of the postcodes in the are blank, some are incomplete but the majority are fully complete. An example of the Postcodes table and the code are below.

Postcodes table

Region Poscode

Edinburgh EH26 9
Edinburgh EH26 0

Code

SELECT dbo_DT_CONTRACTOR.Account_No, dbo_DT_CONTRACTOR.Contractor_Name, dbo_DT_CONTRACTOR.Address_Line1, dbo_DT_CONTRACTOR.Address_Line2, dbo_DT_CONTRACTOR.Address_Line3, dbo_DT_CONTRACTOR.Address_Line4, dbo_DT_CONTRACTOR.Address_Line5, dbo_DT_CONTRACTOR.Post_Code, [Movex Customers].OKSMCD
FROM dbo_DT_CONTRACTOR INNER JOIN [Movex Customers] ON dbo_DT_CONTRACTOR.Account_No = [Movex Customers].OKCUNO
WHERE (IIf(Len(Left([dbo_DT_CONTRACTOR].[Post_Code],(InStr([dbo_DT_CONTRACTOR].[Post_Code],' '))))+1) = 1,[dbo_DT_CONTRACTOR].[Post_Code],"EH26 9") In (SELECT Postcode FROM Postcodes);

Any help much appreciated as I thought this would be simple to do and has now taken me 2 days to get nowhere.

Stuart

 

[stuwallace]

Just to point out the "EH26 9" in the false condition is there to simplify the whole process. I will be calculating the value in here once I get the main code working.

Stuart