Automatic number with date format

[khabib28]

I have code to make autonumber with date format....

Call koneksi()
        cmd = New OleDbCommand("select * from penjualan order by no_faktur desc", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If Not dr.HasRows Then
            penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF" + "0001"
        Else
            penfaktur.Text = Val(Microsoft.VisualBasic.Mid(dr.Item("no_faktur").ToString, 11, 4)) + 1
            If Len(penfaktur.Text) = 1 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF000" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 2 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF00" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 3 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF0" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 4 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF" & penfaktur.Text & ""
            End If
        End If

I want every date change the number back to "0001" ..
help me..

[.paul.]

It's not clear what you're asking for help with...

[jdc2000]

I think what the OP is wanting is something like this:

01072017NF0001
01072017NF0002
...
01072017NF0099
01072017NF0100
02072017NF0001   <--- Change back to NF + 0001 here, since the date changed
02072017NF0002
03072017NF0001   <--- Change back to NF + 0001 here, since the date changed
...

[.paul.]

Assuming that what jdc2000 said is correct, it's just a case of checking the date in the textbox, before calculating the NF number...

[khabib28]

Right ..how to apply with code..

[khabib28]

yes.. and then how to apply to code..

[jdc2000]

You need to create a variable to store the last value of penfaktur.Text that was used, then check the Left most 8 characters against the new value of penfaktur.Text and change the NF suffix to 0001 if it is different from the saved value.

[khabib28]

You need to create a variable to store the last value of penfaktur.Text that was used, then check the Left most 8 characters against the new value of penfaktur.Text and change the NF suffix to 0001 if it is different from the saved value.

can you write the code..
i really need this code

[digitalShaman]

your code from post#1 has an issue: the query orders by no_faktur desc which is "ddMMyyyy" so it orders Jan. 1st 2017 ("01012017") before Feb. 2nd 2016 ("02022016").
so the number you generate is Kind of random anyhow.

an easy fix to achive what you are after could be:
cmd = New OleDbCommand("select * from penjualan WHERE no_faktur LIKE Format(Now, "ddMMyyyy%") order by no_faktur desc", conn)
so that the query pulls only the existing records of today to use that to determine the next number. you Need to Change the red section according to the DBS you use.

[jdc2000]

You could try something like the following:

Call koneksi()
        cmd = New OleDbCommand("select * from penjualan order by no_faktur desc", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If Not dr.HasRows Then
            strDate = Format(Now, "ddMMyyyy")
            penfaktur.Text = "" + strDate + "NF" + "0001"
        Else
            penfaktur.Text = Val(Microsoft.VisualBasic.Mid(dr.Item("no_faktur").ToString, 11, 4)) + 1
            strNewDate = Format(Now, "ddMMyyyy")
            If strNewDate <> strDate Then
                penfaktur.Text = "0001"
                strDate = strNewDate
            End If
            If Len(penfaktur.Text) = 1 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF000" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 2 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF00" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 3 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF0" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 4 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF" & penfaktur.Text & ""
            End If
        End If

[khabib28]

You could try something like the following:

Call koneksi()
        cmd = New OleDbCommand("select * from penjualan order by no_faktur desc", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If Not dr.HasRows Then
            strDate = Format(Now, "ddMMyyyy")
            penfaktur.Text = "" + strDate + "NF" + "0001"
        Else
            penfaktur.Text = Val(Microsoft.VisualBasic.Mid(dr.Item("no_faktur").ToString, 11, 4)) + 1
            strNewDate = Format(Now, "ddMMyyyy")
            If strNewDate <> strDate Then
                penfaktur.Text = "0001"
                strDate = strNewDate
            End If
            If Len(penfaktur.Text) = 1 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF000" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 2 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF00" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 3 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF0" & penfaktur.Text & ""
            ElseIf Len(penfaktur.Text) = 4 Then
                penfaktur.Text = "" + Format(Now, "ddMMyyyy") + "NF" & penfaktur.Text & ""
            End If
        End If

take no effect ?? what wrong??

[jdc2000]

Did you try putting in any break points to check the values you are getting for the dates and the strDate and strNewDate variables?

[khabib28]

Did you try putting in any break points to check the values you are getting for the dates and the strDate and strNewDate variables?

no.....

[jdc2000]

Try that, then post a list of the results you are getting. Without knowing what your actual data is we cannot say what the issue might be.

[khabib28]

Just put this code after Else statement

            If Microsoft.VisualBasic.Mid(dr.GetString(0), 3, 8) <> tgl Then
                penfaktur.Text = "PL" + tgl + "0001"
            Else
                penfaktur.Text = Val(Microsoft.VisualBasic.Mid(dr.Item("no_faktur").ToString, 11, 4)) + 1
            End If

---- " Knowledge has no value , unless you use and share it " ----
#Share your Knowledge
#thanks