Microsoft access

[Guest]

I would like to know how to modify one of my fields in ms access table using
a query. i do not know much about ms access, so i will really appreciate your
help.
Thank you.

From
010-0000-000-000-000000-1205
010-0000-000-000-000000-1205-001
010-0000-000-000-000000-1205-000-001

To
010-0000-000-000-000000-1205-000-000
010-0000-000-000-000000-1205-001-000
010-0000-000-000-000000-1205-000-001

 

[John W. Vinson]

I would like to know how to modify one of my fields in ms access table using
a query. i do not know much about ms access, so i will really appreciate your
help.
Thank you.

From
010-0000-000-000-000000-1205
010-0000-000-000-000000-1205-001
010-0000-000-000-000000-1205-000-001

To
010-0000-000-000-000000-1205-000-000
010-0000-000-000-000000-1205-001-000
010-0000-000-000-000000-1205-000-001

So if the text is 28 characters long append -000-000, or if it's 32 characters
long append -000? Are there other variants?

Back up your database first, just in case; then run an Update query like:

UPDATE yourtable
SET yourtable.yourfield = [yourtable].[yourfield] & Switch(Len([yourfield]) =
28, "-000", Len([yourfield]) = 32, "-000-000", True, "")
WHERE Len([yourfield]) >= 28

I would suggest that, if the different components of this field have some sort
of independent meaning, that you consider splitting it into as many as eight
fields. It's much easier to concatenate different fields together for display
purposes than to split them out and search or sort them when they're all
munged together!


John W. Vinson [MVP]

 

[Joseph Meehan]

John W. Vinson wrote:
...

I would suggest that, if the different components of this field have
some sort of independent meaning, that you consider splitting it into
as many as eight fields. It's much easier to concatenate different
fields together for display purposes than to split them out and
search or sort them when they're all munged together!


John W. Vinson [MVP]

Listen to John. Trust me on this one.