Next incremental number using the current date 1

[qqp]

Hi!

I'm trying to set a project number in this format 07-20-20-01; 07-20-20-02, etc. I've got it where it will now start at 1 on a new day, but it won't increment to the next number. Using this:

Private Sub Command102_Click()

Dim vDate, vRet, vNum, vOrder
Dim i As Integer
DoCmd.GoToRecord , , acNewRec
Me.CreateDate = Date
DoCmd.RunCommand acCmdSave

vDate = Format(Date, "mm-dd-yy")
vRet = DMax("[createdate]", "DateVals", "[createdate]=" & vDate) 'find the max entry for the day
Debug.Print vRet
Debug.Print lastnumber

If IsNull(vRet) Then
   vOrder = vDate & "-01"
Else
   i = InStr(vRet, "-")
   vNum = Mid(vRet, i + 1)
   vNum = Val(vNum) + 1
   Debug.Print vNum
   Debug.Print vNum + 1
   
   vOrder = vDate & "-" & Format(vNum, "000")
   Debug.Print vOrder
   
End If

Any thoughts? I thank you in advance for your time!

 

[combo]

Where do you store project numbers? In vRet you always get Null or current date as string (mm-dd-yy):
[tt]vRet = DMax("[createdate]", "DateVals", "[createdate]=" & vDate)[/tt]
After null, if no match, you start numbering from 1. You may need [tt]"[createdate] Like " & vDate & "*"[/tt] in criteria instead
If you have matching (exact) date, Instr finds the first hyphen, and next vNum will get the day number. I think this is not what you intend.

BTW, the first number has two digits (vOrder = vDate & "-01"), next ones three (vOrder = vDate & "-" & Format(vNum, "000"). This may disturb evaluating text with DMax function.


combo

 

[Andrzejek]

Your life would be a lot easier if, instead of keeping your ‘project number’ in one multi-value field, you would keep it to a separate table:

[pre]
tblProjNo
ID FldA FldB FldC FldD
1 7 20 20 1
2 7 20 20 2
3 ...
[/pre]
You can format the outcome any way you want to, making the numbers (7, 1) into whatever you want (07, 01) and adding “-“ wherever you need. Calculating next Project Number is a breeze.


---- Andy

There is a great need for a sarcasm font.

 

[qqp]

I currently have a query to get the last number for the day.

SELECT LastSettingID.LastOfUniqueID, LastSettingsQuery.MaxOfSalespeople, LastSettingsQuery.MaxOfMFGs, LastSettingsQuery.MaxOfProdGrp, LastSettingsQuery.Projects, LastSettingsQuery.MaxOfBidders, LastSettingsQuery.MaxOfStatus, LastSettingsQuery.MaxOfReasonCombo, LastSettingsQuery.BidStartDate_BeginR, LastSettingsQuery.BidStartDate_EndR, LastSettingsQuery.MaxOfFollowUpStartDate_BeginR, LastSettingsQuery.MaxOfFollowUpStartDate_EndR, LastSettingsQuery.MaxOfMinAmt, LastSettingsQuery.MaxOfTerritoryEngineerCombo, LastSettingsQuery.MaxOfDesignSpecCombo, LastSettingsQuery.Projects
FROM LastSettingID INNER JOIN LastSettingsQuery ON LastSettingID.LastOfUniqueID = LastSettingsQuery.MaxOfUniqueID;

I think it's overkill, but it gets me what I need EXCEPT for what I need ;-) which is 1 for the first project of the day.

Thank you again for the suggestions!

 

[Andrzejek]

Could you share what are those 'pieces' of your Project Number?
[tt]
07-20-20-01
07-20-20-02[/tt]

I am (probably) wrong here, but I see 07 as July (current month), 20-20 as current year 2020, and 01, 02 as consecutive Project Numbers for July 2020

If I know the logic, I may come up with some way of determining a new and next Project Number....


---- Andy

There is a great need for a sarcasm font.

 

[qqp]

Ok, now I get 1 for the first record of the day, and no incremental number after that, only 1! AH!

Private Sub Command102_Click()

Dim vDate, vRet, vNum, vOrder
Dim i As Integer
DoCmd.GoToRecord , , acNewRec
Me.CreateDate = Date
'DoCmd.RunCommand acCmdSave

vDate = Format(Date, "mm-dd-yy")
vRet = DMax("[createdate]", "qsDateVals", "[createdate]Like " & vDate & "")  'find the max entry for the day
Debug.Print vRet
Debug.Print lastnumber

If IsNull(vRet) Then
   vOrder = vDate & "-01"
Else
   i = InStr(vRet, "-")
   vNum = Mid(vRet, i + 1)
   vNum = Val(vNum) + 1
   Debug.Print "vnum " & vNum
   Debug.Print "vNum+1 " & vNum + 1
   Debug.Print "vret " & vRet
   
   vOrder = vDate & "-" & Format(vNum, "00")
   Debug.Print vOrder
   
End If

I changed this: Like " & vDate & "" by removing the asterisk because it was throwing an error. TIA!

 

[Andrzejek]

Well, that's one way to seek help - ignore ALL questions from people who try to help...


---- Andy

There is a great need for a sarcasm font.

 

[qqp]

Sorry Andy, I posted before I saw any questions. "I am (probably) wrong here, but I see 07 as July (current month), 20-20 as current year 2020, and 01, 02 as consecutive Project Numbers for July 2020 ponder", the example was literally for July 20, 2020. So the first project for the day would be 07-20-20-01, next, 07-20-20-02 etc.

I do greatly appreciate your help and advice! Thank you!

 

[Andrzejek]

How about this, assuming you have a table called MyTable where you have a field ProjectNumber:

Private Function [blue]NewProjNo[/blue]() As String
Dim rst As Recordset
Dim strSQL As String
Dim intLastVal As Integer

strSQL = "Select ProjectNumber " & vbNewLine _
    & " From MyTable " & vbNewLine _
    & " Where ProjectNumber LIKE ('" & Format(Date(), "MM-DD-YY") & "-*')" & vbNewLine _
    & " Order By 1 DESC"

With rst
    .Open strSQL, SomeConnectionHere
    If .RecordCount = 0 Then[green]
        'No Project Numbers for that day[/green]
        [blue]NewProjNo[/blue] = Format(Date(), "MM-DD-YY") & "-01"
    Else[green]
        'We have a/some record(s) for today
        'Third element is the last piece of Project Number, 0 based[/green]
        intLastVal = Val(Split(!ProjectNumber.Value, "-")(3))
        intLastVal = intLastVal + 1
        [blue]NewProjNo[/blue] = Format(Date(), "MM-DD-YY") & "-" & Format(intLastVal, "00")
    End If
    .Close
End With

End Function

It is kind of 'pseudo code', but I hope you can see my approach to your problem here...


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

Actually, this above logic could be a lot shorter:

With rst
    .Open strSQL, SomeConnectionHere
    If .RecordCount > 0 Then
        intLastVal = Val(Split(!ProjectNumber.Value, "-")(3))
    End If
    .Close
End With

intLastVal = intLastVal + 1
NewProjNo = Format(Date(), "MM-DD-YY") & "-" & Format(intLastVal, "00")

---- Andy

There is a great need for a sarcasm font.

 

[qqp]

Thanks Andy! I'm working on it! Fingers crossed!

 

[Andrzejek]

Any progress? Do you have it resolved? If so, how did you do it?


---- Andy

There is a great need for a sarcasm font.

 

[qqp]

Yes I did! Thank you so much. I used a modified version of what you gave me (the shorter version), and WALA! You're a rockstar!

Peace.