By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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" <ga*********@gmail.com> 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.