DMAX in a Text Field

[rnutts]

Hi

I have a table with three key fields

table name is tblSampleDetails

Field names are

ClientSampleRef
BatchNumber
Visual

A Batch may have many samples of which some may be visuals

Currently I store a variety of values (some number, some text and some a combination of both) in ClientSampleRef but I would like to start on some samples creating the value for the ClientSampleRef
If the sample is a visual (this is indicated by a check box on the form giving a value of -1) I would like to make the ClientSampleRef = Visual 1 this would then increment within the same batch by one each time.
I have taken some code from Dev Ashish and Arvin Meyer and changed the field and table names but I am getting an error saying there is a missing operator

If Check42 = -1 Then
Me.ClientsSampleRef = "REC-" & Right(DMax("ClientSampleRef", "tblSampleDetails"), Len(DMax("ClientSampleRef", "tblSampleDetails")) - InStr(1, DMax("ClientSampleRef", "tblSampleDetails"), "-")) + 1
End If

I am just testing this code so I have created a record with a value of REC-1 in the field ClientSampleref
I have also created a test database and the code works within that upto a value of REC-10 then I get problems

any help or pointers greatly appreciated

Richard

 

[gemma-the-husky]

you are actually stripping a string from the value and then trying to add 1 to it. you need ot change it to a number with either val or clng function

i would do it in stages, and store the ref in a temporary variable, to prevent multiple uses of dmax. mid is probably more efficient than using both right and len, so you get the following. you also ought to use use explicintly true and false rather thaN -1 and 0 when testing booleans

dim maxref as string

If Check42=true Then

{allow for no records on file)
maxref = nz(DMax("ClientSampleRef", "tblSampleDetails"), "REC-0")
ClientsSampleRef = "REC-" & val(mid(maxref), instr(1, maxref,"-")+1)+1
end if

see if that does what you want works