Solved Change Field Data adding - between text/numbers

[Number11]

Hi,

I need to find a way to break up a Serial Number of products, currently they are within a table and are in the following format

AAAA201934101BA0068A1

I need to change them all to this layout

AA-AA-20193-41-01-BA-00-68-A1

is this possible

 

[arnelgp]

use left(), mid(), right() functions

 

[arnelgp]

or you can use:

Format([FieldName],"@@\-@@\-@@@@@\-@@\-@@\-@@\-@@\-@@\-@@")

 

[Number11]

or you can use:

Format([FieldName],"@@\-@@\-@@@@@\-@@\-@@\-@@\-@@\-@@\-@@")

Thanks that worked fine with an update query

 

[EzGoingKev]

or you can use:

Format([FieldName],"@@\-@@\-@@@@@\-@@\-@@\-@@\-@@\-@@\-@@")

Thanks for posting that, I am using it to format some data I have. I do have a question though.

I have some data that is like this:

1234567890
123456789012

I am using Format([FieldName], "!@@@-@@@-@@-@@-@@")

I get:

123-456-78-90-
123-456-78-90-12

Is there a way to modify the format so it drops the "-" if the string is only (10) digits in length?

 

[theDBguy]

Thanks for posting that, I am using it to format some data I have. I do have a question though.

I have some data that is like this:

1234567890
123456789012

I am using Format([FieldName], "!@@@-@@@-@@-@@-@@")

I get:

123-456-78-90-
123-456-78-90-12

Is there a way to modify the format so it drops the "-" if the string is only (10) digits in length?

How about something like?

Format([FieldName],IIf(Len([FieldName])=10,"!@@@-@@@-@@-@@","!@@@-@@@-@@-@@-@@"))

 

[EzGoingKev]

How about something like?

Format([FieldName],IIf(Len([FieldName])=10,"!@@@-@@@-@@-@@","!@@@-@@@-@@-@@-@@"))

I had thought about that but I did not know if there was a way to make the last two provisional in the way the format is written.

 

[theDBguy]

I had thought about that but I did not know if there was a way to make the last two provisional in the way the format is written.

Sorry, I'm not sure about that part.