Full Version: Dmax plus one
UtterAccess Forums > Microsoft® Access > Access Forms
daniels012
I have this on a label in the form:
=DMax("[SampleNumber]","SampleBookTbl","Left([SampleNumber],3) = 'SSC'")
Which tells me the last used sample number. I would like it to give me the next number to use. So basically, I need it to +1 to the Dmax number it gives me. Where can I add this???
Thank You,
Michael
xteam
=DMax("[SampleNumber]","SampleBookTbl","Left([SampleNumber],3) = 'SSC'") + 1
daniels012
Tried that!
It gives me an Error
In the text box it says simply "Error"???
Michael
ScottGem
You can't do this. SampleNumber appears to be a text field. You can't add to a text field, hence the error.
You will have to parse out the numerical portion, convert to a vlaue, increment and convert back to text.
xteam
frown.gif yes, I didn't noticed the Where clause.
Assuming you have only digits after the prefix SSC you can use something like this:
DMax("Val(Mid([SampleNumber],4))","SampleBookTbl","Left([SampleNumber],3) = 'SSC'") + 1
For
=Val(Mid(DMax("[SampleNumber]","SampleBookTbl","Left([SampleNumber],3) = 'SSC'"),4)) + 1
daniels012
Every number I use has this same pattern:
SSC-00001
Three letters, a dash, then 5 numbers
So can I use one of your examples and change the number to 5??
ichael
ScottGem
Val(Right(SampleNumber,5))
daniels012
OK
It works in giving me the next number...
Oneed it to give me the next number with the preceeding data
IE: it gives me 61 instead of SSC-00061
know I'm gettting picky!!!
Thank You,
Michael
ScottGem
You have to reassemble the number. Something like
SSC-" & Format(newnumber,"00000")
daniels012
That worked perfectly!!
Sometimes I just have a brain fart where I can't get something to work!!
Thank You,
Michael
ScottGem
glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.