I have the following set up to give me aresponse number R05-001
I would like it to reset to R06-001 at new year
ResponseNo: "R" & Right(Format(Da te(),"yyyy"),2) & "-" &
Format([PONum],"000")
This is an expression in a query
any help is apprecieated
Ken
22  5630 
"Kd" <ke******@yahoo .com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. . I have the following set up to give me aresponse number R05-001
I would like it to reset to R06-001 at new year
ResponseNo: "R" & Right(Format(Da te(),"yyyy"),2) & "-" &
Format([PONum],"000")
This is an expression in a query
any help is apprecieated
Ken
Ken,
That should do the job as is. "Date()" will return the current date, so it
should switch automatically at new year.
It seems a bit odd to Format for a 4 digit year then take only the right 2
digits, but it should work.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
intead of Right(Format(Da te(),"yyyy"),2)
should i go with datepart(yy)
Kd wrote: I have the following set up to give me aresponse number R05-001
I would like it to reset to R06-001 at new year
ResponseNo: "R" & Right(Format(Da te(),"yyyy"),2) & "-" &
Format([PONum],"000")
This is an expression in a query
any help is apprecieated
Ken
How is PONum generated? What you have now will automtically change with the
year on the prefix, but the numbers will not go back over to start at one again
if you are using an AutoNumber for the PONum.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
"Kd" <ke******@yahoo .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. . intead of Right(Format(Da te(),"yyyy"),2)
should i go with datepart(yy)
Instead of
Right(Format(Da te(),"yyyy"),2)
you could use:
Format(Date()," yy")
The first one says to assemble the year for the current date in 4 digits
then throw away all but the last 2 digits. The second one says simply
assemble the last two digits of the year for the current date. There is
nothing wrong with the first form, it just does more work than is needed.
Cheers,
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?
What is in the PO Table? Is it a single record with the current PO number,
or does it contain a record of each PO you've created?
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
"Kd" <ke******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?
Kd wrote: PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?
First I see another problem. If you are generating this formatted number in a
query using...
ResponseNo: "R" & Right(Format(Da te(),"yyyy"),2) & "-" & Format([PONum],"000")
....then ALL of your records will change to R06 for the prefix in the new year
not just new ones because you are always using the current system date. You
need to be using the date that the record was created. Do you have such a
field?
If your table included a field named CreateDate then your query expression would
be...
ResponseNo: "R" & Format(CreateDa te(),"yy") & "-" & Format([PONum],"000")
....(eliminatin g the unnecessary Right() function)
Then your DMax assignment changes to...
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >= DateSerial(Year (Date()), 1,
1)"), 0)+1
Now instead of finding the highest number in the table and adding one to it, the
expression finds the highest number from only those records created in the
current year. The Nz() wrapper is required for the very first entry of any
given year since DMax() will return Null in that circumstance.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
thanks
and i would assign the dmax satement to the
response number textbox on form correct
thanks again
Ken
On Fri, 30 Dec 2005 15:09:14 GMT, "Rick Brandt"
<ri*********@ho tmail.com> wrote: Kd wrote: PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?
First I see another problem. If you are generating this formatted number in a
query using...
ResponseNo: "R" & Right(Format(Da te(),"yyyy"),2) & "-" & Format([PONum],"000")
...then ALL of your records will change to R06 for the prefix in the new year
not just new ones because you are always using the current system date. You
need to be using the date that the record was created. Do you have such a
field?
If your table included a field named CreateDate then your query expression would
be...
ResponseNo: "R" & Format(CreateDa te(),"yy") & "-" & Format([PONum],"000")
...(eliminatin g the unnecessary Right() function)
Then your DMax assignment changes to...
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >= DateSerial(Year (Date()), 1,
1)"), 0)+1
Now instead of finding the highest number in the table and adding one to it, the
expression finds the highest number from only those records created in the
current year. The Nz() wrapper is required for the very first entry of any
given year since DMax() will return Null in that circumstance.
If I may tack on...
I've had similar cases where clients want to number their files or
responses or whatever, in the format yy/nnn. When I ask why, the
reason is always so they know how many are in that year. Of course, a
simple query can always give that answer. So I always try to change
clients to accepting a simple sequential numbering system.
Where I can't convince them, I would do something like Rick has
suggested but put this in the Default value of the control, thereby
generating the number when the record is created and maintaining it
in subsequent edits by locking the field.
P This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Earl Anderson |
last post by:
KB Article Q140908 provided the following function to create an Auto
Incrementing Counter:
Function Next_Custom_Counter ()
On Error GoTo Next_Custom_Counter_Err
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long
|
by: Nathan Sokalski |
last post by:
I have a user control that contains three variables which are accessed through public properties. They are declared immediately below the "Web Form Designer Generated Code" section. Every time an event is fired by one of the controls contained in the User Control, these variable are reset. Here is my current code (I have a little more to add later, right now I am just concerned about the variables getting reset):
Public Class DatePicker2...
|
by: Kd |
last post by:
I have the OrderNo set as the following
Right(Format(Date(),"yyyy"),1) & Format(Date(),"mm") &
"-"Format(,"000")+1
This works great until
I get to a new month and need the numbers to reset like the following
example
512-001
Anybody have any thoughts to point me in the right direction,got stuck
with making updates to someone elses created database and getting
frustrated.
|
by: junkaccount |
last post by:
Hello,
I currently have a field named QuoteNumber in a table named Quotes.
The field is set as autonumber and is used to assign sequential numbers
as users enter information in the table through a form. This number is
mainly used later as reference when a customer calls our office with
questions. We use the quote number to retrieve their related
information through a query. This QuoteNumber field is NOT a primary
key. It is not...
|
by: cansnowboard |
last post by:
I built a file management database which generates a file number based on the year.
i.e. 2006-PRRS-13
The criteria was that each file should be unique. So I took the primary key, added one to it and moved on.
Several months down the road they came back and said did we mention the file number needs to reset to zero each year.
They click a button to generate the file number.
| | |
by: cassey14 |
last post by:
Hi Guys...
Im doing a system and it really make my head ache..i hope somebody help me..
I need to have an id like this "ABCD-07-000001"... I dont know how will I work on that thing.. ABCD always a default character..07 is the last two digit of the year and it should be reset everytime a year change..and the last six digit should be incremented by 1 everytime they add a new record but it should be reset when the year change..please help me...
|
by: DeanO |
last post by:
I am trying to set my autonumber field to reset automatically at the beginning of each new year. This is a three digit field and is used with the date for a report number field. The number is currently set to auto fill and looks like this
00/00/0000-000. I want the last three digits to reset with the new year date field. Both fields are hidden and combine in the report number text box.
|
by: medic3212 |
last post by:
Greatings,
I am a vbs newbie, about two months of class.
I am working on a dispatching program which uses an incrementing call number. The call number is made up of two separate parts, the last two digits of the year, (which I have worked out), and an integer, “CallCount” i.e. 07 0115. The two are displayed in a separate text boxes. Each time a call is added, (the “AddCall” button clicked), the CallCount is increased by one. I need to...
|
by: joegood |
last post by:
Hello All
First of all I am an access newbie and I am looking for a bit of help on this. I have looked at other similar articles but I a failing in my implementation.
I have an number generated automatically using this code
Private Sub Form_Dirty(Cancel As Integer)
Me!.Value = (DMax("ReportID", "Reports") + 1)
End Sub
The number format is as follows BR then year(=Format(Date(),"""BR""yy")001
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
