Integer troubles

[Guest]

Hello,

I have a three digit code that represents a location of a device on a site.
I am creating a database that, using the code, holds information about each
device. When I wish to add a new device I use ADO to sort and step through
each record until I get to the next blank entry or gap in a sequence.
i.e 425, 426, 428 are already in use, so when i generate a new number it
returns 427. The number returned next time would be 429
That is all good and proper and works fine

Except:
when the device is in the 000 range.
I need to return an integer so I can increment and compare, but my function
only returns 0.

How can you return a 000 integer, or what trick could I use?

 

[Van T. Dinh]

What the different between numeric values 0 and 000?

Answer: Nothing

If it is numeric 0, you can add 1 to get the next value 1. If you want to
show this as "001", you use the Format Property or the Format() function.

HTH
Van T. Dinh
MVP (Access)

 

[Douglas J. Steele]

Numbers in Access don't have leading zeros. 001 = 01 = 1.

However, if you're dealing with integers, I don't see why you should need
000. That should only matter if you're dealing with text.

 

[Guest]

Beautiful. Thanks for your help.

But maybe you can help me with something else as well!

Example Data

Plant Location Start Number Length
PLANTA LOCA 000 9
PLANTA LOCB 010 9
PLANTA LOCC 020 9
PLANTB LOCA 100 9
etc

Plant and location fields are selected by a combo box on a form.
What is the best way to retrieve the integer in the number field for use in
a function. Should I use an ADO recordset, filter on combo box values and
call the third field or is there an easier way to do this?
Also the forth field ("length") is used to determine whether i have run out
numbers in that range. Is there a way to return multiple values from a
function?

 

[Guest]

Thanks for your help again Mr. Steele.

The trick is that I use these numbers as part of a string. The numbers are
part of a bigger number. So for some equipment there is DB000, and for others
there is IDB405, and JB001 etc.
What I am doing is getting a base number (eg 000) adding say DB at the front
(DB000) - comparing to a record in a record set and if there exists an entry.
incrementing the number, creating a newstring (DB001), moving to the next
record and comparing again until i get a new unused numbers.

Thanks to the help of Access MVP's this database is starting to rock!

However, if you have a suggestion for my reply to Van (in this thread) that
would be great!
Thanks again

 

[Albert D.Kallal]

Thanks for your help again Mr. Steele.

The trick is that I use these numbers as part of a string. The numbers are
part of a bigger number. So for some equipment there is DB000, and for
others
there is IDB405, and JB001 etc.

Yes, but the point was that you deal with the math and the numbers as
numbers, it is ONLY when you use the number for display do you need to pad
in the zeros.


Dim i As Integer

For i = 1 To 5

Debug.Print "AB" & Format(i, "000")

Next i

The output from the above will be

AB002
AB003
AB004
AB005

I am still confused as to what the problem here is, but the advice given by
at least 3 responders now is simply to use integers, and ONLY format them
for display, or when you need to pad the zeros in front. You can't
efficiently do number processing on strings. So, you work with, or convert
the value into a integer...process, and if you need be, format the output as
a string.

 

[Van T. Dinh]

Sorry but I got no ideas of the set-up you are trying to describe.
Remember, I can't see your database.

If you want describe the relevant Tables, the Form(s), its RecordSouce,
ComboBoxes and there RowSources, BoundColumns and whether they are bound or
not.

Generally if I use JET back-end, I don't use DAO rather than ADO.

HTH
Van T. Dinh
MVP (Access)