More help needed w/ numbering and resetting (1 Viewer)

[azgambit]

Hi folks, I posted help in this area previously and still can't get things to work. I did change my approach somewhat, but aarrrggg!!!

Here's what I am trying to do:

Create a field the creates sequential numbers starting with 1 and increasing by 1 for each new record. Then, when the year changes, say from 2002 to 2003, the field re-sets the number to 1.

I conducted several searches through the forums and wasn't able to find any posts that covered this, of course, this doesn't mean it isn't there, but...

I'm pretty sure I can't do this with an AutoNumbering field since this is designed to be a unique numbering field and this particular field will not be unique. So, in order to create the type of field I am looking for, what type of field do I create? What is the code? and where in the properties would I place the code?

Thanks for any help!!!

 

[David R]

Search the archives for: dmax primary

The function you want is Dmax, but you'll have to take some extra steps to ensure you don't get duplicate values. The posts that come up in that search should help you get started.

Post back if you have further problems.

 

[azgambit]

Got part of it

OK, I got the DMax function to work, well sort of. Unless I manually enter the number 1 in the first record, it continually return with the number 0.

Is there anyway to automatically designate record #1 as number 1?

Also, any ideas on how to have the number reset to 1 when the year changes. The_Doc_Man suggested this string, but I can't get it to work:

1 + DCount("[fldname]","tblname","[fldyear]=& Year(Date()) )

 

[David R]

What's not working about it?

You left out a quote in how you posted it, not sure if that's how you have it in your code:

1 + DCount("[fldname]","tblname","[fldyear] = " & Year(Date()) )

Assuming your [fldyear] is numeric, of course...

Though if you're having a problem with it, perhaps you should post back to the thread Doc_Man and you were already in. Starting a new thread leads to duplicated effort, since I'm not sure what he's already advised you on...

Ahh found it. I would disagree with Doc_Man here and use DMax instead of DCount.

 

[azgambit]

Makin sure I got you right

I copied the expression string exactly as it was posted and, yes, it took me a while to figure out I was missing a quatation mark. Although, I placed it at the end between the last two paranthesis (maybe that's why it didn't work <grin>)

Anyway, are you siggesting to use the same string only replace DCount with DMax?

 

[David R]

Correct

The reason I recommend Dmax vs. DCount is that DMax will look up the highest example thus far in your database. For instance, if the latest record entered was 504, it will enter 505 on the next one. DCount on the other hand counts the existing records, just as you'd expect. If you've entered 504 records but deleted 23, then DCount will give you an erroneous (and possibly duplicate) result of 482.