If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
data formatting in access 2003 HELP!!
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 |
#3
|
|||
|
|||
data formatting in access 2003 HELP!!
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. |
#4
|
|||
|
|||
data formatting in access 2003 HELP!!
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 |
#5
|
|||
|
|||
data formatting in access 2003 HELP!!
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Macro to Copy & Paste | [email protected] | Worksheet Functions | 0 | December 1st, 2005 01:56 PM |
Advice needed: Should we upgrade MS Access 2000? And if so to what? | ship | General Discussion | 43 | November 30th, 2005 10:58 PM |
Ambiguous Name Error | pm | Using Forms | 10 | June 5th, 2005 09:19 PM |
What is the difference between 2002 and 2003? | Red Sonya | General Discussion | 2 | March 1st, 2005 05:10 AM |
is Access 2003 any better than XP? | Gorb | General Discussion | 4 | November 11th, 2004 09:44 PM |