Query Expression Builder

[jonmarcr]

I cant find my post on this and my workstation hung last time - perhaps it
didn't post.

I am trying to transform data I am importing into data that can be read by
my users. I have two issues:

The external database sends me info in the form Add1 Add2 Add3 Add4 Add5 but
our database (which I cannot change) has Add1 Add2 Add3 City County

Add1 is always OK
Add2 to Add5 should be moved either 3 2 or 1 space depending on how many
fields out of Add2 to Add5 are populated. This is easy in Excel but I cannot
get it to work in Access when I use IIf to test for a null or an empty field
I get #Error which then propagtes throughout the database.

Maybe someone has already done this?

The second is telephone number which come in in a confused format:
nnn-nnnnnnnn or nn n nnn-nnnn or nnn-nnnn-nnnn - you get the idea. What
comes in I need to format it to read nnn-nnnn-nnnn - easy in Excel but.

My excel sheet (I then use link tables) is enormous because I have to design
to import up to 10,000 records so I reach 33MB - that is why I prefer to use
Access if I can

Help! - I've been at it continuously for three days

 

[Ken Snell [MVP]]

Can you post some examples of the "incoming" data and how the data should
look when it's in your table? Your description isn't clear about what you
mean by "3 2 or 1 space".

As for the phone number, I suggest that you read it in as sent, strip the
spaces and hyphens from it, and then insert your own hyphens. This can be
done by importing the data into a table and then modifying the data either
by an update query (if you're importing directly into the final table, which
is not recommended); or by an append query when you copy the data from the
temporary table to the permanent table.

 

[jonmarcr]

Hi Hen,
My earlier post did post after all and someone has been able to help. Using
expression builder instead of SQL directly I have managed to get close to
what I need and a little more work will sort it out.

For the telno - I don't understand how to strip the spaces - unless it is by
laboriously setting up 13 or 14 expressions one for each possible position of
a number space or hyphen and then ... That seems the paiful way of doing it.

 

[Ken Snell [MVP]]

If you're using ACCESS 2000 or later version, you can use the Replace
function to do this easily. In the following example, I'm using it twice to
strip both the spaces and hyphens and replace with empty string:

TelNumber = Replace(Replace(TelNumber, " ", "", 1, -1, 1), "-", "", 1, -1,
1)

 

[jonmarcr]

Thanks very much!
It is stupidly obvious !!!

If you're using ACCESS 2000 or later version, you can use the Replace
function to do this easily. In the following example, I'm using it twice to
strip both the spaces and hyphens and replace with empty string:

TelNumber = Replace(Replace(TelNumber, " ", "", 1, -1, 1), "-", "", 1, -1,
1)