[AccessD] Possible or No? SOLVED

John Clark John.Clark at niagaracounty.com
Thu Feb 16 12:41:29 CST 2006


After keeping this in the back of my mind, for the last week, I think
I've finally got this one figured out. I was actually getting ready to
put up the white flag. For the first time, since joining this
list...what has it been...five years ago now...the answer didn't
magically appear in my Email. I was actually writing an Email to the
user, requesting a policy change because, as I mentioned, they don't all
do it, and it isn't anything mandated to be done. While typing, it
dawned on me. You should have seen it...the clouds opened up and a beam
of light shown down on my keyboard from above...and I swear I heard
angels singing. An epiphany...I had a damn epiphany!

As exhilarating as it is, to have finally beat this problem, the
solution was so damn simple that I can't help feeling a bit a bit "duh"
at the same time. My only excuse is that I am juggling many other
things, and most of them un-program related.

The solution was as simple as adding ",3" to the mid function. This
made the line:

strSQL = "SELECT
nz(Max(Mid([tblIndictment]![IndictmentNumber],6,3)),0)+1 AS lngMaxID," &
_
         "Year(Now())" & _
         "& '-' & [lngMaxID] AS lngNewID " & _
         "FROM tblIndictment " & _
         "WHERE
(((Left([tblIndictment]![IndictmentNumber],4))=Right(Year(Now()),4)));"

Now it only looks at the characters in slots 6, 7 and 8, and disregards
anything further. This was so dang easy. I can't believe I didn't see it
before. I've tested it and it actually works. I have to leave the field
open for editing...I could have an enable button, but I don't know that
it is necessary...and the user overrides the number. But, then, that
same number will, as it should be, be the next one in line again.

John W. Clark




More information about the AccessD mailing list