Re: Querying for postcodes within an address against another maste

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Trim(Mid([Run_Point_Address_A],InStr(1,[Run_Point_Address_A],",")+1))

This works from the inside to the outside so the first thing that happens is

InStr(1,[Run_Point_Address_A],",") finds the location of the comma and then
we add 1 to we have the position of the first character after the comma.

Mid([Run_Point_Address_A],InStr(1,[Run_Point_Address_A],",")+1) returns the
string from the position to the end of the string. The number of characters
to return is not specified, so mid defaults to all the characters from the
start position to the end position.

Trim simply chops off any leading or trailing spaces. This is handy since
users are prone to entering no spaces to multiple spaces after a comma.
Normally one space, but sometimes none and sometimes 2 or 3 (or more).

xxx, AL 21
xxx, AL 21
xxx,AL 21

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"efandango" <efandango@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:528DDCD6-D791-4603-9AB3-93FCCEAC739A@xxxxxxxxxxxxxxxx
John,

Thanks for helping with my problem, After a fair bit of trial and error
I'm
happy to say that your Formula helped immensly. What I had to do in order
for
it to work for me was to create a Union Query, taking the 8 Venue and
address
per run and get it down to a 2 field array. Once i did that, i was able to
use your Formula against this 2 field array. I also had to change the
field
definitions from:

Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))


TO

PostCodeFrom:
Trim(Mid([Run_Point_Address_A],InStr(1,[Run_Point_Address_A],",")+1))

to reflect the only relevant fields in the new unionised and slimmed down
table .

The only thing is i'm not sure what the various syntax means in your
formula, i tried to look them up referencing things like 'Trim and Instr,
but
couldn't figure out how the whole formuala actually works. Can you
explain?

in the meantime,

THANKS

regards

Eric


"John Spencer" wrote:

What I gave you can be used in a query as a calculated column.


Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))

As for the rest of what you are trying to do, I thought you already had
a solution, but just needed to solve this problem.

Since I don't know what is in your "sub table" in terms of fields, I am
unsure of how to advise you.

The speed of anything you do would significantly improve if you had the
Postcodes in separate fields. I suspect that you could use an non-equi
join in a query using the calculated value as part of the join and
joining to two instances of your "sub table" from each of the From and
To postcode calculations.



efandango wrote:
John,

Yes, The comma only ever appears after the postcode. (if any errors
throw,
I'll manually reconfigure them)

Can you give me some pointers on how/where to use your formula,
Query/VBA?.
Also, Is it possible to be able to compare both 'From_Pcode' and
'To_Pcode'
in the master table with the 'comma' postcodes in the sub table. or
will it
require two seperate processes?.

Am i making sense? (already my head is spinning...) :)






.

Relevant Pages

  • Re: Periodic Table
    ... hardly notice these bugs, but if the situation were reversed and I ... had to switch from period to comma for certain things to work, ... characters except NUL, ... Actually, with MS Windows 98SE, you can use a comma in a file ...
    (comp.sys.hp48)
  • Re: Is this UTF-8 regular expression semantically correct?
    ... Although Joe's idea of permitting a local comma to be mapped to the ASCII comma seemed plausible, it does not look like this is the way that it is actually done. ... The way that it is actually done in Java is that although there is leeway in the specification of identifiers, everything else must be pure ASCII. ... But if you wish to allow pure native speaking language, ... source file characters accepted is implementation-defined. ...
    (microsoft.public.vc.mfc)
  • Re: Applesoft problem with reading text files
    ... It seems to stop reading a line of text when it encounters ... "The INPUT statement with one variable reads all characters up to the ... first comma to be added to the first variable, ... I personally still search for the SSAVE extra command! ...
    (comp.sys.apple2)
  • Re: Applesoft problem with reading text files
    ... It seems to stop reading a line of text when it encounters ... "The INPUT statement with one variable reads all characters up to the ... first comma to be added to the first variable, ... There's a TYPE command in BASIC? ...
    (comp.sys.apple2)
  • Re: macros
    ... space and then 3 characters. ... Edit / Copy on the rangeand then Edit / Paste Special / Values to fix the ... "Louise" wrote in message ... > I have a worksheet with thousands of postcodes on. ...
    (microsoft.public.excel.worksheet.functions)