gauton
asked on
Increase numeric value by one for each day
I have a table:
ID AutoNumber
Alpha Text
BatchNumber Number
Here is what I need to be able to do:
The BatchNumber needs to always start with 900 for each new day and as records are added the BatchNumber is increased by one.
At the start of a new day, the BatchNumber needs to start again with 900.
Anyone got any suggestions?
ID AutoNumber
Alpha Text
BatchNumber Number
Here is what I need to be able to do:
The BatchNumber needs to always start with 900 for each new day and as records are added the BatchNumber is increased by one.
At the start of a new day, the BatchNumber needs to start again with 900.
Anyone got any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
btw, i used
format(varmaxdate, "dd m yyyy") b/c i am in europe
Me.Batchnumber = DMax ("batchnumber", "tblyourtable", "datecreated = #" & Format(varMaxDate, "dd m yyyy") & "#") + 1
if u are in the US, just use:
Me.Batchnumber = DMax "batchnumber", "tblyourtable", "datecreated = #" & varMaxDate & "#") + 1
djeez Jim, u type very very fast.... :-)
cheers
Ricky
format(varmaxdate, "dd m yyyy") b/c i am in europe
Me.Batchnumber = DMax ("batchnumber", "tblyourtable", "datecreated = #" & Format(varMaxDate, "dd m yyyy") & "#") + 1
if u are in the US, just use:
Me.Batchnumber = DMax "batchnumber", "tblyourtable", "datecreated = #" & varMaxDate & "#") + 1
djeez Jim, u type very very fast.... :-)
cheers
Ricky
Ricky,
Not as fast as you think<g>. And I really hate to poke a hole in your solution, but when using DMax, always remind people that it will not work in a multi-user situation. It's possible for two (or more!) users to get the same key value.
Jim.
Not as fast as you think<g>. And I really hate to poke a hole in your solution, but when using DMax, always remind people that it will not work in a multi-user situation. It's possible for two (or more!) users to get the same key value.
Jim.
True Jim,
something i learned using Cobol,
retrieve data,
check data,
user adjusts data,
retrieve data, compare, if necessary adjust automatic or warning,
lock data,
write data
(or something in that fashion...)
;-)
cheers
Ricky
something i learned using Cobol,
retrieve data,
check data,
user adjusts data,
retrieve data, compare, if necessary adjust automatic or warning,
lock data,
write data
(or something in that fashion...)
;-)
cheers
Ricky
ASKER
Jim,
The small sample MDB would be great.
Garland
The small sample MDB would be great.
Garland
ASKER
Jim,
The small sample MDB would be great.
Garland
The small sample MDB would be great.
Garland
need an e-mail address.
Jim.
Jim.
If you don't want to post it, send an e-mail to jimdettman@earthlink.net
Jim.
Jim.
here is an example:
(first, create a new field in your table --> "DateCreated" = Date field, default value = Date() )
in this example the name of the table = tblYourTable (change that according to your real name)
Private Sub cmdNewRecord_Click()
DoCmd.GoToRecord , , acNewRec
CreateBatch
End Sub
Private Sub CreateBatch()
Dim varMax As Variant
Dim varMaxDate As Variant
If DCount("ID", "tblYourTable") = 0 Then
'This is the first Record in the table
Me.Batchnumber = 900
Else
If Date > DMax("DateCreated", "tblyourTable") Then
'New date
Me.Batchnumber = 900
Else
'Find highest date
varMaxDate = DMax("datecreated", "tblYourtable")
'find highest number for this date
Me.Batchnumber = DMax("batchnumber", "tblyourtable", "datecreated = #" & Format(varMaxDate, "dd m yyyy") & "#") + 1
End If
End If
End Sub
cheers
Ricky