Ms Query Data Type mismatch

[swaink]

Hi All

I have a data set in an Excel Spreadsheet table and am querying that table from another workbook

In the data set I have a column showing the UK postcode with no space, I need to extract only the postcode down to sector level and add a space

In my query I have used select LEFT([Postcode],LEN([Postcode])-3) &" "& LEFT(RIGHT([Postcode],3),1) but I receive a Data Type mismatch in criteria expression error

For example TW145JS would look like TW14 5

I have tried searching for a clue to where I am going wrong without success and I wondered if anyone else has experienced this problem and is able to point me ion the right direction

Any help appreciated

Many thanks

Kevin

 

[oldbrewer]

7

5

TW145

5

4

TW14

5

TW14 5

TW145JS

5

3

W12

3

2

W1

2

W1 2

W12BD

6

4

W115

4

3

W11

5

W11 5

W115AC

LEN

first "n"

NEW LEN

POS OF

FIRST

NEXT

CONCAT

required

REQ'D

BLOCK

CHAR

COLS

SPACE

F AND G

WITH

SPACE

BETWEEN

THIS shows the logic

are you adhering to this logic ?

<colgroup><col width="64" span="16" style="*******48pt"> </colgroup><tbody>
</tbody>

 

[swaink]

Morning oldbrewer thanks for the reply

I have revisited the logic and it appears to be ok, I broke it down to just one piece LEFT([Postcode],LEN([Postcode])-3) this still shows the mismatch error
LEFT([Postcode],4) works fine, LEN([Postcode])-3 works ok its just doesn't work when I combine the two

Kev

 

[RoryA]

Do you have any blank postcodes, or shorter than 4 characters?

 

[swaink]

Hi Rory

Yes there are some blanks and potentially there could be some less than four where the customer has not entered the correct postcode

Kev

 

[RoryA]

I think either of those could cause your problem since you'd be supplying a negative number of characters to take. Also, if you have values treated as Null, or errors, that would cause you problems, so you'll need to test for that.

For example, something like:

IIF(IsNull([PostCode],"",IIf(LEN([PostCode]<4,[Postcode],LEFT([Postcode],LEN([Postcode])-3) &" "& LEFT(RIGHT([Postcode],3),1)))