Dmax in text field containing text and number value

[Seph]

Good day everyone,

I have a sequential field called InvoiceNumber.

I'd like to use DMax to reference the highest value and then add a one to it. I have this setup in the On_Load Event on my Invoice New Form.

However, I'm getting the following error

Is this due to it referencing the I in the text field? is there a way to isolate the DMax function to the numeric value of the text field only?

Thanks!

 

[Ranman256]

Your field is a string. You can’t do math on text,
convert to number:

dim iNum as long
iNum = val(Dmax(...)) + 1

 

[Eugene-LS]

I'm getting the following error

Try:

Dim InvoiceNumber As Long
    
    InvoiceNumber = DMax("Val(Mid(InvoiceNumber, 2))", "InvoiceT") + 1

 

[Minty]

The real answer here is to simply store a real number and display it using appropriate formatting.
Then you wouldn't need to jump through hoops to do the rest of the process.

 

[ebs17]

You have to solve self-created problems.

InvoiceNumber should be a number (Long) in the table. Then your calculation works simply and easily. You can use the format property of a control in forms/reports to display the desired format.

The property only affects the display, it does not change the value.

 

[Seph]

Thank you everyone for your feedback.

I'm going to try setting it to a Number value and play with the format of the property in the Form. Will post result when I get it working.

 

[Seph]

Ok, so it is working now.

The easiest solution is curtsey of @Ranman256 , @Minty and @ebs17.

1. Store the value in the table as a Number data type.
2. On the Form I used the following VBA code to find the largest number in the InvoiceNumber field and incremented it by 1:

Private Sub Form_Load()

InvoiceNumber = DMax("InvoiceNumber", "InvoiceT") + 1

End Sub

3. To get the desired look in my report, I changed the Format in the Property Sheet

Thanks again everyone.

 

[Minty]

Glad you fixed it and your problem.
You will find that all sorts of things will be quicker and easier with a real number instead of something kludged together.