Extracting and sorting

  • Thread starter Thread starter Leif Thorsen
  • Start date Start date
L

Leif Thorsen

My question nr 1:In a question I have a concated field containing
"Surname"&"Forname"&"IDnr" like "BushGeorge333". Is ther a way to extract
"333" and "BushGeorge" into two separate new fields in the same question ??

My question nr 2: In a report I have a "DLookUp-field".
Is there a way to sort and/or group the data in the report on this field ??

Thank You in advance because I hope ther is a solution to my problems says

Leif Thorsen
 
Hi Lief

If the IDnr is fixed width of three just take the namepiece as
left(concatedfld,len(concatedfld)-3)
and the IDnr as right(concatedfld,3). If the IDnr length varies then write
a simple function to walk the concatedfld until you hit a numeric and use
that position to perform the left and right functions as above.

Regards

Kevin
 
My question nr 1:In a question I have a concated field containing
"Surname"&"Forname"&"IDnr" like "BushGeorge333". Is ther a way to extract
"333" and "BushGeorge" into two separate new fields in the same question ??

My question nr 2: In a report I have a "DLookUp-field".
Is there a way to sort and/or group the data in the report on this field ??

Thank You in advance because I  hope ther is a solution to my problems says

Leif Thorsen
Click to expand...

Is the data you are describing in your table? If it is, you should not
have a field in your database designed this way. The rule is "One
field, one piece of data". I'm sure you can see the reason behind this
now, getting at the individual pieces of data is difficult. You should
split that field up into three pieces and store them in three
different fields; firstname, lastname and idnr. Now as to your
problem, whether or not you can easily split that up depends on the
makeup of IDnr. Is it always three characters?

If the data is concatenated in a query, but the table stores the three
pieces separately, then you can just get the data direct from the
table.

Keven Denen
 
Thank You for Your tip. Yhe IDnr has not a fixed length so - my problem is
that I don´t know how this function shall be written. Can You give it to me
???
 
Thank You for Your comment. The reason why I have a field as I described is
that I have placed this three parts in a field by using a combo-box and I
have designed the field content just for getting a godd way of sorting on
just this field. The three parts in the field comes from a list af members
and if two members should have the same name there will be a wrong sorting
but by adding the IDnr into the field this field will always be unique. So
how to solve to split this field into letters and digits when the IDnr
doesn´t have a fix length ???

I will be very greatfull if You have a good idea

Best regards from

Leif
 
Lief,

Try something like this:

Sub NumeralTest()
Dim Which As Integer
Which = NumeralStart("abcdf980")
Debug.Print Which
End Sub

Function NumeralStart(strIn As String)
Dim strPosition As Integer
strPosition = 1
Do While strPosition <= Len(strIn)
If IsNumeric(Mid(strIn, strPosition)) Then
NumeralStart = strPosition
Exit Do
Else
strPosition = strPosition + 1
End If
Loop
End Function

Regards

Kevin
 
Thank You for Your comment. The reason why I have a field as I described is
that I have placed this three parts in a field by using a combo-box and I
have designed the field content just for getting a godd way of sorting on
just this field.
Click to expand...

Are you aware that you can sort a query on up to TEN fields? You certainly do
NOT need to concatenate three fields into one in order to do a sort or search.
 
Thank You for Your comments.

As I can see from Your answer I havn´t been clear enough in my question.

The concated field in is one table and is by a combobox placed i onother
table in which I want to extract the three parts.

I hope this information gives You an idea what I want to do.

Do You have a solution ??

Leif Thorsen
 
John W. Vinson said:
Are you aware that you can sort a query on up to TEN fields? You certainly do
NOT need to concatenate three fields into one in order to do a sort or search.
Click to expand...
 
Thank You for Your comments.

As I can see from Your answer I havn´t been clear enough in my question..

The concated field in is one table and is by a combobox placed i onother
table in which I want to extract the three parts.

I hope this information gives You an idea what I want to do.

Do You have a solution ??

Leif Thorsen
Click to expand...

To get at the ID, use right(field,3). This pulls the last three
characters of the field. To get the name portion, use left(field,len
(field)-3). Getting first and last separated is impossible from the
example you showed, nothing to determine where the break is.

Keven Denen
 
Thank You for Your tip.
Sorry to say but i don't know where to write in this code because I am not
so good in Access.
I have tried to use it in my report and I have tried to put in a new field
in my question but I won´t work. I presume that the field You have named
"abcdf980" is my concated field with "SurnameFornameIDnr".

Can You help me ???
 
Thank You for Your comments.

As I can see from Your answer I havn´t been clear enough in my question.

The concated field in is one table and is by a combobox placed i onother
table in which I want to extract the three parts.

I hope this information gives You an idea what I want to do.

Do You have a solution ??
Click to expand...

Ideally, if you have any control over the other table, *don't concatenate it
in the first place*. Storing three facts in one text string is a bad idea, for
precisely the reason you're seeing: you have to go to extra work to pick it
apart again.

If you must, and if the ID number is of arbitrary length, you'll need to write
some VBA code to extract the trailing numeric substring. Untested air code,
needs error checking etc.

Public Function TrailingID(strIn As String) As Long
Dim strNum As String
strNum = ""
Dim iPos As Integer
For iPos = Len(strIn) To 1 Step -1
If IsNumeric(Mid(strIn, iPos, 1)) Then
strNum = Mid(strIn, iPos, 1) & strNum
Else
Exit For
End If
Next iPos
If Len(strNum) > 0 Then
TrailingID = Val(strNum)
Else
TrailingID = 0
MsgBox "No ID found for " & strIn
End If
End Function
 
Thank You for Your tip !

I will try to use it the day I know where to put it i.e. shall I have the
VBA-code in a question for invent a new field or shall I have the code in a
report ??
I am not so good in VBA so please tell me where to put the code

Best regards

Leif Thorsen
 
John W. Vinson said:
Ideally, if you have any control over the other table, *don't concatenate it
in the first place*. Storing three facts in one text string is a bad idea, for
precisely the reason you're seeing: you have to go to extra work to pick it
apart again.

If you must, and if the ID number is of arbitrary length, you'll need to write
some VBA code to extract the trailing numeric substring. Untested air code,
needs error checking etc.

Public Function TrailingID(strIn As String) As Long
Dim strNum As String
strNum = ""
Dim iPos As Integer
For iPos = Len(strIn) To 1 Step -1
If IsNumeric(Mid(strIn, iPos, 1)) Then
strNum = Mid(strIn, iPos, 1) & strNum
Else
Exit For
End If
Next iPos
If Len(strNum) > 0 Then
TrailingID = Val(strNum)
Else
TrailingID = 0
MsgBox "No ID found for " & strIn
End If
End Function
Click to expand...
 
Thank You for Your tip !

I will try to use it the day I know where to put it i.e. shall I have the
VBA-code in a question for invent a new field or shall I have the code in a
report ??
I am not so good in VBA so please tell me where to put the code
Click to expand...

Put the code in a public Module, and use it either in a query by typing

TheID: TrailingId([fieldname])

or in a textbox on a form or report using

=TrailingId([fieldname])

as the Control Source of the textbox. Replace fieldname with the actual field
name in your table of course.
 
Thank You once again !!

Now I will use it and have it to solve my problem.

Have a good day and maybe we will meet again in this forum when I hav
another problem

Leif
 
Leif Thorsen said:
Thank You for Your tip !

I will try to use it the day I know where to put it i.e. shall I have the
VBA-code in a question for invent a new field or shall I have the code in a
report ??
I am not so good in VBA so please tell me where to put the code

Best regards

Leif Thorsen
Click to expand...
 
Back
Top