Extract UK Postcode With Expression In Query
Jan 19, 2006
Hi
If i wanted to run a query from customer records in my database and wanted a list of specific postcodes that matched the criteria how would i do this?
For example each customer records has a postcode BD4 4KL, LS9 7YH ETC
I want to categorise each postcode set to see for example all the customers with postcodes begining with have ordered factsheets.
In the criteria part of the query how would i write it so it only extracts specific postcodes from the database from the ones i require
e.g "HU1, HU2, HU3 HU4" - checks database - returns all postcodes beginning with this.
Please help me!
Thanks
View Replies
ADVERTISEMENT
Dec 31, 2014
I have tried to find this on the forum but it seems all the answers are to do with manipulating the strings based on what the postcode is whereas I just want to extract the postcode as a separate string as simply as possible.
Basically, I have a text field called Add5 which has the last line of the address including the postcode.
Example: "Northampton NN1 7PQ"
I am trying to end up with two strings like this
Add5 = "Northampton"
Postcode = "NN1 7PQ"
I only need to do this in one place, I don't think it needs a module.
View 5 Replies
View Related
Feb 12, 2015
I have a table with about 29,000 postcode values
I want to extraxct the first part of the field (usually 3 or 4 characters)
How can I automate this?
View 2 Replies
View Related
Feb 4, 2014
I have a table with a string field included for an address.
Some rows have postcodes and some dont
How can I extract the PostCode values from the field?
View 14 Replies
View Related
Sep 5, 2007
I have a table with a text field (named Description), which may or may not contain an "ID Number", which is of the form [CHSV]#[A-Z][A-Z][A-Z]## - NB this is not necessarily at the beginning or the end, and will vary between records. For e.g.
"something C4CLE01"
"something H5STT02 Something else"
What I would like to do in a query is to extract just the ID number part and put that in a new field e.g.
"something C4CLE01" => "C4CLE01"
"something H5STT02 Something else" => "H5STT02"
So far I have considering LIKE, which is no good because it doesnt tell you where in the string the regular expression is, and INSTR doesnt work with regular expressions AFAIK.
Added bonus difficulty - I use the database as an external source for an Excel pivot table. I created a function (which I named JOBID) in VBA to do the above job easily enough i.e. I added a field "JOBID([Description])", but when I try to use the data in said pivot table, it says "Undefined function JOBID in expression". I have tried copying the function to the Excel workbook, but that doesn't work.
Im using Office 2003 if that makes any difference. Thanks in advance.
View 5 Replies
View Related
Jul 8, 2005
Am trying to query a customer database to find a how many instances of each postcode exist to determine geographic distribution. Only interested in the first part of the postcode so the following code was suggested:
SELECT Count(owners.Field8) AS Total, Left$([Field8], Len([Field8])-3) AS Code
FROM owners;
However it returned a message saying "you tried to execute a query that does not include the specified expression 'Left$([Field8], Len([Field8])-3)' as part of an aggregate funtion.
What does this mean? :confused:
View 1 Replies
View Related
Jun 5, 2006
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be mich appreciated.
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
View 4 Replies
View Related
Jul 7, 2005
Hello all!
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
Any help most gratefully received!
:)
View 8 Replies
View Related
Aug 14, 2007
I have a table that keeps track of how many times an account is listed. I want to write a query that will only return the greatest number of occurences for that account. For example if acct 12345 has 3 occurences in the database I only want to return the information that is associated with the #3 occurence. As stated there is a field that calculates the number of occurences for each account so in this case there would be a record for 1, 2, and 3 occurence so I only want to see #3. Hope that makes sense. Thanks!
View 3 Replies
View Related
Aug 17, 2005
Hi there,
I am a relative newcomer to Access, and am trying to work out if there is a way to isolate email addresses in a line of text, and delete all the text around them.
I have basically got a column which contains blocks of text and I want to extract the email addresses that are embedded within them. Does anyone know if this is possible by running queries?
Thank you in advance for any help! :confused:
Dan
View 5 Replies
View Related
Oct 1, 2006
Hi, newbie question.
I have an existing select Query, that has a field of dates. I want a new select query based on that source that shows all records except for the one with the largest value date. There will be no duplicate dates. I tried:
SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));
but when I run the query I get error message:
"Cannot have an aggregate function in WHERE clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"
I clicked on Help but did not understand what it said. So any help from here would be appreciated.
Thanks
View 2 Replies
View Related
Sep 15, 2013
I am trying to extract data from a query. Part of code when form loads, my questions is bold:
'query criteria
vOrgCode = Me.tOrgCode
'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"
[code]...
i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.
View 3 Replies
View Related
Jun 20, 2005
I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.
How can I do it in a query, there is no find function. If there is an equivelant what is it?
Sample data:
22DF-RED
33AF-0
44TG-Blue
I need only the data to the left of the Dash
Thanks
Ziggy
View 2 Replies
View Related
Dec 20, 2006
Hello I have a query in MSACCESS that looks like this
N| Q | A | B | C | D | E | F |
X|..4.|..1.|..6.|..7.|..9.|...|...|
now my problem is that I want to have a query select the 2 closes values to Q from columns either A B C or D and put them into columns E and F
I am just so stuck here, if anyone can help or give me a steer in a right direction i would greatly appreicate it!
View 1 Replies
View Related
Nov 28, 2012
I am trying to construct a query to extract mailing addresses from a table. I have individuals entered into a table (a separate record for each person) but if they are married I want an address such as Mr and Mrs J. Doe so that only one address label is printed off so that only one letter is sent out. If one of them dies then the address should only go to the surviving party eg Mrs J.
View 4 Replies
View Related
Feb 18, 2014
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.
View 2 Replies
View Related
Jun 11, 2015
is it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.
Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir
What I want the query to return:
Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A
View 6 Replies
View Related
Jun 27, 2013
I am trying to loop through a query results to extract email addresses from a query result.
I have the following code
NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
[Code] .....
If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?
Someone on another post said that sql uses US date format so i tried converting to us date and still no results?
View 6 Replies
View Related
Mar 6, 2006
I need to know how to be able to make my database accept only postcode format entries like RT24 6QP (english). Like a validation , it would only accept that format. How would i go about doing that.
Please explain to be in easy to understand contex as im no expert with access.
Thanks
View 6 Replies
View Related
Jul 1, 2015
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
View 1 Replies
View Related
Jun 21, 2007
Hi, I am inputing addresses into a massive databse, through a form, does anyone know a way, that i can get it to do a search for the address based on the postcode, like how googlemaps does it or something?
Thanks
Alex
View 2 Replies
View Related
Jul 4, 2007
I was wondering if anyone had any experience of integrating a product like QuickAddress (QAS) into an Access Database to aid the completion of address details. ThanksRussG
View 6 Replies
View Related
Jul 8, 2005
Hi Guys,
I have searched the forum, and not really found the answer I`m looking for.
I used in my Database, a postcode field, which for 90% of the postcodes I input is is fine.
However, som of the postcodes dont conform to standard post codes.
I`ve found some info in this link, http://www.access-programmers.co.uk/forums/showthread.php?t=89393&highlight=links
but this didnt really help.
Most postcodes ( as set in the database properties ) confirm to thiis standard
SK01 1AA
Which if intered in this format works all OK.
However, some postcodes go in as fiollows
M01 1AA
Which leaves me a digit short, and messes up my search sections of the database, and the link to external map programs like M$ Mappoint.
Is there anyway that anyone knows of that will sort this out, and allow both entries withouht screwing up other programs.
This field is also a REQUIRED field, and must be a data field, or my external program links like M$ Map Point wont work.
THanks in advance guys.
Max
View 12 Replies
View Related
Mar 20, 2006
hello all
this thing is really bugging me im trying to come up with a post code validation and i have tried dozens, which should have worked really, but just havent so wondering if you guys knew any
examples of postcodes would be
BN3 8JH
BN41 8JH
theyre the only two types that are bound to be entered really
the first and second characters have to be a letters
the 3rd has to be a number
the 4th is sometimes a letter (not always present)
then theres a space
then there has to be a number
letter
letter
any suggestive codes would be great, as none of mine have worked its in access
thanks
nightmare lol
View 1 Replies
View Related
Nov 1, 2006
Hi,
I have a postcode field on a form that allows 8 characters. e.g. ST10 8BY including the space in the middle.
The only validation I have been able to use thus far is the above but I would like to know how to validate two letters (AA) then between 1 and 2 numbers (11) etc.
Is there any code/built in functions that allows this. I know there is an input mask but if I use that, I can set the poscode format up like ST10 8BY but it thinks that the rule has been broken if the postcode was changed to S10 8BY by removing the T. This is also a valid postcode.
Does anyone have any suggestions?
Thanks
View 3 Replies
View Related
Dec 3, 2006
hi guys,
Just been asked this and not sure if it is possible...
they do not want to pay loads of money to the royal mail for their postcode/address data. but they want a faster way (like typing postcode and address options popup) of entering addresses. There are tons of websites that can be used to find addresses with a postcode, i was wondering if it was possible to somehow link the database to lookup address from a website...?
anyone got any ideas?
thanks, james
View 11 Replies
View Related
