Incrementing a text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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


Ofer Cohen said:
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

--
Good Luck
BS"D


Ruth Enright said:
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.
Click to expand...
Click to expand...
 
Ruth said:
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.
Click to expand...


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
What is the purpose of the P- before the counter?
Is it going to be P always?
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Back
Top