Auto Sequence Number For Reference Number

Dec 8, 2005

The title probably doesn't give an idea of what I need, so here goes.

We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.

example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7
120705SEARLH01 1st order taken by RLH for Seagate on 12/7

I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".

I have a query that generates the first portion (date, customer, and initials):

SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO
FROM PickUpReqData;

But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)

Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.

Thanks.

Gary

View Replies


ADVERTISEMENT

Auto-Sequence Number When Deleting Field Midpoint

Nov 21, 2011

At present I am running Revit Architecture with DBLink to access to edit fields for shelving etc.

What i am doing is fitting out warehouses with 600+ shelving units and they are all numbered.

These numbers are tagged in the drawing.

What I am hoping to do is export the data to access (completed) then amend any changes within access.

Normally, we would put say 600 shelving units on a drawing all tagged 1-600. But then the client may want to delete shelving unit 321.

Leaving the drawing with all shelving units tagged 1-320,322-600.

How would i go about it so access renumbers all data after the deleted field (ie number 321)?

View 2 Replies View Related

Modules & VBA :: Sequence Number In Query Use A Starting Number

Sep 20, 2014

creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5

---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------

View 2 Replies View Related

Appending Records With A Desired Auto Number In A Table With Auto Number

Oct 26, 2005

Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.

I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.

Jim

View 7 Replies View Related

Forms :: Auto Generate File Number Based On Number Of Records In Year

Jan 21, 2014

I have a form [IUDATA]

I have a add record button.

I have a date field [DATEIN]

I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.

View 12 Replies View Related

Tables :: Procedure To Restrict / Stop Auto Number Increment For Certain Number Of Record Count

Mar 16, 2014

I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.

View 8 Replies View Related

Auto Increasing A Number, Depending On Maximum Number When Condition Is Met

Sep 18, 2006

Hi,

I'm trying to get the maximum number in a table field to increase it by one depending on the member that is selected in a drop down in a field.

I have three tables: members, programs and times. Each member can have N programs and each program can be broadcast N times.

Each member has a three digit code, like XXX. Each program has the three digit code of the member + three numbers that are supposed to auto increment. That is, the first program of member X with the member code XXX is called XXX001.

What I'm trying to do is that when a new program is filled in and I select the member, then the program code should update automatically, adding one to the latest program by that member.

That is, if the last program by member X that was inserted in the database is XXX010, then if a new program is inserted it should automatically be XXX011, even though programs by other members have been added in between.

This is the code I use now, for the AfterUpdate when selecting the member in a dropdown in the form. But although I've played around a bit, I just get error messages...

Private Sub medlemsruta_AfterUpdate()
Dim medlemskod
medlemskod = Me![medlemsruta].Column(2)

Dim strMax As String

strMax = DMax("programs_kod", "table_programs", "Left$(programs_kod, 3) = medlemskod")
Me!program_kod = Left$(strMax, 3) & Format$(Val(Right$(strMax, 3)) + 1, "000")
End Sub

Medlemsruta is a dropdown where one selects the member from the members table, where the three digit code is in the third column (Column(2)).

I'm trying to use DMax to get the maximum number for the particular member and after that adding 1 to that for the new program code.

Grateful for any advice! Thanks!

View 3 Replies View Related

General :: Getting Invoice Number Field To Auto Generate Next Number

Jun 2, 2014

I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0

Code:

Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

invoice numbers are 04024, 04025 etc...how I keep the formatiing?

View 5 Replies View Related

Auto Number, Year, Month Number Combination

Apr 20, 2008

I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

View 10 Replies View Related

Query That Result Incremental Number, With No Auto-Number

Sep 10, 2006

Well, here's my database :

Name : String
Address : String

I want to add an automatically generated column, "no.", to show which number it's in. Like this :

No. Name Address
1. A Unknown
2. B Unknown2

The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.

Is it possible to generate query that have "no." column in it ?

View 1 Replies View Related

General :: Auto Number PK Change Starting Number?

Dec 18, 2013

I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.)

View 7 Replies View Related

Creating A Serial Number That Is NOT Auto-number

Mar 20, 2012

I have a table called "OrderDetails" with following fields:

Num
OrderID (Primary key)
Product
Quantity
Price

I want to create a data entry subform that can used to enter order details in this table such that, for a given OrderID, the Num field is automatically set to previous number + 1. For example, for OrderID = 12, if there are 4 products that need to be entered, the 4 records should automatically take 1 , 2, 3, 4.

View 6 Replies View Related

Sequence Break In Key Number Field

Mar 30, 2006

Hi!

1. If accidently a record or two in a sequence are deleted, how will you know that this has happened! What type of validation can be enforcedto prevent this?

2. How do you make your Tables, Quries, Forms, Reports, Macro tamper proof?

Please help!

Orsonros

View 2 Replies View Related

Adding Sequence Number To A Query

Apr 3, 2007

I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?

Thanks

View 3 Replies View Related

Tables :: How To Get Sequence Number From Table

Sep 17, 2013

i want to know such a method that if i give a number, the database find me the next available number from the given list in the table.for example, i have a table having list of numbers like 6500, 6501, 6502 etc, I give the number 6500, the database should give me the next available number in sequence from that given list in the table.

View 7 Replies View Related

Insert Sequence Number In Query

Nov 12, 2013

I would like to insert a sequence number into a query that currently returns 7367 rows.

Here's what it looks like now:

ID_NUM
DTE_FIN_TRANS_RCV

1322
9/27/2012

1322
7/10/2012

1011
7/2/1999

1011
9/22/1999

1011
10/21/1999

Here's the desired outcome with sequences:

ID_NUM
DTE_FIN_TRANS_RCV
SEQUENCE

1322
9/27/2012
1

1322
7/10/2012
2

1011
7/2/1999
1

1011
9/22/1999
2

1011
10/21/1999
3

All I need is a way to number rows with a value higher than the previous for that particular ID_NUM.

View 4 Replies View Related

Use 2 Different Controls To Increment Number Sequence

Feb 1, 2013

I'm trying to code for a 'duplicate record' button on a form that, as well as copying the values of all the controls of the previous record to the new record, makes a certain control (which contains a number) increment as appropriate.

There are two controls on the form which contain the start and end values of a numerical sequence (of potentially any length) e.g. the range 48-82 shows 48 in text box boxREGSEQSTART and 82 in the text box boxREGSEQEND OR the single value 99 shows 99 in text box boxREGSEQSTART and text box boxREGSEQEND is empty. In a record, the value of boxREGSEQEND would always be higher than that of boxREGSEQSTART or would be blank.

The appropriate incrementing action would be:

After clicking 'Duplicate Record' button, If there is no value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSEQSTART should be 1 greater than the value of boxREGSEQSTART in the record it has been copied from.

i.e. the record being copied is records a single item, a single value, and the next record should show the sequence moved on by one

OR

If there is a value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSTART should be 1 greater than the value of boxREGSEQEND in the record it has been copied from.

i.e. the record being copied is recording multiple items, a sequence, but the next record should still show the sequence moved on by one

I can get the duplication to work no problem, and I can get the incrementing to work fine for the first set of conditions, but can't for the life of me get it to increment when there is a range!

The logic (in my head, expressed as best I can in the macro language ) for the whole sequence of actions goes like so:

RunMenuCommandCommand SelectRecordRunMenuCommandCommand CopyRunMenuCommandRecordsGoToNewRunMenuCommandCommand SelectRecordRunMenuCommandCommand Paste
If [boxREGSEQEND]=[IsNull] Then
GoToControlControl Name boxREGSEQSTARTSetValueItem = [boxREGSEQSTART]
Expression = [boxREGSEQSTART]+1Else
GoToControlControl Name boxREGSEQSTARTSetValueItem = [boxREGSEQSTART]
Expression = [boxREGSEQEND]+1GoToControlControl Name boxREGSEQENDSetValueItem = [boxREGSEQEND]
Expression = [boxREGSEQEND]=Null

Which I would assume would tell the program to copy the current record, make a new one and paste the copy, then if there was a range recorded in the original, start the numbering of the new record one number higher than the end of that range and clear the way in the new form in case it also records a range, otherwise just increment the sequence along by one.

View 3 Replies View Related

Generate Record Number Sequence In Query?

Mar 23, 2006

Hi -

Suppose I have a generic query that returns a group of records.

Is there some way to generate a field containing the number sequence of each record? E.g. if there are 10 records, generate the numbers 1 through 10?

Must be a simple way to do this, I'm just a bit dense today...

Thanks,

-g

View 1 Replies View Related

Queries :: Sequence Number Reset At Each ID Exchange

Nov 18, 2014

I have two tables - one with parent Records, the other with child records. The ID links the two tables .I want to add a sequence number on the child table which resets at each ID change. What would be the best way to accomplish this?

Code:
PC_ID ID Name Address

P 1 Parent1 Address1
P 2 Parent2 Address1

PC_ID ID Name Address SEQ

C 1 Child1 Address1 1
C 1 Child2 Address1 2
C 1 Child3 Address1 3
C 1 Child3 Address1 4
C 2 Child1 Address1 1
C 2 Child2 Address1 2
C 2 Child3 Address1 3

View 4 Replies View Related

Modules & VBA :: Database With 7 Columns - How To Number In Sequence

Jun 11, 2015

I have a table in a database with 7 columns. The data is sorted by Date1 in descending order.

For each pid I want to put the sequence numbers
First record has two conditions
If string 2 is null then start numbering from sequence1
If string 2 is not null then start numbering from sequence2
If string 2 = string1 then
Sequence1 = 0
Second record has two conditions
Number sequence2 with the value 2 or 3 depending on the line one
If string 2 = string1 then
Sequence1 = 0
Else
Sequence1 = next number
Same condition for the rest of records

View 5 Replies View Related

Create A Sequence Number For Each Record Associated To Foreign Key

Jun 6, 2012

I have data in a table that looks like the first table and I would like to write a query to update the sequence so it looks like the second table.

PersonID
Color
Sequence

1
Red

1
Blue

1
Green

2
Blue

3
Red

3
Blue

PersonID
Color
Sequence

1
Red
1

1
Blue
2

1
Green
3

2
Blue
1

3
Red
1

3
Blue
2

View 2 Replies View Related

Auto Number Not Displaying Number

Mar 17, 2005

I have a form with an Auto number field. When the form is opened in new record mode the auto number field displays the following: (Auto Number) instead of the actual number that it has generated. I want the number to be displayed

View 6 Replies View Related

Auto Number Starting At A Particular Number

Jul 25, 2005

Hi, I have a form with some fields on it, there is one called Pro Number. what I would like to happen is when a new record is created, it starts at a certain number and continues to increment by one. In other words, the first record would be 5600 and the next new record would be 5601 etc.

I did have this working in an previous database, by creating a append query to start the number, but that does not seem to be working now...

Any help would be very much apprecited.

David

View 14 Replies View Related

Queries :: Make Table To Add Sequence Number To Records

Jan 14, 2014

I am looking to make a table which adds a sequence number to my records.

Example data would look like this

Acc no Date
1234535 01/01/2013
1234535 05/01/2013
1234535 11/01/2013
9876543 02/01/2013
9876543 22/02/2013
9876543 01/03/2013

I want it to add a deq based on the account no then date in sequence oldest to newest.

Acc no Date Seq
1234535 01/01/2013 1
1234535 05/01/2013 2
1234535 11/01/2013 3
9876543 02/01/2013 1
9876543 22/02/2013 2
9876543 01/03/2013 3

View 5 Replies View Related

General :: DLookup - Resetting Sequence Number In A Table?

Oct 8, 2013

Occasionally a user of mine needs to reset a sequence number in a table.

A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:

Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")

...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.

Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.

Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.

What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).

It's almost like the dlookup falls asleep now and won't wake up until the update is run...

View 2 Replies View Related

Queries :: Extract Or Calculate Number Of Records In A Sequence

Jan 4, 2014

How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved