Trim how to?

[knowledge76]

I am comparing two fields in a query like

[table1].[Prefix]=right([table2].[Prefix],3))

the prefix in table A look like AB123 or A123 or AC123 and in table2 like 1234. HOw can I the trim beginning if it is A,AB or AC? Any suggestions?
Thanks for your answers.

 

[DCrake]

Simple Software Solutions

Are you attempting to extract the numeric value from the string or a specific number of characters?

If it is the former you may need to have a function that reads the string from left to right and testing for numerics, such as:

Public Function DropAlpha(AnyCode As String) As Long

Dim sStr As String

For i = 1 To Len(AnyCode)
     sStr = Mid(AnyCode,i)
     If IsNumeric(sStr) Then
          Exit For
     End If
Next i

DropAlpha = CLng(sStr)

End Function

This is aircode and has not been tested

CodeMaster:

 

[knowledge76]

I just wanna trim the A,AC and AB at the beginning for comparison. Cant I implement it without using your code? I mean within query using similar function like RIGHT, but in my case it is not the solution.

 

[DCrake]

Simple Software Solutions

Question? what is the opposite to Right?
Answer? Left
Question? what comes between Left and Right
Answer Mid

You have not explained as to whether you want to keep the leg and throw away the body or to keep the body and throw away the leg.

 

[knowledge76]

I am using the following expression in my query to get rid of AB in the beginning

mid([Prefix],(InStr(1,[Prefix],"AB")+2)) AS expression1
with this expression i am not covering the AC case when it comes before the number. Can I somehow bind the AC condition in my above sql statement?
Thanks

 

[DCrake]

Simple Software Solutions

If you use the function I provided earlier you can get it drop any prefix whether it be A or AB or AC or ABCDE

Example

X: DropAlpha("ABC123")

= 123

X: DropAlpha("XZY111")

= 111

 

[knowledge76]

As I am not a expert, I am asking you how to implement your function in my database?

 

[Mike375]

You can't use Right/Left as normal because it won't know where to start. If you had something line AB 123 or ABC,123 or ABC-123 or ABC()123 then you can include the , or space or dash or ( or ) with InStr. But for a number you need a function.

Paste the following into a module (or DCrake's or anyone else who joins the party). Then in a query add a calculated field and call it whatever you like. Putting FinddNum() or DCrake's DropAlpha() is just like doing Right() or Left() etc except those functions come built into Access whereas the others have to be made.

YourNewFieldName:FindNum([The FieldName that has the ABC123 etc]) and that will give the numbers at the end of the string.

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer

For i = 1 To Len(strName)

strTemp = Mid(strName, i, 1)

If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 122 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function

 

[knowledge76]

Thanks a lot it worked. Before we close talking about this thread I just want to ask you experts whether in the same way I can get the result for other string where the case is different not AB123, AC 123,A123 but something like
AB123FF and I want to trim just the AB. Can I modify the code regarding this.

 

[DCrake]

Simple Software Solutions

Good to see you have arived at a solution, you don't say which one you are using?

However, using the same logic if you want to return everything from the first numeric digit encountered then you would have to step through the string 1 character at a time until you hit something between 0 and 9. once you hit that position then everything from that position would be returned.

Is there some reason why your codes have differing masks? do you have any control over this?

 

[Mike375]

The code i put up would produce a null for AA123FF and the clue is here

http://www.alanwood.net/demos/ansi.html

This list the ANSI numbers for different characters, That is what the Asc is about in the code.

Similar code to the above could split an entry like AndrewSmith because you grap the capital S since it has a different Asc to lower case s. You will see from the link that lower and upper case is a different number for the same character.

If you want to play around with field splitting then search about on InStr, Len, LTrim.

Len is very useful as it gives the count for characters in a string. As an example, take your ABC12454. The above code grabs the 12454 which is 5 characters. The ABC12454 itself is 8 characters. Thus the difference between Len() on the original field and the Len() on the new field is 3, which tells us the first set of characters is 3 and we know they must be the Alpha characters. Thus that will allow us to use Left() and get the ABC into its own field.

 

[knowledge76]

I am using your code and I have no control over the different masks,unfortunately.

 

[DCrake]

Simple Software Solutions

CODE]Public Function DropAlpha(AnyCode As String) As Long

Dim sStr As String

For i = 1 To Len(AnyCode)
sStr = Mid(AnyCode,i)
If IsNumeric(sStr) Then
Exit For
End If
Next i

DropAlpha = CLng(sStr)

End Function[/CODE]

The above works for extracting the remaining numeric value

CODE]Public Function DropLetters(AnyCode As String) As Long

Dim sStr As String

For i = 1 To Len(AnyCode)
sStr = Mid(AnyCode,1)
If IsNumeric(sStr) Then
Exit For
End If
Next i

DropLetters = CLng(sStr)

End Function[/CODE]

This function enumerates from left to right to fiind a number if it finds one then from that position on will be returned by the function whether it contains numbers only or numbers and letters

 

[knowledge76]

thanks for your help. really appreciate that

 

[Brianwarnock]

Dropletters will fail.

1 Dropletters is a Long and cannot therefore contain alpha.
2 This sStr = Mid(AnyCode,1) should be
sStr = Mid(AnyCode,i,1)
3 DropLetters = CLng(sStr) should be
Dropletters = Mid(AnyCode,i)

I wonder how much testing of code given to them posters do.

Knowledge did you read my comment on your earlier Trim post?

Brian

 

[DCrake]

The comment in the previous post was correct in that there was an error in the function DropLetters, however, as with many respondents, solutions are often written in aircode (untested) and are a representation of a solution.

Unless it it warranted and the OP is really struggling I tend not to validate the code and usually indicate this by referriing to aircode or brevity. In this case I did not. Having said all that the following has been tested and is syntactically correct.

Public Function DropLetters(AnyCode As String) As Long

Dim sStr As String

For i = 1 To Len(AnyCode)
sStr = Mid(AnyCode, i)
If IsNumeric(sStr) Then
    DropLetters = CLng(sStr)
    Exit Function
End If
Next i


End Function

CodeMaster:

 

[Brianwarnock]

But that is now equivalent to your dropalpha.

Please note that I was not criticising you, Codemaster, I too give untested air code, and with this poster i too developed a solution as he changed the ground rules resulting in a careless error, hence my comment to refer to his previous post, but he never comes back and doesn't seem to learn. I shall ignore him in the future.

Brian