Using DMax

[Brian]

I have a table in which there is a field called ContactID. It is a text
field and started with 0001. Whenever I added a contact I used the formula:

ContactID = CStr(CInt(DMax("str_ContactID", "dbo_tbl_Contacts")) + 1)

to get the "next" ContactID number. Unfortunately, we have reached
ContactID number 10000. Now the formula above always returns 10000 because
for some reason the text "9999" is greater than the text "10000". Needless
to say, adding Contacts has come to a screeching halt.

Does anyone know how to alter my formula above to get a ContactID above
10000? Note that this is an extensive database and changing existing
ContactID numbers is not practical, so somehow I have to go down the path
that has already been created.

Thanks!
Brian

 

[NoodNutt]

G'day Brian

I'm curious as to why you're using Text to count...?

Typically, you should use AutoNumber as it increments automatically, but if
you need to do it via a Number format, something like

Sub Before_UpDate()
Me.ContactID = Me.ContactId + 1
End Sub

This also will count up regardless.

HTH
Mark.

 

[bcap]

You sound incredulous that "9999" is greater than "10000". Well it is, and
it's because the data type of your field is text, not number. The
comparison of text data proceeds character by character, starting with the
first character. And, in the collating sequence, "9" comes after "1", hence
"9999" is greater than "10000".

To solve this properly you should change the data type of the field from
Text to Number (specifically, Long Integer). However, if the application is
large and mature, this may have many unfortunate side-effects.

The quick and dirty fix is this:

ContactID = CStr(DMax("CInt(str_ContactID)", "dbo_tbl_Contacts") + 1)


The smart move would be to change the

 

[Brian]

Mark,

Thanks for your response. We did the text thing because for some reason or
other we wanted to see 0001 as opposed to just 1 (and so on). Unfortunately,
I didn't realize at the time that when we hit that fifth digit, everything
would stop working. So for now, I'm stuck with what we have and hope that
someone knows how to do what I need to get done.

Thanks!
Brian

 

[bcap]

AutoNumber fields have some important problems.

Firstly, they are not guaranteed sequential. The AutoNumber value is
assigned when you begin entering a new record. If the record is abandoned
before it is saved, then that AutoNumber value is lost and there is a gap in
the sequence.

Secondly, you can only rely on AutoNumbers being unique within the table as
it currently stands, you cannot rely on them being unique over time.
Suppose you add a customer record to a table and it gets assigned AutoNumber
value 100. You then do some stuff with that record (send someone a letter
for instance, with customer number 100). Then, before any other records are
entered, you delete record 100. Then you compact the database. Then you
add another new record which gets AutoNumber value...100! So, you've now
got two different customers who, as far as they are concerned, are both
customer 100!

Of course, this latter problem also afflicts the OP's DMax approach,
assuming he allows records to be deleted.

There are ways to implement a guaranteed sequential, unique-for-all-time
numbering system, but they're pretty darn complicated (and cause a
performance bottleneck in busy, multi-user databases).

 

[bcap]

It's too late now, but the smart thing would have been to create the field
as numeric and simply format it whenever it is output on a form, report etc.
The following format would've done the trick, whether used in a Format
property or the Format function:

"00000"

 

[NoodNutt]

Brian

I'm no MVP, Just a thought, bit of mucking around, but you could try this.

Create a query based on your dbo_tbl_Contacts
Drag all the Fields into the grid
insert a blank field into the grid.

For this exercise we'll label it as:

MyNewContactId: Val([ContactID])

Open up your dbo_tbl_Contact table in design view

Inert a new field and make it numeric, long (fixed, no decimals)
Call the new field MyNewContactID
Save it and open it
Do an ascending filter on the old Contact ID column
Leave the table open.

Now go back to the new query
Open it in normal view, not design.
Do the same ascending filter on the old Contact ID column
Click on the NewContact column tab at the top so you highlight the entire
column.
Then copy it.
Go back to the table and click on the newcontact column and paste in the
data from the query.

save it, close it
then close the query you created without saving it.

as a precaution, I would take a little time to compare the numbering toward
the end of the recordset, just to make sure the numbers correspond, if they
are out of sequence, you can tidy them up by matching them.

before you go back to tbl view, go to the relationship design grid and make
sure there isn't any relavent ties to the old contactID field, if so, delete
them, you can always go back and re-establish them after.

Now, re-open the tbl in design view, delete your old ContactID field and
rename your new one as your old one.

That should do the trick, unless of course if any of the MVP's have another
alternative solution, which I have no doubts they may.

Good luck
HTH
Mark.

 

[bcap]

If he wants to change the data type of the field, all he needs to do is to
open the table in design view and change the data type of the field...

He'll also need to do the same on every related field in other tables,
re-establish the relationships, and then fix all the code, formulas,
expressions and queries throughout the system which currently depend upon
the ContactID being text.