Daily Reset Sequential Numbering Using Dmax.

[NickTheG]

Scenario I have two tables. Table 1 is for a job and table 2 is for tasks completed on that job. I am looking at not using the auto number and currently has work a system were a ID is genereated based on date i.e 20170921 - 1 were the "-1" increments for every job and these number resets everyday . I currently have this working but resets every year. Also In Table two I want this linked to Table 1 but have an ID that increments 1,2,3,4 based on job so that it resets when a second job is entered. I am looking to change the code below so that it does no reset the sequential number every year but resets everyday. I have been trying to look at the bottom codes but cannot get the numbering to reset daily or if the date does not appear in a table. Could i please request so help on modifying the following code. I am a relative beginner to access database and VBA so I may be looking at the wrong implementation. also would be greateful at any help for sequential numbering bases on a sub form. Table 1 ID Request ID Date of Request Item Subject to change Change category item reference justification for change approval for change rtest yearid monthid dayID BaseID BCustID FormatID First Code 'If IsNull(Me![BaseID]) Then 'Me![BaseID] = Nz(DMax("[BaseID]", "[tblChangeManagement]", "[YearID]='" & Year(Date) & "'"), 0) + 1 'End If 'Me![CustID] = [FormatID] & [YearID] & [MonthID] & [DayID] & "-" & Format([BaseID], "00000") 'End If Second Code If DLookup("DateOfRequest", "tblChangeManagement", Date) Then Me.[BaseID] = Format(Nz(DMax("[BaseID]", "[tblChangeManagement]", "[YearID]='" & Year(Date) & "'"), 0) + 1) Else Me.[BaseID] = 1 End If Me![CustID] = [FormatID] & [YearID] & [MonthID] & [DayID] & "-" & Format([BaseID], "00000") End Sub

[ranman256]

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

in the query: qsDateVals,
SELECT [RWO],(Left([RWO],8)) 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 = FORMAT(date,"yyyymmdd")
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

[NickTheG]

What does RWO refer to sorry. I cannot seem to gt it passed the first part of the if statement. As it will always give the value I set were its "-01"

[ranman256]

whatever key field you need it to be.
mine is RWO.

[NickTheG]

Apologies I didn't get your first statement make a query to pull the [RWO] and the bare date values in the RWO. Thankyou in advance for replying and being a massive help.