Noob using Dmax

[sonny123]

I have a simple table & form
the table has a created date field (value is set by default when the record created)
and Openeddate field (value is inputted as datetime)

I have a Counter Field which I want to set as 3 digits
so that the first record inputed which has a openeddate of say 01/01/2011
holds the value 001. (the leading zeros are important to me)
the next record inputted with the same opendate is 002

And the next record inputted for an opendate that does not exist in the table is 001

I have been playing with Dmax

Private Sub Ctl3DigitCode_BeforeUpdate(Cancel As Integer)
Me![3DigitCode] = Nz(DMax("[3DigitCode]", "[Main]", "[DateOpened]"), 0) + 1
End Sub

But I cant get this to work.

 

[pbaldy]

Your criteria isn't right:

http://www.mvps.org/access/general/gen0018.htm

I would store a simple number and format it with the zeros for the user:

Format(FieldName, "000")

 

[sonny123]

Thanks paul,
if I can get the counting bit right, then ill worry about the formatting,
had a brief look at your link and will explore that content further after the weekend
just a quick response
when you say the criteria isn't right
do you mean this bit

Private Sub Ctl3DigitCode_BeforeUpdate(Cancel As Integer)
Me![3DigitCode] = Nz(DMax("[3DigitCode]", "[Main]", [COLOR="Red"]"[DateOpened]"[/COLOR]), 0) + 1
End Sub

Do I need to reference the dateopened that has been inputed as a value on the Current record that is being inputted rather than making a reference to the main table
I'm guesssing that the "Refering to a form control" bit of your link is what i should be looking at.

And I'm still guessing that Dmax Is what I should be using (not Dlookup)

Or am I really confused, forgive my ignorance & thanks in advance for your patience, I know a little Java and a little SQL but i'm a virgin when it comes to access.
But Keen to learn!

 

[pbaldy]

I think you're on the right track. Yes, the highlighted bit is the part you have wrong, and you would use DMax; the link uses DLookup but the syntax is the same for all of them. You would want the form version, and from the look of it you would compare to the date value entered on the form.

 

[sonny123]

Okay after a bit ( a lot actually) of trail and error I got IT

Private Sub InputBY_Exit(Cancel As Integer)
Me![3DigitCode] = Nz(DMax("[3DigitCode]", "[Main]", "[DateOpened] = #" & Forms!Main!DateOpened & "#"), 0) + 1
End Sub

It took me a while to realise there was something not quite right when using

Private Sub Ctl3DigitCode_[I][B]BeforeUpdate[/B][/I](Cancel As Integer)

Im not sure why

but anyway, when i did it as on exit from the previous field it worked fine

 

[pbaldy]

Glad you got it sorted out. I should have noticed the event. I only use the before update event for validation.