Hi,
I'm getting as bit confused with my queries. I simply (!) want to insert a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have this
space.
Thanks
Alan
5  6249 
"Alan" <no****@nospam. com> wrote in message
news:dq******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... Hi,
I'm getting as bit confused with my queries. I simply (!) want to insert a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have
this space.
Thanks
Alan
Add a new field (column) to your query. Call it PostCode2.
Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
Fred Zuckerman
"Fred Zuckerman" <Zu********@sbc global.net> wrote in message
news:fx******** **********@news svr13.news.prod igy.com... "Alan" <no****@nospam. com> wrote in message
news:dq******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... Hi,
I'm getting as bit confused with my queries. I simply (!) want to insert
a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have
this space.
Thanks
Alan
Add a new field (column) to your query. Call it PostCode2.
Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
Fred Zuckerman
The trouble with UK postcodes is that they can vary in length and there are
a number of patterns which are valid. I believe that, although there might
be an odd exception, the one thing you can rely on is the three characters
at the end being Numeric, Alpha, Alpha such as '1TQ'.
So if you are sure the field holds valid uk postcodes which are simply
missing the space, then use Fred's suggestion, but modify the expression to:
Left([Postcode],Len([Postcode])-3) & " " & Right([Postcode],3)
What you are saying is not correct.There are a whole range of postcodes
where the first part would be 3 chars not4 chars.
e.g HU9 0PB
--
Terry Kreft
"Alan" <no****@nospam. com> wrote in message
news:dq******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... Hi,
I'm getting as bit confused with my queries. I simply (!) want to insert a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have
this space.
Thanks
Alan
"Fred Zuckerman" <Zu********@sbc global.net> wrote in message
news:fx******** **********@news svr13.news.prod igy.com... "Alan" <no****@nospam. com> wrote in message
news:dq******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... Hi,
I'm getting as bit confused with my queries. I simply (!) want to insert
a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have
this space.
Thanks
Alan
Add a new field (column) to your query. Call it PostCode2.
Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
Fred Zuckerman
The trouble with UK postcodes is that they can vary in length and there are
a number of patterns which are valid. I believe that, although there might
be an odd exception, the one thing you can rely on is the three characters
at the end being Numeric, Alpha, Alpha such as '1TQ'.
So if you are sure the field holds valid uk postcodes which are simply
missing the space, then use Fred's suggestion, but modify the expression to:
Left([Postcode],Len([Postcode])-3) & " " & Right([Postcode],3) Hi,
I'm getting as bit confused with my queries. I simply (!) want to
insert a
space into a postcode field the 4th character from the right, so, for
example, ML201TQ becomes ML20 1TQ, which is the post offices format.
Unfortunately I've been given a database with postcodes that don't have this space.
Thanks
Alan
Add a new field (column) to your query. Call it PostCode2.
Use Mid(PostCode,1, 4) & " " & Mid(Postcode,5, 3)
Fred Zuckerman
The trouble with UK postcodes is that they can vary in length and there
are a number of patterns which are valid. I believe that, although there
might be an odd exception, the one thing you can rely on is the three characters
at the end being Numeric, Alpha, Alpha such as '1TQ'.
So if you are sure the field holds valid uk postcodes which are simply
missing the space, then use Fred's suggestion, but modify the expression
to:
Left([Postcode],Len([Postcode])-3) & " " & Right([Postcode],3)
Thanks Fred, Anthony and Terry,
Yes Anthony and Terry are correct as UK postcodes do vary in length but the
end is always consistent in that there is always a space at the 4th
character from the right as indicated by this document: http://www.mrs.org.uk/standards/down...codeformat.pdf
Thanks again!
Alan This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Lapchien |
last post by:
I have a field for a postcode. I'd like another field to auto contain an
'area' number, based on the first part (only) of the postcode. For example,
if a postcode LS4 4DJ was entered, another field would read the LS4 part and
enter '7' in that new field.
I have a table that contains an up to date list of just these first-part
postcodes...
Thanks,
Lap
|
by: panorris |
last post by:
I am an infrequent user of Access and have a problem which I hope
someone may be able to help me with. I have a text field in a database
which contains UK postcodes similar to EH4 7TY or DN10 9IU. As shown
in the two examples, a postcode can either have seven characters
including the space in the middle or 8 characters including the space
in the middle. What I would like to do if delete the space from those
postcodes which are 8...
|
by: Dazz |
last post by:
Hello
I have made a small app to query a database which works fine. I am now
adding a logging function which writes back to the database in a separate
table. When I update the OleDataAdapter, I get an error saying "Syntax
error in INSERT INTO statement" but the insert statement was automatically
generated by OleDBCommandBuilder.
Can someone help me in finding the problem????
|
by: jabernet |
last post by:
The following insert takes ages, although it started out pretty fast. I
monitored it with DB2mon, and in the begin insert count was about 1000
per minute while after 300000 or so rows, it droped to about 20 rows
per minute. Anyone any idea what could cause that?
Regards,
Janick
INSERT INTO
|
by: andrewtayloruk |
last post by:
I'm a newbie when it come to things php and i'm having a bit of trouble.
I'm trying to insert data from an html form into a mysql database and can't get it to work.
Just a few bits about my setup, i'm running an sql server locally, i've created the database, table and fields. I think what i'm missing is something that actually runs the sql query when i hit the submit button. Also, i'm aware i haven't made the data being inserted safe, i...
| | |
by: andrewtayloruk |
last post by:
Hi i was wondering if anyone could help.... I have made a little script that puts whatever a user enters into a mysql database. As well as the forms my script has an image input that uploads an image to my webserver. The thing i'm stuck on is getting the script to input the name of the file into a field in the database.
So basically the file will be uploaded and when the submit button is clicked a link to that image will be generated and put...
|
by: billynastie2007 |
last post by:
Hi i am writing a balloon race site and i am having problems with some functions to calculate the distance the balloon travels firstly i am reading my info from the database using the fetch assoc function what i have is 2 table one for the race details and one for the postcode details what i am having problems with is listing all the races in the database then having a coloumn at the end which tell you how far the balloon has travelled i have...
|
by: Pinna |
last post by:
Hiya, I am trying to select based on a range on postcode on a table. The field POSTCODE is a varchar2(8).
The problem is when refinng on 'where postcode between 'PE1' and 'PE29'', because this is a character field and not a number field, 'PE3','PE4','PE5','PE6','PE7','PE8','PE9' are all ignored because the construct orders in characters and then numbers and as a result the query excludes it.
Can anyone provide some code to overcome this...
|
by: panos100m |
last post by:
Hi
I am using DB_dataobjects and pear .
I noticed that for some of my tables my insert statement doesnt work correclty ..
Those tableas are tables that i changed recently (i removed the space and added "_" and now i removed the underscore also)
So this is the output
DataObjects_test: CONNECT: Checking for database database_ in options
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
