Solved #Error on shortening postcode if no postcode is present (1 Viewer)

[Number11]

So i have this to shorted the postcode, which is working as long as a postcode is provided within the field, how can i get around so i dont get the #ERROR would like it just to be left blank? please?

ShortPostcode: Left([PostCode],1) & IIf(Asc(Mid([PostCode],2,1))>=65,Mid([PostCode],2,1),"")

 

[ebs17]

While SQL and thus queries can brilliantly handle NULL content, this is not always the case when using VBA functions.
In your example, Asc has a problem with NULL. With Nz you can set a replacement value, like ...

... & IIf(Asc(Mid([PostCode], 2, 1), 0) >= 65, ...

 

[cheekybuddha]

Try:

ShortPostcode: IIf(Len(Postcode & '') >0, Left([PostCode],1) & IIf(Asc(Mid([PostCode],2,1))>=65,Mid([PostCode],2,1),""),  '')

 

[arnelgp]

you can also add two spaces to your Postcode field:

ShortPostcode: Trim(Left([PostCode] & Space(2),1) & IIf(Asc(Mid([PostCode] & Space(2),2,1))>=65,Mid([PostCode] & Space(2),2,1),""))

 

[Number11]

Try:

ShortPostcode: IIf(Len(Postcode & '') >0, Left([PostCode],1) & IIf(Asc(Mid([PostCode],2,1))>=65,Mid([PostCode],2,1),""),  '')

Thanks thats worked

 

[cheekybuddha]