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

  • Thread starter Thread starter Kjell Harnesk
  • Start date Start date
K

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?
 
Kjell Harnesk said:
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?
Click to expand...


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

Bye
 
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?
Click to expand...

DMAX("MyField","MyTable","MyField<99999")+1
 
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?
Click to expand...

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