clean function within access

[dogpigfish]

I pull a table from a large database into access. I have multiple fields in
one field. It translates better into excel because it puts all text items in
different rows all linked to the same record (alt-enter). Within access
these multiple fields are listed in one row separated by a square looking
symbol. How do I clean this out of the fields (by deleting the square
symbols)?

 

[Jeff Boyce]

If you are asking how you can stuff multiple "facts" into a single field,
reconsider. As a relational database, Access offers a better way than you
can come up with in Excel.

Having multiple potential values suggests that you have a one-to-many
relationship. If you want to get the best use of Access' features and
functions, you need to model your data with normalization in mind, not the
limitations of Excel.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dogpigfish]

The Database we are pulling from doesn't recognize a one-to-many relationship
and therefore throws several fields into one field. I am attempting to break
the text out using automation within access (and eventually create
relationships). Within excel a user would use the function =clean() to take
out all unnecessary text items. I want to eliminate the square boxes
(alt-enter) that show up in my table, however am not sure how to do it. What
function in access would I use and can it be done?

 

[John Spencer]

Take a look at the replace function.

I'm not sure what character is represented by the square, but you should be
able to get its Ascii using the ASC function in the immediate window of the
vba.

?Asc(Paste the character here)

Then you can use an update query to replace all the characters.

 

[Marshall Barton]

The Database we are pulling from doesn't recognize a one-to-many relationship
and therefore throws several fields into one field. I am attempting to break
the text out using automation within access (and eventually create
relationships). Within excel a user would use the function =clean() to take
out all unnecessary text items. I want to eliminate the square boxes
(alt-enter) that show up in my table, however am not sure how to do it. What
function in access would I use and can it be done?

If you are going to use individual values from the Excel
"lists", then listening to Jeff will make things a lot
easier to work with. Using the Split function in a VBA
procedure to add records to the many side table is
relatively straightforward.

If all you want to do is get the same effect in Access,
then Replace the Lf (ascii 10) with CrLf (ascii 13 and 10):
Replace(thefield, vbLF, vbCrLF)