Building Expressions to clean up imported data

[jonmarcr]

Reposting as I forgot to set "notify" on - thanks

This is something I can do with Excel but if results in a 33MB file and the
link tables are just a little slow. I've battled with it for two solid days
and have got nowhere (well I've got to the point where I have half a result
with loads of #Error cells

I import (among other stuff) address fields Add1 Add2 Add3 Add4 Add5 Postcode
Our database expects the City and the County to be in slots Add4 and Add5.
The external database simply fills up fields starting from the left so a
2-line address will have the City in Add2 and a three line address will have
the City in
Add2 and the County in Add3 and so on. My task is to use logic to get
(90-95%) the addresses into the correct fields. I can manage it in Excel
using nested If statements and that is how I have been trying to do it in
Access but I can only get so far - not too bad but the tests that throw up an
empty cell return #Error and that I cannot get rid of.

The second tidy-up is a series of telephone numbers which we want to display
nnn-nnnn-nnnn but the database imports them as nn n - nnnnnnnn or as nnn
nnnn-nnnn or almost any other messy combination. Again in excel this is easy
but I am tearing my hair out in Access

Any ideas please?

 

[Jamie Collins]

I import (among other stuff) address fields Add1 Add2 Add3 Add4 Add5 Postcode
Our database expects the City and the County to be in slots Add4 and Add5.
The external database simply fills up fields starting from the left so a
2-line address will have the City in Add2 and a three line address will have
the City in
Add2 and the County in Add3 and so on. My task is to use logic to get
(90-95%) the addresses into the correct fields.

I don't think you've fully explained your assumptions.

Are we assuming that in order to hit your required success rate a
three line address is usually

Line1 | City | County

and rather than

Line1 | Line2 | City

and that a four line address is usually

Line1 | Line2 | City | County

rather than

Line1 | Line2 | Line3 | City

?

Using the above logic, try

SELECT Add1, IIF(Add3 IS NULL OR Add4 IS NULL, NULL, Add2) AS Add2,
IIF(Add3 IS NULL OR Add4 IS NULL OR Add5 IS NULL, NULL, Add3) AS Add3,
IIF(Add3 IS NULL OR Add4 IS NULL, Add2, IIF(Add5 IS NULL, Add3,
Add4)) AS Add4, IIF(Add4 IS NULL, Add3, IIF(Add5 IS NULL, Add4, Add5))
AS Add5 FROM MyTable;

Jamie.

--

 

[jonmarcr]

Thanks Jamie,

My task is to transform imported data into the correct format for our database
We import
Add1 Add2 Add3 Add4 Add5
But not all the fields are populated
Add1 is always OK Add2 is always populated but the rest may be empty or not
We have fields
Add1 Add2 Add3 City County
If Add5 Add4 and Add3 are "" then: County=Add2 and set Add2=""
If Add5 Add4 are "" then: County=Add3 City=Add2 and set Add3="" Add2=""
If Add5 is "" then: County=Add4 City=Add3 and set Add3="" Add2=Add2

Anyway I get the idea I'll experiment - thanks very much.

 

[jonmarcr]

OK I've tried but failed (!)
It says it finds a circular reference to Add3
Just to make sure I've understood, the expression:
IIF(Add3 IS NULL OR Add4 IS NULL, NULL, Add2) AS Add2
Means If Add3="" or Add4="" then "" otherwise Add2 and call this Add2
and so on? I ought to be able to work it out from there.

 

[jonmarcr]

Thanks very much - it has now worked.
For some reason entering it via the expression builder using add1 add2 ect
as expr worled, trying to enter it as an SQL statement kept on throwing up
errors.

Thanks again.