Variable Question 2

[Ollie71]

Not sure if this is a silly question or not but anyone tell me if you can compare and strip txt and numbers from a String Variable.

I have in my Db two different results codes. One for people Hair analysis (a number code like2980..) and one for Misc analysis (could be anything from dust, skin to dog, cow hair, being txt prefix and and a number e.g. C102, Du250 etc..).

The Misc code can come back from the Lab mixed with the People ones and is in one field which I import to a temp table and match it with its record in its table and is therefore a String Field to cater for the txt part of the MiscCode.

However when stored in the 'MiscLabRegister' table it is stored as two seperate parts, Subjectcode (txt part) and the SubjectCodeNumber (number part) and concatenated into one when shown on the forms.

I do this to make it easier to increment the number and choose which txt prefix depending on the type of analysis it is.

I know it would probably be easier to use an array which each element could be manipulated, but I am a novice learning programmer and completely self taught, and arrays are something I havent quite got my head around yet.

But if there is a way I can step through the elements of a variable and strip the identified ones out it would be simple.

If anyone has a an easier way to do the whole thing I would love to hear it. Just bear in mind that the 'Code' can contain both the plain number code and the txt prefix code and that I need the Misc code saved split into the table as the txt part and the number part, all else is subjective.

Heres a visual on the data in case my longwinded explanations are confusing lol.

From lab: Code
2980
2981
C108
DU58
Pure numbers (and associated data) saved to LabRegister Table in one field called 'Code'

Misc Codes (and accociated data) are saved as
'SubjectCode' 'SubjectCodeNumber'
C 108
DU 58

Thanks
Ollie

 

[HarleyQuinn]

To strip numbers and letters from a string you could use one of the functions Remou or I wrote here

There may be better ways to set your form up than this but that's something we could come back to if necessary.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Ollie71]

HarleyQ,

Thanks mate that worked perfectly. You earned your star!

I used Remou's version only because I understood it a little more than your version which im sure would have worked fine as well.

Not sure if there is a better way to achieve what I need but I assigned the 'code' to one each of the remNum and remLetter to return the respective portions back to the two seperate controls on the form and Bingo! there they were split perfectly just as I need them. Saved me heaps of mucking around trying to find a way.

God Microsofts help is as far from that as you could possibly get, thank the powers I stumbled accross this forum.

Thanks again, its good to see the knowledgable people do look and respond so quickly to our newbie posts!

Cheers!
Ollie

 

[HarleyQuinn]

Ollie, glad I could help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

In the interests of completeness and furthering of knowledge I'll give you an explaination of how mine works (via comments )

Function RemNumbers(strS As String)
     Dim re As Object ' object to hold Regular Expression object
    
     Set re = CreateObject("VBScript.RegExp") ' late bind to RegExp object so no need to reference in application
    
     With re
         .Global = True ' find all matches not just first
         .MultiLine = True ' over multiple lines
         .IgnoreCase = True ' whether upper or lower case (more relevant for alpha char matching)
         .Pattern = "[0-9]" ' regular expression for numeric range
         RemNumbers = .Replace(strS, "") ' set return value to value of strS where everything matched by the pattern is replaced with ""
     End With
  
 End Function

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

HarleyQuinn, Thanx for the code. I ripped it to my stash. ;-) ==> *

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HarleyQuinn]

You're welcome Skip

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.