Removing duplicate characters in text field and arranging characte

[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

 

[Alex Dybenko]

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

 

[Tom van Stiphout]

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

 

[Eric Gerds]

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

 

[Kelvin Leong]

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.

 

[Kelvin Leong]

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.

 

[mcescher]

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 -

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.