|
|
Hi All,
Some one once created a very simple put useful auto number generator
the following code was used, and did what i needed.
Private Sub Form_Current()
If Me.NewRecord Then
Me!ProjectQNo = "Q" & _
Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
"Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
1, _
"0000") & Format(Date, "yy")
End If
End Sub
it actual looked at the Highest number in the ProjectQNo Field with in
the tbl_Projects and then worked out the next number to issue eg
Q374008 .
I Need a Similar code that would produce a number e.g C3750 but for it
not to reset the beginning of the year. can this me done?
|
|
Try:
If Me.NewRecord Then
Me!ProjectQNo = "Q" _
& Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4),
"tbl_Projects"), 0)) + 1, "0000")
endif
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
"dan.cawthorne[ at ]gmail.com" wrote:
[Quoted Text] > Hi All,
>
> Some one once created a very simple put useful auto number generator
> the following code was used, and did what i needed.
>
> Private Sub Form_Current()
> If Me.NewRecord Then
> Me!ProjectQNo = "Q" & _
> Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
> "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
> 1, _
> "0000") & Format(Date, "yy")
> End If
> End Sub
>
> it actual looked at the Highest number in the ProjectQNo Field with in
> the tbl_Projects and then worked out the next number to issue eg
> Q374008 .
>
> I Need a Similar code that would produce a number e.g C3750 but for it
> not to reset the beginning of the year. can this me done?
>
|
|
do you mean that you want the number to continually increment, no matter the
year?
dan.cawthorne[ at ]gmail.com wrote:
[Quoted Text] >Hi All,
>
>Some one once created a very simple put useful auto number generator
>the following code was used, and did what i needed.
>
>Private Sub Form_Current()
>If Me.NewRecord Then
> Me!ProjectQNo = "Q" & _
> Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
> "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
>1, _
> "0000") & Format(Date, "yy")
>End If
>End Sub
>
>it actual looked at the Highest number in the ProjectQNo Field with in
>the tbl_Projects and then worked out the next number to issue eg
>Q374008 .
>
>I Need a Similar code that would produce a number e.g C3750 but for it
>not to reset the beginning of the year. can this me done?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1
|
|
Dale Fye wrote:
[Quoted Text] > Try:
>
> If Me.NewRecord Then
> Me!ProjectQNo = "Q" _
> & Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4),
> "tbl_Projects"), 0)) + 1, "0000")
> endif
Note that this breaks at 9999 and the next number will be 1000
(Mid(ProjectQNo,2) gets everything from the second position on.
|
|
Mike,
Good observation, but not entirely true. It doesn't break at 9999, it just
rolls over to 1000, which probably is not his intention, although the OP
failed to indicate whether he wanted a longer ProjectQNumber what he wanted
to do when it reached 9999.
Personally, and I probably should have put this in my original response, I
strongly recommend against concatenating values into a single field. If the
Q is a constant, and never changes, it should not even be stored. And in
the original instances, he should just have a # field (this could either be
auto-number of manually generated), and a year field.
If he wants to present this as some sort of ProjectQNumber, then he can
concatenate them in the query that he uses for his recordsource.
Dale
"Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message
news:wRT3l.7231$pr6.244[ at ]flpi149.ffdc.sbc.com...
[Quoted Text] > Dale Fye wrote:
>> Try:
>>
>> If Me.NewRecord Then
>> Me!ProjectQNo = "Q" _
>> & Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4),
>> "tbl_Projects"), 0)) + 1, "0000")
>> endif
>
> Note that this breaks at 9999 and the next number will be 1000
> (Mid(ProjectQNo,2) gets everything from the second position on.
>
|
|
I meant Break as in broken and should have used your term
I just realized that at this point there will never be a 1001, just a string
of 1000's.
Dale Fye wrote:
[Quoted Text] > Mike,
>
> Good observation, but not entirely true. It doesn't break at 9999,
> it just rolls over to 1000, which probably is not his intention,
> although the OP failed to indicate whether he wanted a longer
> ProjectQNumber what he wanted to do when it reached 9999.
>
> Personally, and I probably should have put this in my original
> response, I strongly recommend against concatenating values into a
> single field. If the Q is a constant, and never changes, it should
> not even be stored. And in the original instances, he should just
> have a # field (this could either be auto-number of manually
> generated), and a year field.
> If he wants to present this as some sort of ProjectQNumber, then he
> can concatenate them in the query that he uses for his recordsource.
>
> Dale
>
>
> "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message
> news:wRT3l.7231$pr6.244[ at ]flpi149.ffdc.sbc.com...
>> Dale Fye wrote:
>>> Try:
>>>
>>> If Me.NewRecord Then
>>> Me!ProjectQNo = "Q" _
>>> &
>>> Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4), "tbl_Projects"), 0)) + 1,
>>> "0000") endif
>>
>> Note that this breaks at 9999 and the next number will be 1000
>> (Mid(ProjectQNo,2) gets everything from the second position on.
|
|
Mike,
I see what you mean.
For some reason, I thought that Format(10001, "0000") was going to give me
"0001", but it doesn't, it just keeps growing. But when you concatenate this
to the Q and the year, you will get "Q1000008".
And since I used DMAX(Mid(ProjectQNum, 2, 4) ..., I'm always going to get
the "9999" as the maximum, once that value is reached.
That is why I always prefer to use multiple fields for this type of thing,
and concatenate them in a query, if I absolutely need it them mashed together.
--
Dale
email address is invalid
Please reply to newsgroup only.
"Mike Painter" wrote:
[Quoted Text] > I meant Break as in broken and should have used your term
> I just realized that at this point there will never be a 1001, just a string
> of 1000's.
> Dale Fye wrote:
> > Mike,
> >
> > Good observation, but not entirely true. It doesn't break at 9999,
> > it just rolls over to 1000, which probably is not his intention,
> > although the OP failed to indicate whether he wanted a longer
> > ProjectQNumber what he wanted to do when it reached 9999.
> >
> > Personally, and I probably should have put this in my original
> > response, I strongly recommend against concatenating values into a
> > single field. If the Q is a constant, and never changes, it should
> > not even be stored. And in the original instances, he should just
> > have a # field (this could either be auto-number of manually
> > generated), and a year field.
> > If he wants to present this as some sort of ProjectQNumber, then he
> > can concatenate them in the query that he uses for his recordsource.
> >
> > Dale
> >
> >
> > "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message
> > news:wRT3l.7231$pr6.244[ at ]flpi149.ffdc.sbc.com...
> >> Dale Fye wrote:
> >>> Try:
> >>>
> >>> If Me.NewRecord Then
> >>> Me!ProjectQNo = "Q" _
> >>> &
> >>> Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4), "tbl_Projects"), 0)) + 1,
> >>> "0000") endif
> >>
> >> Note that this breaks at 9999 and the next number will be 1000
> >> (Mid(ProjectQNo,2) gets everything from the second position on.
>
>
>
|
|
|