459,695 Members | 1,601 Online
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

# VBCode Help for Beginner

 P: n/a Okay, this is probably real simple stuff for you guys. I know its simple, I used to know some of this stuff when I was learning Visual Basic in school, but yeah. Only a couple of things. Firstly, I want to know how to extract an integer from a string. For example, I would have a string, that says perhaps "LGK1111", and I want to just extract the 1111 and put it in an integer variable. The second is how to get the number of records in a table and stick it in a variable, I kinda want to do one of those "For i = 1 to ThingForNumberOfRecords". If I could get the simplest ways of doing this with VBCode I would be very greatful! Thank you, Tom Keane Nov 13 '05 #1
5 Replies

 P: n/a >For example, I would have a string, that says perhaps "LGK1111 Actually, the above can be quite difficult if you don't define the above problem. Is there ALWAYS going to be 3 letters that we must first skip, or what about: 1abc2def3 Do we want the above to return 123? As you can see, this kind of problem can get quite tricky quite fast. I would suggest that we simply first build a function that REMOVES all non numbers, and THEN we convert to a integer. Thus our code could go: dim intResult as integer dim strNumberPart as string strNumberPart = OnlyNumbers("LGK1111") intResult = strNumberPart ms-access will in fact convert the string number to a integer number when you "stuff" the above into the intResult variable. Of course, we still need to define our custom function that only returns numbers. And, I want to STRESS that if we ALWAYS knew that the first 3 digits would be letters, then we could dump the function and use: strNumberPart = mid("LGK1111",4) intResult = strNumberPart However, here is a custom funciton we can place in a stardard module, and use it anywhere in the appction: Public Function OnlyNumbers(myphone As String) As String Dim i As Integer Dim mych As String OnlyNumbers = "" If IsNull(myphone) = False Then For i = 1 To Len(myphone) mych = Mid\$(myphone, i, 1) If InStr("0123456789", mych) > 0 Then OnlyNumbers = OnlyNumbers & mych End If Next i End If End Function The second is how to get the number of records in a table and stick it in a variable You can go dim lngReocrdsInTable as long lngReocrdsInTable = dcount("*","NameOfTable") -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal Nov 13 '05 #2

 P: n/a > strNumberPart = mid("LGK1111",4) intResult = strNumberPart Yes, the String in question always has the same three letters at the beginning. I actually found similar code on the net. Thanks though ;) The second is how to get the number of records in a table and stick it in a variable You can go dim lngReocrdsInTable as long lngReocrdsInTable = dcount("*","NameOfTable") Thanks alot for that. But perhaps you could help me further if I elaborate a little bit. I have two tables, one is called "Jobs" and the other is "Customer Details". Customer Details is linked to Jobs thru a primary key in Jobs called "JobNo". Anyways, I have a form that displays each job, and for each record it displays the records in Customer Details (as a subform) specific to the job number. So, say I went to JobNo 500, it would display records for say 10 people. There is a field in Customer Details called "WCCertNo". Each certificate number, when entered, is in the format "LGKxxxx" where "x" are numbers. I have a command button in the Jobs form called cmdAllocate and what I want to do is to go through each record in Customer Details to find the highest number value in WCCertNo, so I can then go and allocate new numbers for the job I have selected. I hope that makes sense. Your help would be GREATLY appreciated ;) Thank you, Tom Nov 13 '05 #3

 P: n/a One simple way is to use two fields. Have one field as text and the other as an autonumber. Whenever you display the fields in a form or report combine them to give the illusion it is one text field. Simpler Less Coding Less maintenance GJ Nov 13 '05 #4

 P: n/a So, what you will do is put the following code in the sub-forms ON insert event: Also, make sure the WCCertNo has a index on it. The code we will place in the on-insert event will automatically set the next number.... dim strMax as string dim lngMax as long strMax = dmax("WCCertNo","[customer details"]) lngmax = right(strmax,4) lngmax = lagmax + 1 strMax = "LGK" & lngmax me!WCCertNo = strMax The above code will thus automatic enter the WCCertNo if there is not one. All you have to do is start typing in the sub-form,a nd the WCCertNo will appear. It should be noted if this is/will be a multi-user applcaiton, then the above code would need to be moved to the before udpate event, and the WCCertNo will not (can not) appear untill the user is done working on that reocrd. (the reason for this is that two users working would get the same number, as the current roecrd has NOT been saved.).. Anway, give the above code and the on-insert idea a try.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal Nov 13 '05 #5

 P: n/a "celtic_kiwi" wrote in message news:11**********************@g14g2000cwa.googlegr oups.com... One simple way is to use two fields. Have one field as text and the other as an autonumber. Whenever you display the fields in a form or report combine them to give the illusion it is one text field. Simpler Less Coding Less maintenance I have to 100% agree with the above, however, while it is less work, it is a (bad) mistake to use any kind of auonumber number for humans to consume. Autonumbres are not to be given meaning, nor are they to be seen, or used by users in anyway, shape, or form. You can read about this tip (#7)..and a few others here: http://www.mvps.org/access/tencommandments.htm Of course, the main problem with autonubmers is they are numbers that can change, get reset (by mere compacting), and if you start to add a record, but then delete it..the autonumber is used up. Autonumbers are internal pointers just like memory pointers in word...something that the developers can use, but not to be of any use by the end users. So, autonumbers are great for setting relationships between tables, but if the application in question needs a number that users will refer to, then that number needs to be custom made as you have NO control how/what the next auto number will be, and further a compacting of a file can re-set those numbers.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.