Incrementing a text field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ruth Enright - 10 May 2007 21:37 GMT

I've read with interest the questions and responses regarding incrementing a
text field. However, as I know next to nothing about VBA, I'm hoping to get
a specific answer to my question: Can I put as a default in a table text
field "P-3000" and have that increment by 1 when a new record is entered? If
so, can you provide the specific syntax. I'm using Access 2002. Thank you.

Signature

Ruth Enright

Reply to this Message

Ofer Cohen - 10 May 2007 22:21 GMT

What is the purpose of the P- before the counter?
Is it going to be P always?

If so, then dont add the P to the field, have a number field that increment
by 1, nd using a query you can add the P

Select "P-" & [FieldName] As NewFieldName From TableName

About incrementing the field you can use
=Nz(DMax("[FieldName]","[TableName]"),2999)+1

The DMax will retrieve the Max number and then you just need to add one to it.
That can use on the Form Load event, where you assgn a value to the field in
the form, but it can't used as a default
===============================
If you don't mind having gaps between the numbers, 3000 , 3002 , 3005 then
use a counter as field type.

Any way, I can keep on going, but mybe you can aim me to the right direction
and answer my questions first

Signature

Good Luck
BS"D

> I've read with interest the questions and responses regarding incrementing a
> text field. However, as I know next to nothing about VBA, I'm hoping to get
> a specific answer to my question: Can I put as a default in a table text
> field "P-3000" and have that increment by 1 when a new record is entered? If
> so, can you provide the specific syntax. I'm using Access 2002. Thank you.

Reply to this Message

Ruth Enright - 11 May 2007 16:14 GMT

Thank you for your responses. Here is the problem: The field in question is
part of a table that is linked to a database that has been in existence for
some time. I originally made this field a text field because I wanted a way
to designate different types of clients (one of which is "Prospective" --
hence the "P-") (There are other types -- so not all entries in this field
are "P" and the numbers vary for other reasons, so it really cannot look for
a maximum value. Any entries made in this new db will be prospective ("P"),
and I picked 3000 because this far exceeds the number of records already in
the main db. I'm looking simply for a default value that will increment by
one each time it is used, starting from P-3000, even though the prior entries
in that field vary widely. Thanks to all again for thinking about it. I
look forward to further responses.

Ruth Enright

> What is the purpose of the P- before the counter?
> Is it going to be P always?
[quoted text clipped - 22 lines]
> > field "P-3000" and have that increment by 1 when a new record is entered? If
> > so, can you provide the specific syntax. I'm using Access 2002. Thank you.

Reply to this Message

Cyber-guy - 13 May 2007 04:52 GMT

>Thank you for your responses. Here is the problem: The field in question is
>part of a table that is linked to a database that has been in existence for
[quoted text clipped - 8 lines]
>in that field vary widely. Thanks to all again for thinking about it. I
>look forward to further responses.

Check out http://forums.databasejournal.com/showthread.php?t=45256
near the end of the thread is a sample db that should work for you. You can
specify any starting number you like. The field is a number, not a text
field - you'll have to append a "P-" for your purpose - if you plan on using
other client types, add a field for P and whatever else and append that
prefix to the number - I use that method to number reports - report name &
number

Hope this helps,
Cyber-guy

>Ruth Enright
>
[quoted text clipped - 3 lines]
>> > field "P-3000" and have that increment by 1 when a new record is entered? If
>> > so, can you provide the specific syntax. I'm using Access 2002. Thank you.

Reply to this Message