Finding the highest value and then adding 1 in a new record.

[Kjell Harnesk]

I have a form that is based on a table. The table has a field called
ProjectNumber. My form is for new records only so I have set it to open
with a new record. I want to find the highest value for ProjectNumber and
then add 1 to the ProjectNumber for the new record. There are some numbers
that has to be excluded because for example 99999 is used for "garbage
projekts" so I really would like to find the highest value wich is lover
than 99999. I have been messing with this and created a query that returns
the value I want to find and the form has a control showing that value but
it works only at times. Mostly it says that I am using null even when the
control shows the value. Can someone help me please?

 

[ALESSANDRO Baraldi]

I have a form that is based on a table. The table has a field called
ProjectNumber. My form is for new records only so I have set it to open
with a new record. I want to find the highest value for ProjectNumber and
then add 1 to the ProjectNumber for the new record. There are some numbers
that has to be excluded because for example 99999 is used for "garbage
projekts" so I really would like to find the highest value wich is lover
than 99999. I have been messing with this and created a query that returns
the value I want to find and the form has a control showing that value but
it works only at times. Mostly it says that I am using null even when the
control shows the value. Can someone help me please?

Looking for Dmax Function on the Help, you can find lot of sample.

Bye

 

[Andi Mayer]

I have a form that is based on a table. The table has a field called
ProjectNumber. My form is for new records only so I have set it to open
with a new record. I want to find the highest value for ProjectNumber and
then add 1 to the ProjectNumber for the new record. There are some numbers
that has to be excluded because for example 99999 is used for "garbage
projekts" so I really would like to find the highest value wich is lover
than 99999. I have been messing with this and created a query that returns
the value I want to find and the form has a control showing that value but
it works only at times. Mostly it says that I am using null even when the
control shows the value. Can someone help me please?

DMAX("MyField","MyTable","MyField<99999")+1

 

[fredg]

I have a form that is based on a table. The table has a field called
ProjectNumber. My form is for new records only so I have set it to open
with a new record. I want to find the highest value for ProjectNumber and
then add 1 to the ProjectNumber for the new record. There are some numbers
that has to be excluded because for example 99999 is used for "garbage
projekts" so I really would like to find the highest value wich is lover
than 99999. I have been messing with this and created a query that returns
the value I want to find and the form has a control showing that value but
it works only at times. Mostly it says that I am using null even when the
control shows the value. Can someone help me please?

As the default value property for that form control to:
=DMax("[ProjectNumber]","TableName","[ProjectNumber]<99999")+1