Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 17th, 2003, 01:01 PM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Urgently need help...

Hello, all!

I need to be able to populate a field in a form that will return an incremented number, and that will "re-start" at 1 on the next day. It is NOT my primary key (have an autonum field for that).

For example: Let's say today (10/17/03) I have logged in 25 entries. I would like a field in my form to show 1, then 2, then 3, etc. for each successive record entered on that date. Tomorrow (10/18/03) I would like the same field to show 1, then 2, then 3, etc. for each successive record recorded on that date (and so on...). The Form Field would be something like "Invoice number" (I'll rename to make it fit my situation). The important thing is that it increment automatically when starting a new record, and that it re-start on each successive day. I have a "Date In" field already in the table and form that automatically populates with the current date.

How do I implement this? Unfortunately, I have a superior who is adamant that we be able to do this...:(. I am relatively competent in Access, but have no experience whatsoever with VBA. Can someone please help??

Thanks!
Reply With Quote
  #2 (permalink)  
Old October 17th, 2003, 01:59 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe a variation of this one will help?

http://p2p.wrox.com/topic.asp?TOPIC_ID=5337

Let us know.

Sal
Reply With Quote
  #3 (permalink)  
Old October 17th, 2003, 02:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

That referenced topic dealt with a numbering of the rows after the fact. That is, after the rows had already been inserted in the table (or at least that's how I interpreted the issue).

Here, I think the OP wants to number the rows as they are being inserted. Perhaps something along the lines of:
Code:
INSERT INTO Invoices
    (InvoiceNumber,...)
VALUES (SELECT NZ(MAX(InvoiceNumber),0)+1 WHERE InvoiceDate=#invoicedate#),...)
The idea being to find the maximum value in the date in question and add 1 to get the next number. If the row is the first one on the given date, there will be no MAX value (it will be null), so the NZ protects against that...

(I'm no Access programmer, so I can't help with the forms display issue, but I do know a little about SQL so I might be able to help, query wise...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #4 (permalink)  
Old October 17th, 2003, 02:47 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Since there is a DateIn field that is automatically entered she could build a report where the RecordSource is a query having the fields he wants including the DateIn field. He could use an unbound form where the user can select a specific date or between two dates as parameter to DateIn field of this query.

Put an unbound textbox in the report detail section.
Set the RecordSource to =1
Set the RunSum set to OverGroup or OverAll depending on how he watts the data.

This will display the count for each day
Hope this helps.
Reply With Quote
  #5 (permalink)  
Old October 17th, 2003, 03:04 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Jeff, This will work

SELECT (SELECT COUNT(*) FROM yourtable T2 WHERE T1.Today=T2.Today AND T2.EntryDate<=T1.EntryDate) AS TheeCount, T1.EntryDate, T1.Today
FROM yourtable AS T1
WHERE ((((SELECT COUNT(*) FROM yourtable T2 WHERE T1.Today=T2.Today AND T2.EntryDate<=T1.EntryDate))<>0));


I checked it on my PC. Here is what you do.

Make sure that there are two date fields
Today (is a date field that only keeps the current date IE 10/17/2003
          Dafault Value is Date()
EntriTime (date field formated to keep date and time)
         Default Value is Now()











Sal
Reply With Quote
  #6 (permalink)  
Old October 17th, 2003, 03:18 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Actually, to ensure uniquenes use an autonumber field instead for the EntryTime field.
there is a possibility of two or more records being inserted in one second.

Sal
Reply With Quote
  #7 (permalink)  
Old October 17th, 2003, 05:23 PM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff: I think this is more along the lines of what I need. Now to be really stupid... how/where should I insert the code you suggest?

My table is set up with the following fields:

ID (autonum; PK; used as FK in several other tables)
DateIn (default value: Date() )
TimeIn (default value: Time() )
.... there are several other fields, but not relevant to my original post...

What I would like to do is create a "new" field/form field that will show: (a) current date as in the DateIn column and (b) a sequential number (NOT the autonum in field [ID]) that will increment automatically with each new record added. When the date advances, the sequential portion of this new field should start numbering again at "1". It is unlikely I will need more than 3 digits in the sequential number.

What we are doing is tracking the number of "job requests" in each day. Some days are busy, others not... It is important to my boss that we can keep track "on the fly" of "what job number we are up to" during a particular day. This is not so much for reporting issues (I have a query to handle that issue already) but to make data entry easier for the persons using the DB in their daily tasks. Needless to say, no one in the department knows Access... so ease of use is essential.

What we do is enter the "job request number" on a requisition form. My boss does NOT want us to be entering "1,235" as a "job request number." We normally don't go over 50-100 jobs per day (though it has happened in the past that we will log 125-150 requests in one day!), thus I will need to have 3 digits in my sequential number. To keep all the information together, I would like this "automatically" generated sequential number to appear in the main data entry form that the users will use.

I will need the information to automatically populate when the record is begun, as well. (OnEnter?)

Anyway, thanks for your ideas: your help is GREATLY Appreciated!!









"Here, I think the OP wants to number the rows as they are being inserted. Perhaps something along the lines of:
Code:
INSERT INTO Invoices
    (InvoiceNumber,...)
VALUES (SELECT NZ(MAX(InvoiceNumber),0)+1 WHERE InvoiceDate=#invoicedate#),...)
The idea being to find the maximum value in the date in question and add 1 to get the next number. If the row is the first one on the given date, there will be no MAX value (it will be null), so the NZ protects against that...

(I'm no Access programmer, so I can't help with the forms display issue, but I do know a little about SQL so I might be able to help, query wise...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

[/quote]
Reply With Quote
  #8 (permalink)  
Old October 17th, 2003, 08:10 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Scott,
Please double check my propossed solution. Since you already have an auto number. I can guarantee that this will always work assigning the correct number to your record, as long as you never delete any records. Like This

ID DailyID DateIn TimeIn
105 1 11/16/2003 9:01:47 AM
106 2 11/16/2003 9:01:48 AM
107 3 11/16/2003 9:01:48 AM
108 4 11/16/2003 4:45:00 PM
109 1 11/17/2003 8:59:00 AM First one
110 2 11/17/2003 10:00:00 AM

You get the idea

By using this in a query, you will not have to worry about where to put what code. the query takes care of it all for you. Just do not delete any records from it, and the numbers will never change. If you delete a record, It should not matter much.



SELECT T1.ID, (SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID) AS DailyID, T1.TimeIn, T1.DateIn
FROM T1
WHERE ((((SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID))<>0));

By the way, substitute T1 with the name of your table ans voila, you get

ID DailyID TimeIn DateIn All Other fields here
__________________________________________________ __________
1 1 12/30/1899 19:52:03 10/17/2003
2 2 12/30/1899 19:52:05 10/17/2003
3 3 12/30/1899 19:52:08 10/17/2003
4 4 12/30/1899 19:52:09 10/17/2003
5 5 12/30/1899 19:52:09 10/17/2003
6 6 12/30/1899 19:52:09 10/17/2003
7 7 12/30/1899 19:52:09 10/17/2003
8 8 12/30/1899 19:52:10 10/17/2003
9 9 12/30/1899 19:52:10 10/17/2003
10 1 12/30/1899 19:52:32 10/18/2003
11 2 12/30/1899 19:52:37 10/18/2003
12 3 12/30/1899 19:52:40 10/18/2003
13 4 12/30/1899 19:52:41 10/18/2003
14 5 12/30/1899 19:52:41 10/18/2003
15 6 12/30/1899 19:52:42 10/18/2003
16 7 12/30/1899 19:52:43 10/18/2003

It works. Notice multiple entries within the same second, and no problem.

Let me know if you do not know where to put this in the query.



Sal
Reply With Quote
  #9 (permalink)  
Old October 17th, 2003, 09:20 PM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sal: Thanks GREATLY for the reply!! It looks great and will work on implementing it tomorrow when I go back into the office. However, where should I put the query? And could you give a newbie a quick explain of T1, T2 etc....? I hate to be such a pain in the ***, but I've struggled with this one so long, my brain is fried! Also, will this "store" the information in a table for retrieval at a later time? My boss wants to be able to, say, pull up a listing (much like the one you have inserted!) on a daily, weekly, monthly, quarterly, etc... basis. So, for example, on 10/31/03, she may choose to run a report showing all entries (with the job log "sequencing" intact) for 10/01/03....

Thanks in advance from a non-IT person. You know what they say, a little knowledge is a dangerous thing! Unfortunately in this situation, I have no choice but to come up with a solution! ARRRGH!

Thanks again,
Scott
New York City


Quote:
quote:Originally posted by sal
 Scott,
Please double check my propossed solution. Since you already have an auto number. I can guarantee that this will always work assigning the correct number to your record, as long as you never delete any records. Like This

ID DailyID DateIn TimeIn
105 1 11/16/2003 9:01:47 AM
106 2 11/16/2003 9:01:48 AM
107 3 11/16/2003 9:01:48 AM
108 4 11/16/2003 4:45:00 PM
109 1 11/17/2003 8:59:00 AM First one
110 2 11/17/2003 10:00:00 AM

You get the idea

By using this in a query, you will not have to worry about where to put what code. the query takes care of it all for you. Just do not delete any records from it, and the numbers will never change. If you delete a record, It should not matter much.



SELECT T1.ID, (SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID) AS DailyID, T1.TimeIn, T1.DateIn
FROM T1
WHERE ((((SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID))<>0));

By the way, substitute T1 with the name of your table ans voila, you get

ID DailyID TimeIn DateIn All Other fields here
__________________________________________________ __________
1 1 12/30/1899 19:52:03 10/17/2003
2 2 12/30/1899 19:52:05 10/17/2003
3 3 12/30/1899 19:52:08 10/17/2003
4 4 12/30/1899 19:52:09 10/17/2003
5 5 12/30/1899 19:52:09 10/17/2003
6 6 12/30/1899 19:52:09 10/17/2003
7 7 12/30/1899 19:52:09 10/17/2003
8 8 12/30/1899 19:52:10 10/17/2003
9 9 12/30/1899 19:52:10 10/17/2003
10 1 12/30/1899 19:52:32 10/18/2003
11 2 12/30/1899 19:52:37 10/18/2003
12 3 12/30/1899 19:52:40 10/18/2003
13 4 12/30/1899 19:52:41 10/18/2003
14 5 12/30/1899 19:52:41 10/18/2003
15 6 12/30/1899 19:52:42 10/18/2003
16 7 12/30/1899 19:52:43 10/18/2003

It works. Notice multiple entries within the same second, and no problem.

Let me know if you do not know where to put this in the query.



Sal
Reply With Quote
  #10 (permalink)  
Old October 17th, 2003, 10:44 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You are welcome,
Click on Queries,
Click on "New" on top
Click "OK" when the "New Query" dialog opens
Close the "Show Tables" dialog when it opens
                  On the top left of your screen you will see "SQL" in Bold letters, click on that and a screen will open and it will have the word "SELECT;" entered. Delete it and paste the code I gave you.
Replace every T1 for the name of your table in your database.
open your query and you should see the results.
after that you can open your query in design view as normal and you can add the remaining fields that you wish to show.

that's it.
time to go to bed, too bad you are working on Saturday.






Sal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
JMeter: Need help Urgently motionless BOOK: Expert One-on-One J2EE Design and Development 0 February 19th, 2008 12:16 PM
Urgently Need Help humayoo Beginning PHP 2 October 16th, 2007 02:39 PM
Need Help Urgently dpkbahuguna Beginning VB 6 2 August 10th, 2006 01:29 AM
urgently need help sumeghagupta .NET Web Services 1 October 5th, 2004 11:38 PM



All times are GMT -4. The time now is 03:51 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.