Selecting data and incrementing using DMax

[rnutts]

I have data in a table
I am extracting using an append Query, I wish to assign a number from the original table +1 to the records pulled by the append query.
I am very new to this and have the expression below, however the result is #error in created field CertifNumber
The data I am looking for is in table Sample Details and the Field is CertNo

CertifNumber: DMax([Sample Details]![CertNo],"Sample Details","[Sample Details]![CertNo]+1")

If somebody could help me with this expression I would be very grateful.

Thanks

Richard

 

[boblarson]

The problem lies in the DMAX syntax. The last part (after the second comma) should be the criteria of what you are selecting as DMAX, not assigning the value. You would do it like this:

CertifNumber: DMax([Sample Details]![CertNo],"Sample Details","[CertNo]=" & [Forms]![Sample Details]![CertNo]) +1

or, if you are just pulling the last number from everything:

CertifNumber: DMax([Sample Details]![CertNo],"Sample Details") +1

 

[rnutts]

Thanks for the answer, I would like some further understanding of the formula you have given please.
I have put both examples in and both still return #Error
Am I trying to do this the right way, or is there a better way of achieving what I want to do?????

 

[boblarson]

oops I led you astray... I missed something originally.

It should be this:

CertifNumber: DMax("[CertNo]","Sample Details") +1

which means [CertNo] is the field we are searching in the "Sample Details" table and then once we find it we add one. The brackets and quotation marks should be as shown.