| | |
|
|
|
Retrieving a sting in a query |
|---|
| message from =?Utf-8?B?QWNjZXNzbWFu?= on 22 May 2004 |
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?
Thanks
|
| Arvin Meyer replied to =?Utf-8?B?QWNjZXNzbWFu?= on 22 May 2004 |
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.
|
| =?Utf-8?B?YWNjZXNzbWFu?= replied to Arvin Meyer on 22 May 2004 |
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?
|
| Damien McBain replied to =?Utf-8?B?YWNjZXNzbWFu?= on 22 May 2004 |
you could use fully numeric 4 digit p/codes like we do in little old oz...!
|
| =?Utf-8?B?VGV6YmFy?= replied to =?Utf-8?B?YWNjZXNzbWFu?= on 22 May 2004 |
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.
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:
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 CAPITALS
Hope that solves your problem.
Regards
Tezbar
|
| =?Utf-8?B?QWNjZXNzbWFu?= replied to =?Utf-8?B?QWNjZXNzbWFu?= on 22 May 2004 |
That works perfectly - thanks very much!
|
| =?Utf-8?B?VGV6YmFy?= replied to =?Utf-8?B?QWNjZXNzbWFu?= on 22 May 2004 |
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 regards
Tezbar
|
|
Archived message: Retrieving a sting in a query (MS Access)
