Retrieving a sting in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've noticed someone else had a similar problem to me when trying to retrieve the first part of a postcode. I have two types of postode: AB1 2CD and AB10 2CD. So far by putting the following in as a query: Left([InputString], InStr([InputString], " ") - 1) i have managed to get the postcode AB1 to appear correctly (without a space, which is how i want it) but the word #Error comes up on all the postcode AB10. Looking at the expression, it looks fine to me. Can anybody see where i'm going wrong
Thank
 
Accessman said:
I've noticed someone else had a similar problem to me when trying to
Click to expand...
retrieve the first part of a postcode. I have two types of postode: AB1 2CD
and AB10 2CD. So far by putting the following in as a query:
Left([InputString], InStr([InputString], " ") - 1) i have managed to get the
postcode AB1 to appear correctly (without a space, which is how i want it)
but the word #Error comes up on all the postcode AB10. Looking at the
expression, it looks fine to me. Can anybody see where i'm going wrong?

That expression will work fine. The only #Errors you should see is from data
without the space.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Unfortunately for some reason it doesn't. Could this be affected as the postcode format i'm using has an iput mask (LL09\ 0LL)? i have definatley made sure there is a space in the postcode but it still won't work? Any ideas?
 
accessman said:
Unfortunately for some reason it doesn't. Could this be affected as
the postcode format i'm using has an iput mask (LL09\ 0LL)? i have
definatley made sure there is a space in the postcode but it still
won't work? Any ideas?
Click to expand...

you could use fully numeric 4 digit p/codes like we do in little old oz...!
 
I've been looking at your problem and when I first tried it out I'd have agreed with Arvin - that there was nothing wrong with your query. However, after reading about your Input Mask there's actually two parts to the solution

I you adjust your statement to

IIf(Len([InputString])<=4,[InputString],Left([InputString],InStr([InputString]," ")-1)

you wouldn't get #Error even if you only have the first part of the postcode entered

However, the main problem is your Input Mask - you need to adjust it to read
LL09\ 0LL;0;_ instead of just LL09\ 0L
Click to expand...

The CRUCIAL BIT here is the second argument (after the first semi-colon

This "specifies whether Microsoft Access stores the literal display characters in the table when you enter data. If you use 0 for this section, all literal display characters (for example, the parentheses in a phone number input mask) are stored with the value; if you enter 1 or leave this section blank, only characters typed into the control are stored.
(Quoted from Help on 'Input Mask Property'

The third argument (after the second semi-colon) simply provides a placeholder so that the user can see how much needs to be entered

The > at the beginning is ensures that all the postcode is displayed as CAPITAL

Hope that solves your problem

Regard
Tezba
 
Glad to have been able to help

I had a problem myself today (with hyperlinks) and I know how frustrating it gets when you can't get to the bottom of a problem

Began thinking I was useless so it was good to be able to help someone else. But then that's what's so good about these Discussion Groups

King regard
Tezbar
 
Back
Top