Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

HELP! Access 2007 expression for incremental number


(!)

lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
07-Aug-2012, 10:46 AM #1
HELP! Access 2007 expression for incremental number
I have an access db with a table (Table A). I have a date field (Date - Present) and a Integer field (Sequence). I am trying to append a number to the end of my date in the following format: 20120807-01 and have it reset back to 01 daily.

I was able to get this: 20120807-1 but the end number just keeps going it doesn't reset to 1 the next day.

This is the expression I have so far: =Format([Date - Present],"yyyymmdd" & "-" & [Sequence]

Any suggestions to make this expression reset to 1 daily or does anyone have any other expressions that might work?

Thanks...I have also searched high and low and found a few possible solutions but none of them have worked thus far.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
07-Aug-2012, 01:27 PM #2
lenestopage, welcome to the Forum.
You have found the limitation of an Expression or Calculated field.
I am not sure how you are Incrementing the "Sequence" field, possibly as an Autonumber.
The control over the Sequence field is the key to what you want to do.
So you have to use VBA to do so, either using a VBA Recordset or a Dlookup function to establish whether or not the Current date - present is the same as the "last" date - present record in the table.
Can you describe the table structure or show a screen print of it?
__________________
OBP
I do not give up easily
lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
09-Aug-2012, 12:57 PM #3
Thanks
I have attached some screens of my database. Unfortunately I am a very inexperienced with VB, any help would be greatly appreciated.

The Sequence field I spoke of earlier is represented as Catalog Id
Date - Present is represented as Present Date
Table A is represented as Table1

Thanks again for any and all help!
Attached Thumbnails
HELP! Access 2007 expression for incremental number-record-2-2.png   HELP! Access 2007 expression for incremental number-fields.png   HELP! Access 2007 expression for incremental number-expression-used-so-far.png   HELP! Access 2007 expression for incremental number-record-1-1.png  
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 01:00 PM #4
So you want the value to go in the AutoCatalog ID?
lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
09-Aug-2012, 02:31 PM #5
the catalog Id field if possible - the auto catalog field was created when I was trying different expressions and ideas.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
10-Aug-2012, 05:14 AM #6
Are you using a normal data entry form to enter the new records or a special "only new records" form?
lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
10-Aug-2012, 07:47 AM #7
just a normal data entry form
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
10-Aug-2012, 08:03 AM #8
Do you know how to find the After Update Event Procedure of the "Date Received" field?
lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
10-Aug-2012, 09:23 AM #9
yes I just don't know what to put in there
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
10-Aug-2012, 11:47 AM #10
OK, we will start off slowly, the code below expects to find at least one record already in the table and showing on the form.
Add this to the actual VBA Event of the "Date Received" field in the VBA Editor (select Event Procedure and then click the 3 small dots in the after update event)

Dim rs As Object
On Error GoTo errorcatch
if not me.newrecord then exit sub
Set rs = Me.Recordset.Clone
rs.movelast
msgbox rs.[Present Date]

rs.close
set rs = nothing

exit sub
errorcatch:
MsgBox Err.Description

At the moment all this code should do is when you have a new record and enter the "Date Received" value and enter or tab you should get a Message telling you the date of the previous record (providing the records are in date order)
lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
10-Aug-2012, 01:49 PM #11
I have done that so far and Im getting the message box.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
10-Aug-2012, 02:52 PM #12
Does it give you the correct date?
lenestopage's Avatar
lenestopage lenestopage is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Beginner
10-Aug-2012, 04:03 PM #13
yes it gives me the last date of the previous input date
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
11-Aug-2012, 05:52 AM #14
OK, next step, we need the number as well so add this to the message box

Replace this line

msgbox rs.[Present Date]

with

msgbox rs.[Catalog Id] & " - " & rs.[Present Date] & " - " & me.[Present Date]

That should show you the previous CatalogId & Date and the present date from the current record.
After replacing the line of code add the following lines

if rs.[Present Date] = me.[Present Date] then
me.[Catalog Id] = format(me.[Present Date], "yyyymmdd"
else
me.[Catalog Id] = format(me.[Present Date], "yyyymmdd"
end if

Now I can't add the number to the id yet because we haven't discussed how many records per day you are likely to get, if it is less than 100 we should use
01 - 99
if it is more than 100 we should use
001 - 999

So what value is currently in your last record, the reason we need this is to identify how many characters we need to cout out to get just the number.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑