Custom Counter

[Marcelino Benitez via AccessMonster.com]

Good morning guys. This is my issue. I have a custom counter that be have
this setting
A6001-01 means "Company letter" "year" "Julian Date"- "counter #"
Ok my problem is if I start a day with company letter D6001-01 is good. Ok,
If the next one is company letter "A" is suposse to be A6001-01 because is
the first work order number for this date on this company but the problem is
display A6001-02. This one is my routine please let me know what I'm doing
wrong.

Dim count As Variant
Dim compdate As Variant
Dim JDate As Integer
Dim Ydate As Integer '
Dim t As Variant 'Counter variable

Me.Undo
Form.Requery
DoCmd.GoToRecord , , acLast 'Send the page to the last record
compdate = Text31
'MsgBox Text31

DoCmd.GoToRecord , , acNewRec


JDate = Format(Date, "y")
Ydate = Format(Date, "yy")

If JDate < 10 Then
tdate = Ydate & "0" & "0" & JDate & "-" & "01"
End If

If JDate >= 10 Then
tdate = Ydate & "0" & "0" & JDate & "-" & "01"
End If

If JDate > 99 Then
tdate = Ydate & JDate & "-" & "01"
End If

'MsgBox [tdate]

If compdate > tdate Or compdate = tdate Then
t = Format(compdate, "!&&")
If t < 99 Then
t = t + 1
t = Format(t, "00")
End If

If JDate < 10 Then
WONum = Ydate & "0" & "0" & JDate & "-" & t
End If

If JDate >= 10 Then
WONum = Ydate & "0" & JDate & "-" & t
End If

If JDate > 99 Then
WONum = Ydate & JDate & "-" & t
End If
Else
WONum = tdate

End If

Thanks in advance.

Marcelino

 

[Jeff Boyce]

Marcelino

It seems like your code is testing for a date change, but I didn't notice
any testing for the initial letter.

By the way, it isn't a good idea to "stuff" all those different meanings
into a single field, nor is it necessary. If you have a company field, you
can obtain the left-most letter using Left() in a query. Similarly, if you
have a date field, you can obtain the last digit of the year in a query.
And a Julian date. And a sequence number. It is in your query that you
assemble (concatenate) these pieces together into a "smart key" (note:
"smart keys" are not very smart!).

You might also find it easier to come up with the function to check initial
letter, year, Julian date and sequence number and derive the next sequence
number.

By the way, 2... if you are using the first letter of a company name, how
do you distinguish between two companies that start with the same letter?
What happens when you have more than 26 companies?

 

[BruceM]

I have done some work with concatenated fields. In addition to what Jeff
has said, I will say that you will probably need to bring the DMax function
into this at some point. One thing I'm having difficulty understanding is
how your first digit can be a 6 (rather than have it starting with 06),
since I can't identify an instance of the number starting with JDate. It
would help if you identified various scenarios and the expected result. I
can't quite figure out from your code what all of the various If statements
would produce. Going to the last record to pull a value from a text box
(containing what exactly, by the way?) is almost certainly not the way to
begin.
Where are you running this code? What are you undoing at the very beginning
of the code?