I have the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.[FYStartDate] >= #6/1/2009# Then
Me.txtJobNum = Nz(DMax("JobNum", "tblDetail", "[FYStartDate]>=#6/1/2009#"), "0000") + 1

End If
End Sub

My question is that when I started new records on 6/1/09, the control started to count up the way it should. When it got to 10 and I started a new record the number jumped to 2989 instead of the next due number 11. Any thoughts on why???

You're telling the Nz function to return a string if there's no value: you should be returning a number.



If you're trying to ensure a 4 character field, format the value after the addition, not before:

I applied your suggestion and it still returns the same number, 2989.

Okay, let's see what's happening. Try this:

The message said "Max JobNum is 2988."

Question: This control can't have duplicating numbers, can it?

Hold on. I wasn't really understanding your situation before.

Controls simply display data: there aren't really rules about what they can and can't be. The txtJobNum control is presumably bound to the JobNum field in table tblDetail. Somehow data got into that table with a value of 2988 for JobNum. Are there any other ways that data could have got into that table other than through your form?

Sorry for the confusion (as the title says). I have a field in a table that counts up "JobNum". On 6/1 of whatever year it starts counting from 1 to whatever for 1 year and then starts over at 1 and does it again the following 6/1. 6/1 to 5/31 is my fiscal year. To determine what fiscal year I am in I have another field, "FYJobNum". 6/1/09 looks like this: P10-0001, P10-0002, and so on. P10 being my fiscal year designation.

So my question from before, "I can't have duplicate numbers in this field, right???" Because the code is always looking for the highest number. Is there a way to write code to have it start over at 1 and only look at the current fiscal year number??? Or is that too complicated.

Sorry, but you're still not giving enough information. If JobNum has been designated as the only field in a unique index (which doesn't have to be a primary key), then no, you can't have duplicate numbers in it. However, I don't see a need for both JobNum and FYJobNum. Why not just worry about FYJobNum?

I was told in the past that I had to make 2 fields...1 for the "P" number and 1 for the other. Now in talking with you I find that is not the case, correct??? If I go with 1 field (FYJobNum), I can change the "P" number year after year and have the four digit number go back to "0000" at the beginning of the fiscal year???

Are the two fields about which you're talking JobNum and FYJobNum? Which one is your Primary Key?

My recommendation would be to have two fields, but not those two fields. Instead, the P number should be stored by itself in one field, while the part after the hyphen should be stored in the other field. The combination of the two fields should be the primary key. That will let you restart at 1 each time the Fiscal Year indicator changes. If we assume that the fields are named FYPrefix and FYJobNum, your code would be something like:



You'd then create a query with a computed field that concatenates the two fields for display purposes.

So you don't think a single field is a good idea??

As in concatenating two pieces of information into a single field? No, it's a bad idea: it violates database normalization principles, and makes queries more difficult than they need be.

OK...thanks for the advice Doug...it was very helpful. Have a good day!!