[lenestopage]

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]

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?

[lenestopage]

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!

[OBP]

So you want the value to go in the AutoCatalog ID?

[lenestopage]

the catalog Id field if possible - the auto catalog field was created when I was trying different expressions and ideas.

[OBP]

Are you using a normal data entry form to enter the new records or a special "only new records" form?

[lenestopage]

just a normal data entry form

[OBP]

Do you know how to find the After Update Event Procedure of the "Date Received" field?

[lenestopage]

yes I just don't know what to put in there

[OBP]

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]

I have done that so far and Im getting the message box.

[OBP]

Does it give you the correct date?

[lenestopage]

yes it gives me the last date of the previous input date

[OBP]

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.