Sequential Numbering Question (1 Viewer)

[DeanFran]

A couple years ago I took over maintaining a preventive maintenance DB. There is one aspect I haven't been able to fix. To say I'm not the most qualified VBA person would be an understatement, though I know enough to believe its the answer to this question. One of the functions of the DB is to generate a document called a Request Work Order (RWO), (And of course the resulting record in a table) that is a generic blank work order anyone can do to request you guessed it, work. Anyway, the original designer decided that the RWO numbering scheme would be the Date Serial plus a sequential 2 digit suffix, in other words the first RWO issued today would be 42997-01, then 42997-02, and so on. Tomorrow the first RWO would be 42998-01, etc. I would love to auto generate this number for the users, but I don't have the chops to do it. I assume, that the Date Serial for today must be stored somewhere until tomorrow after the last time the dB is closed for the day for comparison, so that the suffix sequence can be reset to -01 each day. I'm not looking for the answer, but would really appreciate any nudges in the right direction.

 

[Ranman256]

make a query to pull the [RWO] and the bare date values in the RWO. (left 5, no day#)

in the query: qsDateVals,
SELECT [RWO],(Left([RWO],5)) AS DateVal FROM tData;


Now when you need to make a new work order,
get the MAX value for THAT date,
then either start from 1 (if null) or add +1 if this day exists:

btnAddNewOrder_click()
dim vDate, vRet, vNum, vOrder
dim i as integer

vDate = clng(date)
vRet = DMax("[RWO]","qsDateVals","[DateVal]=" & vDate)   'find the max entry for the day

if isNull(vRet) then
   vOrder = vDate & "-01"
else
   i = instr(vRet,"-")
   vNum = mid(vRet,i+1)
   vNum = val(vNum) + 1
   vOrder = vDate & "-" & format(vNum,"00")
endif

txtOrderNum = vOrder     'fill in new order# in the textbox
end sub

 

[The_Doc_Man]

Technically, that 5-digit number isn't the DateSerial value, because DateSerial is a function that returns a date. Not to worry, though.

Offhand, I would say that your date is converted by the simple method of evaluating the expression CLng(Date()) - which will give you an integer number of days since the system reference date, which is 1-Jan-1900 as day 1. Or 31-Dec-1899 as day 0.

Assuming you want a simple monotonically increasing count starting from 1, and recognizing that a work order number with a dash in the middle HAS to be a string, ...

Dim DatSer as String
Dim RWOCt as Long
Dim NewRWO as String

...

DatSer = CStr( CLng( Date() ) )
RWOCt = DCount( "[RWO]", "table-name-goes-here", "Left([RWO], 5) = '" & DatSer & "'" )
NewRWO = DatSer & "-" & CStr( RWOCt + 1 )

Just provide the correct table or query name for the DCount and you will get the number you want autogenerated with no need to remember anything. Note that IF my guess is wrong about the 5-digit number, you can STILL use that code but the first line of the snippet becomes

DatSer = CStr( CLng( Date() ) + FudgeFactor )

I.e. add or subtract some constant you need to align the dates to match your current scheme. But by my calculations, 42997 is 9/19/2017 (TODAY) so I don't think you have a fudge factor.

 

[DeanFran]

Sorry, I think I used the DateSerial moniker incorrectly. I meant the date as an integer, for instance in this case, a field of type Number, a default value of Date() and a Field Size of Long Integer (I assume that's what CLng means), as you both noted. You two have given me some great answers that are frankly still a bit over my head, but I am going to get to work going through them a step at a time, to try to better understand them both.