Expression stopped working (1 Viewer)

[Monardo]

Hello

Some months ago I wrote an expression and included it into the Default Value of a form text box. It was working perfectly until one day and since then I am breaking my head, but cannot find the reason. Here is the info:

It is a simple form/subform for the simple related (two tables) database.
The database is for registering what arrives into warehouse.
The problem is on the main form.
There is a field in the main table (tblPO) called <PO/Invoice#>. The data entered in this field has the following format: " 2011-001 ", where 2011 is current year and 001 is a consecutive number for the invoices as they come in. This means that in year 2012 the first invoice will be "2012-001" and the second "2012-002", etc.

In order to automate this number creation I wrote the following expression in the Default Value of the appropriate text box of the form:

=Year(Now()) & Format((Val(DLast("right([tblPO]![PO/Invoice#],3)","[tblPO]"))+1),"000")


It was working as a charm, until the number reached 2011-043 and since than it is always 043. On the January 1st the number turned into 2012-043, so the first part (Year(Now)) is still working, but DLast is retrieving the wrong number (apparently 042). When I check the table raw data the last number is "2012-003", but the expression stubbornly gives 043.

What could have happened? I have not changed anything before (or after) of the event.

Expert opinion would be appreciated.
Thank you

 

[spikepl]

Dlast is a tricky beast, that does not always do what you think, http://www.access-programmers.co.uk/forums/showthread.php?t=196721

Use DMax with the right criteria

 

[vbaInet]

Once you've switched to DMas(), here are a few parts of your expression I would advise you amend:

Year(Date())

"Val(Right([tblPO]![PO/Invoice#],3))"

New bits in red.

 

[Monardo]

Thanks a lot, very helpful

Problem solved

 

[gemma-the-husky]

it's probably a bit late now - but parsing a number/text into components to be able to increment one of those components is fiddly, and relatively slow, as well as being technically non-normalised.

it would be much better to hold two distinct fields - year and ordernumber.

sorting/filtering becomes a lot easier as well

this applies to pretty well any structured field. a field should not contain a structure of its own - it should be "atomic"