Data type mismatch in criteria expression

[stuwallace]

I'm getting this error message when running my query. I'm a newbie so can't quite work out what I'm doing wrong. The query is below but I think the offending part of it is the true expression in the IIf statement and in particular the Instr function. If I put a static expression in there it works.

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 (6 = 1 ,[dbo_DT_CONTRACTOR].[Post_Code],Left([dbo_DT_CONTRACTOR].[Post_Code],InStr(1,[dbo_DT_CONTRACTOR].[Post_Code],' ')+1))) In (SELECT Postcode FROM Postcodes);

Any help would be much appreciated.

Stuart Wallace

 

[Guus2005]

your IIF statement is quite useless.

iif(6=1,...

the processor which gives true as a result is not invented yet. So the true part of the IIF statement is never executed.

HTH

 

[stuwallace]

The reason for the 6=1 was to force a true or false result to enable me to determine which of the expressions was at fault (true or false). If I make this 6=6 it works without error which was making me beleive that the Left or Instr were at fault. The code I would have in the WHERE is

(IIf(Len(Left([dbo_DT_CONTRACTOR].Post_Code],InStr([dbo_DT_CONTRACTOR].[Post_Code],' ')))+1) = 1 ,([dbo_DT_CONTRACTOR].Post_Code],Left([dbo_DT_CONTRACTOR].[Post_Code],InStr(1,[dbo_DT_CONTRACTOR].[Post_Code],' ')) In (SELECT Postcode FROM Postcodes);

 

[namliam]

(IIf(Len(Left([dbo_DT_CONTRACTOR].[b][U][[/U][/b]Post_Code],InStr([dbo_DT_CONTRACTOR].[Post_Code],' '
                                                   )
             )
        )+1
    ) = 1 
        
      ,([dbo_DT_CONTRACTOR].[U][B][[/B][/U]Post_Code],Left([dbo_DT_CONTRACTOR].[Post_Code],InStr(1,[dbo_DT_CONTRACTOR].[Post_Code],' ')) In (SELECT Postcode FROM Postcodes);

1) You are missing a [
2) Your () is IMHO out of whack!

 

[namliam]

Why do the IIF anyways?? Why not use "OR" which is going to be faster probably and definatly easier

Why do an IN and not i.e. Join? Faster still...

 

[stuwallace]

The reason I went of the IIf was because the DT_CONTRACTOR table is not guranteed to have the full postcode if one at all. The Postcode table will contain part postcodes of "EH26 9","L3 2","LA14 4". Couldn't work how to do it any other way. I accept the the () have got a little bit messy but I've be trying to get it working and I think that's where the mess comes from. I'lll check on the missing [

Thanks for your help.

 

[namliam]

Really hard to find a problem in an obiously otherwize broken statement....

I.e.
What are you trying to do with :
Len(Left([dbo_DT_CONTRACTOR].Post_Code],InStr([dbo_DT_CONTRACTOR].[Post_Code],' ')))+1) = 1

???

Trying to find out if there is a space int he Post_code??

 

[stuwallace]

To explain a bit further.

I'm trying to get the details of records in the DT_CONTRACTOR table where the postcode is in the postcodes in the Postcodes table. The problem as mentioned being that the postcodes in the Postcodes table are only part postcodes so I can't use a join (or at least thats what I thought).

The added problem is that the postcode in DT_CONTRACTOR may be empty or have too little of the postcode to make it usable. e.g LA14 instead of at least LA14 7 but ideally LA14 7RT.

The LEN function was to try and get round blank postocodes by testing if the position of a space + 1 character = 1. If it did equal 1 then it meant the postcode does not have a space so is either blank or incomplete. The search wont work for either of these two as the minimum seach inthe Postcodes table is LA14 7. I think i'm over complicating things and there is probably a better way to acheive the results. I just hope my explanation enables you to help me some more.

Thanks for your help so far.

 

[namliam]

I would probably use a cartagian product... something like so...

SELECT Table2.Field1, Table3.Field1
FROM Table2, Table3
WHERE (((Table2.Field1) Like [table3].[field1] & "*"));

If table2 contains:
123 abc
123 bcd
123 cde

And Table3
123 a

It should only return 123 abc, however if Table3 only contains
123

Then ofcourse you run into trouble...

Note that with big # of records this will work but be ultimatly slow and will make your query NOT updatable.

 

[stuwallace]

Thanks mailman

I've have a look at this and try it out on my data.

 

[namliam]

Alternative is to make a query and add fields:
Leftx: Left(Post_Code,x)

<< Insert any given number instead of x >>

Then add the query x times and join your postcode to the different fields...

If you understand what I mean...