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
Dec 30 '05
22  5633 
Peter Sutton wrote: 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.
Actually using the DefaultValue property for this is a poor choice in a
multi-user system as it allows mutliple users to grab the same value if they
both start records before either one saves. Also it won't work at all in a
continuous form.
Assigning the value in the BeforeUpdate event is the best place to prevent
collisions.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Sat, 31 Dec 2005 05:40:52 GMT, "Rick Brandt"
<ri*********@ho tmail.com> wrote: Peter Sutton wrote: 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.
Actually using the DefaultValue property for this is a poor choice in a
multi-user system as it allows mutliple users to grab the same value if they
both start records before either one saves. Also it won't work at all in a
continuous form.
Assigning the value in the BeforeUpdate event is the best place to prevent
collisions.
True what you say about collisions.
In the few cases, I've had like this, users want to see the allocated
number at the start of the process and with not a lot of users adding
records infrequently, I put a check in the BeforeUpdate event to check
and fix any collisions.
Horses for courses I guess.
P
Rick
Thanks for all your help first of all
I changed this as you sugested and even changed my table field from
OrderDate to
CreateDate
When I put
ResponseNo: "R" & Format(CreateDa te(),"yy") & "-" &
Format([PONum],"000")
I get undefined function'Create Date' in expression
What am I doing wrong
Ken
Kd wrote: Rick
Thanks for all your help first of all
I changed this as you sugested and even changed my table field from
OrderDate to
CreateDate
When I put
ResponseNo: "R" & Format(CreateDa te(),"yy") & "-" &
Format([PONum],"000")
I get undefined function'Create Date' in expression
What am I doing wrong
Ken
CreateDate (being the name of a field and not a function) should not have ()
after it.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Thanks
That worked
now all I have to do is get PONum to reset to 001
deleted all records from last year except 5 and
new PONum forr 2006 came out R06-006
think I have tried
Then your DMax assignment changes to...
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year (Date()), 1,
1)"), 0)+1
everywhere i can think of on before update events
still cant get number to set to R06-001
Kd wrote: think I have tried
Then your DMax assignment changes to...
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year (Date()), 1,
1)"), 0)+1
everywhere i can think of on before update events
still cant get number to set to R06-001
Did you try the BeforeUpdate event of the form?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Kd wrote: yes i did
And what do you get? The expression is using Nz() so when there are no records
yet created in the year 2006 the DMax() will return Null and then the Nz()
pretty much HAS to return zero to which we add 1. There is not much that can go
wrong with that.
Just to clarify...the DMax() statement will only produce the incrementing PONum.
It will not give you a result that looks like "R06-001". You use a default
value of Now() or Date() for the CreateDate, and you assign the value of PONum
in the BeforeUpdate event of the form using the DMax() expression. It is only
AFTER the record has been created that you can use your Format() expression to
give you the "R06-001" output.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Well I am gonna learn a lot of paitence working with this stuff
cleaned out table have 3 fields
PoNum - lng int
Create Date - short Date default Date()
Customer -Text
Query
PoNum
Create Date
Customer
ResponseNo: "R" & Format(CreateDa te(),"yy") & "-" &
Format([PONum],"000")
Form
PONum default value=DMax("[PONum]","[PO Table]")+1
CreateDate default value date()
Customer
ResponseNo
BeforeUpDate on form
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year (Date()), 1,
1)"), 0)+1
I am sure I have something in wrong place
Any help is so appreceated
Ken
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
