data formatting in access 2003 HELP!!

valiHey everyone,

I have a database in access 2003, with one column with data entered in the
following formats:

1_2_101_9, 5_0_1, 4_0_10_1 and 1_2

All the data is entered as text.

Thing is I want to display the data so the part after the 3rd underscore
does not show. ie instead of 4_0_101_15, it displays as 4_0_101

Can't use validation as the amount of numerals in the third section is
varialble: having a minimum of one digit and a maximum of 5.

All I need is the first three sets of numerals to dispaly not the fourth.

Any ideas gratefully received, the more idiot proof the better as I am a
beginner.

Thanks in advance,

N

There are many ways to delete (or not show) data or sections of. Just a bit
confused as to way you have this type of "text". If you could offer some
info it would be helpful.

An obvious idea would be to type the "numbers" into a field and then use a
query to add them into the format that you need - more info needed ??



"NKL" wrote:

valiHey everyone,

I have a database in access 2003, with one column with data entered in the
following formats:

1_2_101_9, 5_0_1, 4_0_10_1 and 1_2

All the data is entered as text.

Thing is I want to display the data so the part after the 3rd underscore
does not show. ie instead of 4_0_101_15, it displays as 4_0_101

Can't use validation as the amount of numerals in the third section is
varialble: having a minimum of one digit and a maximum of 5.

All I need is the first three sets of numerals to dispaly not the fourth.

Any ideas gratefully received, the more idiot proof the better as I am a
beginner.

Thanks in advance,

N

You can't achieve this by formatting a field or textbox; you'll need to
use a VBA function that takes your strings and returns just the bit you
want.

One way to do this would be to write VBA code that uses functions such
as InStr() and Left().

I'm lazy, so would instead probably use a regular expression with the
rgxExtract() function at
http://www.j.nurick.dial.pipex.com/C...rgxExtract.htm

This should do the job:

rgxExtract([MyField], "^((?:[^_]*_){0,2}[^_]*)")



On Wed, 8 Feb 2006 07:11:33 -0800, NKL
wrote:

valiHey everyone,

I have a database in access 2003, with one column with data entered in the
following formats:

1_2_101_9, 5_0_1, 4_0_10_1 and 1_2

All the data is entered as text.

Thing is I want to display the data so the part after the 3rd underscore
does not show. ie instead of 4_0_101_15, it displays as 4_0_101

Can't use validation as the amount of numerals in the third section is
varialble: having a minimum of one digit and a maximum of 5.

All I need is the first three sets of numerals to dispaly not the fourth.

Any ideas gratefully received, the more idiot proof the better as I am a
beginner.

Thanks in advance,

N

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

This looks like a good place to use the "split" function
Split(expression[, delimiter[, limit[, compare]]])

Split(FieldTxt,"_",n) will return each bit between the underscores.

Ed Warren

"NKL" wrote in message
...
valiHey everyone,

I have a database in access 2003, with one column with data entered in the
following formats:

1_2_101_9, 5_0_1, 4_0_10_1 and 1_2

All the data is entered as text.

Thing is I want to display the data so the part after the 3rd underscore
does not show. ie instead of 4_0_101_15, it displays as 4_0_101

Can't use validation as the amount of numerals in the third section is
varialble: having a minimum of one digit and a maximum of 5.

All I need is the first three sets of numerals to dispaly not the fourth.

Any ideas gratefully received, the more idiot proof the better as I am a
beginner.

Thanks in advance,

N

Note of caution: If this data is manually entered, I will bet it has errors
and will not parse properly.

Ed Warren.

"Ed Warren" wrote in message
...
This looks like a good place to use the "split" function
Split(expression[, delimiter[, limit[, compare]]])

Split(FieldTxt,"_",n) will return each bit between the underscores.

Ed Warren

"NKL" wrote in message
...
valiHey everyone,

I have a database in access 2003, with one column with data entered in
the
following formats:

1_2_101_9, 5_0_1, 4_0_10_1 and 1_2

All the data is entered as text.

Thing is I want to display the data so the part after the 3rd underscore
does not show. ie instead of 4_0_101_15, it displays as 4_0_101

Can't use validation as the amount of numerals in the third section is
varialble: having a minimum of one digit and a maximum of 5.

All I need is the first three sets of numerals to dispaly not the fourth.

Any ideas gratefully received, the more idiot proof the better as I am a
beginner.

Thanks in advance,

N