Hello there,
Does anyone know how to create a sequential
record number field in a query??
Thanks,
James
4  8840 
It can be done by including a calculated field based on a Function, but you
have to be very careful, because I've found that (1) you must pass it a
field or every record will be "1" as it will run only once, and (2) that
field must be the unique id, and you must check that you run the function
only once per unique id, and not for any unique id's lower* than the highest
you've done already, because it may well be called more than once for each
record (don't ask me why).
* if sorted in ASC, higher if sorted DESC
Larry Linson
Microsoft Access MVP
"James" <ja*********@nt lworld.com> wrote in message
news:TfUic.171$ cy3.74@newsfe1-win... Hello there,
Does anyone know how to create a sequential
record number field in a query??
Thanks,
James
See: http://www.lebans.com/rownumber.htm
Rownumber.zip is a database containing functions for the automatic row
numbering of Forms, SubForms and Queries.
Updated Oct. 13 by Allen Browne. Includes error handling and cleaned
code.
Here's an update to the Serialize function by Peter Schroeder:
Good to hear. FWIW, here's the version I came up with today, based off
of your code and Ken's(Getz) suggestion, with a few changes:
Function Serialize(qryna me As String, keyname As String, keyvalue) As
Long
Dim rs As Recordset
On Error GoTo Err_Serialize
Set rs = CurrentDb.OpenR ecordset(qrynam e, dbOpenDynaset, dbReadOnly)
rs.FindFirst Application.Bui ldCriteria(keyn ame, rs.Fields(keyna me).Type,
keyvalue)
Serialize = Nz(rs.AbsoluteP osition, -1) + 1
Err_Serialize:
rs.Close
Set rs = Nothing
End Function
Peter Schroeder
--
HTH
Stephen Lebans http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"James" <ja*********@nt lworld.com> wrote in message
news:TfUic.171$ cy3.74@newsfe1-win... Hello there,
Does anyone know how to create a sequential
record number field in a query??
Thanks,
James
Hi Stephen,
wow, that's brilliant!
I can cope with this, however, is there an easier way of doing it?
James
"Stephen Lebans" <Fo************ *************** *************@l invalid.com>
wrote in message news:iQ******** *************@u rsa-nb00s0.nbnet.nb .ca... See:
http://www.lebans.com/rownumber.htm
Rownumber.zip is a database containing functions for the automatic row
numbering of Forms, SubForms and Queries.
Updated Oct. 13 by Allen Browne. Includes error handling and cleaned
code.
Here's an update to the Serialize function by Peter Schroeder:
Good to hear. FWIW, here's the version I came up with today, based off
of your code and Ken's(Getz) suggestion, with a few changes:
Function Serialize(qryna me As String, keyname As String, keyvalue) As
Long
Dim rs As Recordset
On Error GoTo Err_Serialize
Set rs = CurrentDb.OpenR ecordset(qrynam e, dbOpenDynaset, dbReadOnly)
rs.FindFirst Application.Bui ldCriteria(keyn ame, rs.Fields(keyna me).Type,
keyvalue)
Serialize = Nz(rs.AbsoluteP osition, -1) + 1
Err_Serialize:
rs.Close
Set rs = Nothing
End Function
Peter Schroeder
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"James" <ja*********@nt lworld.com> wrote in message
news:TfUic.171$ cy3.74@newsfe1-win... Hello there,
Does anyone know how to create a sequential
record number field in a query??
Thanks,
James
James wrote: Hi Stephen,
wow, that's brilliant!
I can cope with this, however, is there an easier way of doing it?
James
That's one of the funniest replies I've read in this newsgroup. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Iain Miller |
last post by:
Now this shouldn't be hard but I've been struggling on the best way as to
how to do this one for a day or 3 so I thought I'd ask the assembled
company.....
I'm writing an application that tracks a group of Sales people, the
customers they deal with and the business they transact with them.
I've got my head around all the tables & some of the basic Query structures
OK and am beginning to delve into creating the forms I need to be able...
|
by: John Ortt |
last post by:
> I have a table of dates in ascending order but with varying intervals. I
> would like to create a query to pull out the date (in field 1) and then
pull
> the date from the subsequent record (and store it in field 2).
>
> I would like to run a query that returns all the data in the table plus
the
> record number, in a similar sense to the getuser() command to get the
User's
> Identity, I would like a GetRecord() command.
|
by: Lapchien |
last post by:
I have list of numbers in a table (originally from autonumber in a different
database) from 1 to 1,000,000. The list is not in sequential order - there
are loads of numbers missing. How can I identify what numbers are missing?
Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise
|
by: GAVO. |
last post by:
Hello every one
I have a database with a form called "frmOrders" on that for I need to
create a sequential number for each city apart from the original autonumber.
So the table "tblorders" would look something like this:
OrderID (Autonumber) SeqNo City
1 1 London
2 1 Madrid
3 ...
|
by: James |
last post by:
Hello there,
Does anyone know how to create a sequential
record number field in a query??
Thanks,
James
| | |
by: longtim |
last post by:
I have been having endless difficulty creating reports/queries that
set any relevent parameters from controls in forms.
I am creating an application under access 2003 but will target access
2000. The access file is in access 2000 format.
I have a form that will hold the relevent parameters for the
query/report that reports the statistics for all job records that
match a certain criteria. These are:
- A Customer Name.
|
by: Anderson |
last post by:
I have a table which has employee number. I have attempted to creat a
function whic will derive a unique number for each record how ever the
code below only returns 10,000 for all records. What I am doing wrong? I
am I right in saying that In assuming that I dont to loop since I am
returnign this for a every record in query.
Your help will be greatly appreciated.
Function Generate_Number(emp_no As Variant) As Variant
Dim strSQL As...
|
by: NomoreSpam4Me |
last post by:
Hi there i have a little problem with my invoice.
Here it is:
i have a main menu with buttons, one of my button is "Create new
invoice", when click on it a form pop up so i can enter my information
and one of the field (the user cannot change the info in it.) is
invoice #. Right now, everythime i click on "Create new invoice", the
invoice # add 1. But my problem is sometime the employee dont fill it
(for x reason) and shut it down,...
|
by: John |
last post by:
Hi
I need to assign sequential invoice numbers to orders starting from the last
highest number + 1. I have tried the following code;
UPDATE Orders SET Orders. = DMax("","Orders")+1
WHERE Orders.) Is Null AND ...
The problem is that all orders get the same number which is the last highest
number + 1. Apparently the query does not recalculate DMax("","Orders")+1 for each record and instead only gets the value once in the
|
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: 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: 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...
| |
