Removing duplicate characters in text field and arranging characte

  • Thread starter Thread starter Kelvin Leong
  • Start date Start date
K

Kelvin Leong

Hi there,

I have a field that stores 4 digit numbers as text in a field. I would like
to remove the duplicate digits in a query so that it can be used in a report.
I would also want to maintain the original numbers in the field. The
following are examples of number and the result after filtering:

2929 => 29
1311 => 13
0230 => 230
4121 => 124

Noticed that the number 0 is put last. How can I achieve the above result?
Do I need to create a public function? if so, how can it be done?

Thanks
 
Hi,
yes, it should be a public function, I think you need to create a loop there
to get chars one by one, for each char you check if it exists in previous
chars, if yes - then skip it from result and so on

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
On Tue, 4 Nov 2008 01:50:01 -0800, Kelvin Leong

That does not seem like the best way to store that data. Can you tell
us the broader context so we may be able to suggest a better solution?

-Tom.
Microsoft Access MVP
 
I am guess here
Public Function Return_unique_numbers(str_source As String)
Dim bln_1 As Boolean, bln_2 As Boolean, bln_3 As Boolean, bln_4 As Boolean,
bln_5 As Boolean, bln_6 As Boolean, bln_7 As Boolean, bln_8 As Boolean, bln_9
As Boolean, bln_0 As Boolean
Dim lng_Count As Long, lng_Temp As Long, str_Result As String
bln_1 = False
bln_2 = False
bln_3 = False
bln_4 = False
bln_5 = False
bln_6 = False
bln_7 = False
bln_8 = False
bln_9 = False
bln_0 = False
lng_Count = Len(str_source)
For lng_Temp = 1 To lng_Count
Select Case Mid(str_source, lng_Temp, 1)
Case "1"
bln_1 = True
Case "2"
bln_2 = True
Case "3"
bln_3 = True
Case "4"
bln_4 = True
Case "5"
bln_5 = True
Case "6"
bln_6 = True
Case "7"
bln_7 = True
Case "8"
bln_8 = True
Case "9"
bln_9 = True
Case "0"
bln_0 = True
End Select
Next
If bln_1 Then str_Result = "1"
If bln_2 Then str_Result = str_Result & "2"
If bln_3 Then str_Result = str_Result & "3"
If bln_4 Then str_Result = str_Result & "4"
If bln_5 Then str_Result = str_Result & "5"
If bln_6 Then str_Result = str_Result & "6"
If bln_7 Then str_Result = str_Result & "7"
If bln_8 Then str_Result = str_Result & "8"
If bln_9 Then str_Result = str_Result & "9"
If bln_0 Then str_Result = str_Result & "0"
Return_unique_numbers = str_Result
End Function
 
Hi Linq,

Well, the idea is that in a 4 digit number, no matter how the numbers are
arranged in the 4-digit number, I can 'simplify' it to show the a number
without the duplicate digits in, and each digit is in ascending order, with
the exception of 0 at the back. For example,

Case of numbers with 0;
0000 => 0
0001 => 10

Case of numbers 9930, 3930, 3390, 0939, 9003, etc, these numbers become 390.
It cannot be 309, since the exception is that digit 0 is at the back.
Case of numbers 3141, 1334, 3144, 1341, 1434, etc, these numbers become 134.

From there on, I can probably group these numbers to see whether it will end
up a single-digit, double-digit, triple-digit or quadruple-digit number.
Examples, 0000, 1111, 2222, 3333, 4444, 5555, etc, will end up as a
single-digit number.

In the case of 1333, 3311, 1311, 1313, 3131, will end up as 13 (double-digit)

After I get this right, my objective is to 'simplify' my input
(4Digit_Field) and in my respective listing of either single-digit,
double-digit, triple-digit or quadruple-digit number , I can assign the
'simplified' number to the listing:

Single-Digit Number Listing:

No. | Count
--------------
0 | 1
1 | - (for the case of non)
2 |
3 |

Double-Digit Number listing:

No. | Count
-------------
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
10 |
23 | (21 is not in there because it will be 12, and 22 will become 2)

I hope that you will be able to understand my explanation.

Thanks.
 
Hi Eric,

Thanks for your time figuring it out. As explained to Linq in his reply, the
idea is that in a 4 digit number, no matter how the digits are arranged in
the 4-digit number, I can 'simplify' it to show the a number without the
duplicate digits in, and each digit is in ascending order, with the exception
of 0 at the back. For example,

Case of numbers with 0;
0000 => 0
0001 => 10

Case of numbers 9930, 3930, 3390, 0939, 9003, etc, these numbers become 390.
It cannot be 309, since the exception is that digit 0 is at the back.
Case of numbers 3141, 1334, 3144, 1341, 1434, etc, these numbers become 134.

From there on, I can probably group these numbers to see whether it will end
up a single-digit, double-digit, triple-digit or quadruple-digit number.
Examples, 0000, 1111, 2222, 3333, 4444, 5555, etc, will end up as a
single-digit number.

In the case of 1333, 3311, 1311, 1313, 3131, will end up as 13 (double-digit)

After I get this right, my objective is to 'simplify' my input
(4Digit_Field) and in my respective listing of either single-digit,
double-digit, triple-digit or quadruple-digit number , I can assign the
'simplified' number to the listing:

Single-Digit Number Listing:

No. | Count
--------------
0 | 1
1 | - (for the case of non)
2 |
3 |

Double-Digit Number listing:

No. | Count
-------------
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
10 |
23 | (21 is not in there because it will be 12, and 22 will become 2)

I hope that you will be able to understand my explanation.

I will try your suggestion to see I can achieve something.

Thanks again.
 
Hi Eric,

Thanks for your time figuring it out. As explained to Linq in his reply, the
idea is that in a 4 digit number, no matter how the digits are arranged in
the 4-digit number, I can 'simplify' it to show the a number without the
duplicate digits in, and each digit is in ascending order, with the exception
of 0 at the back. For example,

Case of numbers with 0;
0000 => 0
0001 => 10

Case of numbers 9930, 3930, 3390, 0939, 9003, etc, these numbers become 390.
It cannot be 309, since the exception is that digit 0 is at the back.
Case of numbers 3141, 1334, 3144, 1341, 1434, etc, these numbers become 134.

From there on, I can probably group these numbers to see whether it will end
up a single-digit, double-digit, triple-digit or quadruple-digit number.
Examples, 0000, 1111, 2222, 3333, 4444, 5555, etc, will end up as a
single-digit number.

In the case of 1333, 3311, 1311, 1313, 3131, will end up as 13 (double-digit)

After I get this right, my objective is to 'simplify' my input
(4Digit_Field) and in my respective listing of either single-digit,
double-digit, triple-digit or quadruple-digit number , I can assign the
'simplified' number to the listing:

Single-Digit Number Listing:

No. | Count
--------------
0    |    1
1    |    - (for the case of non)
2    |
3    |

Double-Digit Number listing:

No. | Count
-------------
12  |
13  |
14  |
15  |
16  |
17  |
18  |
19  |
10  |
23  |  (21 is not in there because it will be 12, and 22 will become 2)

I hope that you will be able to understand my explanation.

I will try your suggestion to see I can achieve something.

Thanks again.






- Show quoted text -
Click to expand...

This is a little shorter.

Public Function FindDups(strDigits As String) As String
Dim intArrDups(9) As Integer, intPos As Integer
For intPos = 1 To 4
intArrDups(Val(Mid(strDigits, intPos, 1))) = 1
Next
For intPos = 1 To 9
FindDups = FindDups & IIf(intArrDups(intPos), CStr(intPos), "")
Next
FindDups = FindDups & IIf(intArrDups(0), "0", "")
End Function

Hope this helps,
Chris M.
 
Back
Top