asked on

Need more help autopopluating a number field

Last week an expert helped me with a number field auto-population topic. But the rule have changed. For reference here was the original post:

"I have a form with a 5-digit number field that I want to auto-populate with data when a new record is entered. The first three digits are to be sequential starting with 300. The second 2 digits are to be filled with last 2 digits of the year. So if the new record was created today the auto-populated field would be 30016.

But here's the catch. The year is a fiscal year starting March 1 and ends the last day of February. So the year isn't just a calendar year, it is a fiscal year."

I ended up last week awarding to: https://www.experts-exchange.com/questions/28992163/How-autopopulate-number-field-with-2-different-criteria.html?anchor=a41942308¬ificationFollowed=181612782&anchorAnswerId=41941906#a41941906

However, the rules have changed. The fiscal year rule still exists, no changes. The last two digits of the auto-populated 5 digit number stay the same... To be the last two digits of the fiscal year. BUT, each year, starting with March 1, the first 3 digits start over starting with 001, then 002, then 003, etc. (I do need the zeroes in front of the other numbers).

How can this now be accomplished?

Rey Obrero (Capricorn1)

use this function

Function GetNextSequence()
Dim newVal As String, curVal As String, seq As String
Dim curYear As Integer, dDate As Date
  If Month(dDate) > 2 Then curYear = Year(dDate) Else curYear = Year(dDate) - 1

 If DMax("yourIDField", "YourtableA") > 0 Then
     curVal = DMax("yourIDField", "YourtableA")
  'test if the year is the same
    If Right(curVal, 2) = Right(curYear, 2) Then
    seq = Val(Left(curVal, 3)) + 1
    newVal = seq & Right(curYear, 2)
    Else
        newVal = 300 & Right(curYear, 2)
    End If
    Else
        newVal = 300 & Right(curYear, 2)
End If
GetNextSequence = newVal
 CurrentDb.Execute _
 "insert into YourtableA (yourIDField) Values(" & newVal & ")"

End Function

Open in new window

PatHartman

Using a single column and having the most significant value be in the low-order digits makes this so much more complicated than it needs to be. I warned you in your previous post about the issues. At least you are now resetting the count each year. That gives you 999 per year which is still a very small number.

I don't believe that the posted solution actually works. You need to find the year part of the ID in the DMAX() function and find the maximum value for a year, not just the maximum value because 56715 is greater than 00416 but the next number you want is 00516, NOT 30016.

I suggest that you actually pay someone (not me) to write this function for you and storing the two pieces separately will be easier to manage in the long run. You can concatenate them for display and finally, do not under ANY conditions use this field as your PK.

Rey Obrero (Capricorn1)

<I don't believe that the posted solution actually works.> did you test it?

PatHartman

Yes. Did you? Also, the OP is no longer starting at 300. He is starting at 001

Rey Obrero (Capricorn1)

oh yeah, you are right. I didn't noticed that last part :-(

Rey Obrero (Capricorn1)

ok trry this

Function GetNextSequence()
Dim newVal As String, curVal As String, seq As String
Dim curYear As Integer, dDate As Date
  If Month(dDate) > 2 Then curYear = Year(dDate) Else curYear = Year(dDate) - 1

 If DMax("yourIDField", "YourtableA") > 0 Then
     curVal = DMax("yourIDField", "YourtableA")
  'test if the year is the same
    If Right(curVal, 2) = Right(curYear, 2) Then
    seq = Format(Val(Left(curVal, 3)) + 1, "000")
    newVal = seq & Right(curYear, 2)
    Else
        newVal = "001" & Right(curYear, 2)
    End If
    Else
        newVal = "001" & Right(curYear, 2)
End If
GetNextSequence = newVal
 CurrentDb.Execute _
 "insert into YourtableA (yourIDField) Values(" & newVal & ")"

End Function

Open in new window

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

You don't need all that. One line will still do, just adjust for the month offset and the start value and apply Format as you now need leading zeroes for the small IDs:

=Format(Val(Nz(DMax("Left([ID],3)", "YourTable", "Right([ID], 2) = " & Format(DateAdd("m", -2, Date()), "yy") & "")), 0) + 1) & Format(DateAdd("m", -2, Date()), "yy"), "00000")

Open in new window

/gustav

SteveL13

ASKER

Gustav, I'm trying your code but getting an error.

Expected end of statement. SO I removed the comma after "yy") which it seemed to be complaining about but it still isn't right.

SteveL13

ASKER

Here is what I have now but can't compile.

= Format(Val(Nz(DMax("Left([ID],3)", "mytable", "Right([ID], 2) = " & Format(DateAdd("m", -2, Date), "yy") & "")), 0) + 1) & Format(DateAdd("m", -2, Date), "yy", "00000")

Getting "wrong number of arguments or invalid property assignment"

Rey Obrero (Capricorn1)

@steve
did you try my last post?

SteveL13

ASKER

Rey, I am right now. Back to you in a minute.

Rey Obrero (Capricorn1)

add this to the declaration

Dim maxYear as integer

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.