Calculating a number sequence

T

Tony Williams

I have a control on a form called ABCDNbr. The makeup of the control is
ABCD/Cnbr/Year and the format is "ABCD"/000/0000;0;_
Where "ABCD" is static Cnbr is a hidden control based on a field of my table
where the default value is
=Nz(DMax("[Cnbr]","Offence"),0)+1
and year is the 4 digit year of a control CInputDate which is the date of
the new record input whose default value is Date()
How do I get the value of ABCDNbr to be automatically created as say
ABCD0092007 where 009 is the next value of Cnbr and 2007 is the year of
input.

Thanks
Tony
 
T

Tony Williams

One other thing the middle portion of the number ie value of Cnbr should
have leading zeroes such as 009
Thanks
Tony
 
S

Steve

Me!ABCDNbr = "ABCD" & Format(Me!CnBr,"000") & Format(Year(Date()),"yyyy")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
T

Tony Williams

Thanks Steve but where do I put that expression?
Thanks
Tony
Steve said:
Me!ABCDNbr = "ABCD" & Format(Me!CnBr,"000") & Format(Year(Date()),"yyyy")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)





Tony Williams said:
I have a control on a form called ABCDNbr. The makeup of the control is
ABCD/Cnbr/Year and the format is "ABCD"/000/0000;0;_
Where "ABCD" is static Cnbr is a hidden control based on a field of my
table where the default value is
=Nz(DMax("[Cnbr]","Offence"),0)+1
and year is the 4 digit year of a control CInputDate which is the date of
the new record input whose default value is Date()
How do I get the value of ABCDNbr to be automatically created as say
ABCD0092007 where 009 is the next value of Cnbr and 2007 is the year of
input.

Thanks
Tony
 
T

Tony Williams

I've put it in the Onload of the form but the year is showing as 1905 not
2007 Why's that?
Tony
Tony Williams said:
Thanks Steve but where do I put that expression?
Thanks
Tony
Steve said:
Me!ABCDNbr = "ABCD" & Format(Me!CnBr,"000") & Format(Year(Date()),"yyyy")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)





Tony Williams said:
I have a control on a form called ABCDNbr. The makeup of the control is
ABCD/Cnbr/Year and the format is "ABCD"/000/0000;0;_
Where "ABCD" is static Cnbr is a hidden control based on a field of my
table where the default value is
=Nz(DMax("[Cnbr]","Offence"),0)+1
and year is the 4 digit year of a control CInputDate which is the date
of the new record input whose default value is Date()
How do I get the value of ABCDNbr to be automatically created as say
ABCD0092007 where 009 is the next value of Cnbr and 2007 is the year of
input.

Thanks
Tony
 
T

Tony Williams

I'm sorry but I have no idea what this is about. I'm a regular poster here
and find the newsgroups incredibly helpful when I get stuck with a problem.
I hope your argument with this guy doesn't stop me from getting an answer to
my problem!!!!!!!!!!!
Thanks
Tony

Steve said:
Me!ABCDNbr = "ABCD" & Format(Me!CnBr,"000") & Format(Year(Date()),"yyyy")

--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3350+ pageloads, 2250+ first-time visitors (these figures are
rapidly increasing)

To PCD' Steve: (this is also to inform those who do not understand...)
This is *not* about the sigline...(although you are far away from a
'resource' status).
But we will simply continue to hunt down *each and every* of your posts.

Why???
Because you are the ONLY person here who continues to advertise in the
groups.

It is not relevant whether you advertised in *this* particular post or
not...
==> We want you to know that these groups are *not* your private hunting
grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR
 
S

Steve

Hi Tony,

Check your system clock to see what year it is returning. Date() returns the
current date and "yyyy" should cause only the four digit year to display.

Putting the expression in the OnLoad event might not be a good idea as this
causes a record to be created as soon as your form opens. It would be better
to put the expression in the AfterUpdate event of another control on your
form that will always be entered when a new record is created.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Tony Williams said:
I've put it in the Onload of the form but the year is showing as 1905 not
2007 Why's that?
Tony
Tony Williams said:
Thanks Steve but where do I put that expression?
Thanks
Tony
Steve said:
Me!ABCDNbr = "ABCD" & Format(Me!CnBr,"000") &
Format(Year(Date()),"yyyy")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)





I have a control on a form called ABCDNbr. The makeup of the control is
ABCD/Cnbr/Year and the format is "ABCD"/000/0000;0;_
Where "ABCD" is static Cnbr is a hidden control based on a field of my
table where the default value is
=Nz(DMax("[Cnbr]","Offence"),0)+1
and year is the 4 digit year of a control CInputDate which is the date
of the new record input whose default value is Date()
How do I get the value of ABCDNbr to be automatically created as say
ABCD0092007 where 009 is the next value of Cnbr and 2007 is the year of
input.

Thanks
Tony
 
S

Steve

Tony,

This guy is tracking all my responses to posts in the newsgroups. He is
wasting readers time and wasting newsgroup bandwidth. You can greatly help
the newsgroups by sending a complaint to:
(e-mail address removed).

The guy's name is Arno Raaijmaakers. In your complaint state that he is
violating planet.nl's Terms of Service and Acceptable Use Policy and is
violating newsgroup rules of conduct:

Rules of conduct
Appropriate Language: The purpose of our communities is to exchange
technical information and
expertise about Microsoft products. Please avoid personal attacks, slurs,
and profanity in your
interactions.

Be sure to include his post as an example.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
J

John Marshall, MVP

It's not your fault, you just happened to run into the local snake oil
salesman. Steve santos, aka pcdatasheet has been preying on these newsgroups
for years. It is standard policy that users of these newsgroups can get free
peer to peer help without being pestered by people looking for work.
Unfortunately, steve has taken it upon himself to be the only one who
ignores this rule and tries to con unsuspecting users out of their money.

Steve has also proven he really does not know what he is doing by his
numerous wrong and incomplete answers.

So steve has annoyed Arno to the point that he has decided to inform any
user who has contact with steve of steve's past behaviour even if steve's
post is benign.

Don't worry about getting an answer, there are many people far more
competent than steve who would gratefully help you for free.

John... Visio MVP
 
J

John Marshall, MVP

Steve you are a piece of work.

So are you recommending that if people do not like your sleazy business
practise of soliciting in the newsgroups?

John... Visio MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top