Re: Formula ?

Steve,
Thank you! I'm learning, which I love to do.
Now for tweaking.
After entering several records, closing the program, and then reopening
the program, I notice that it will always start at 000. When I compare the
"RunNumber" with the actual count on the Record Navigation bar, it is way off.
When I look at the table, there are several duplicate RunNumbers with "0".

Several Questions:
1. Do I set the Indexed to "no duplicates" on the table?
2. How do I set the starting number to 001 and not 000
3. How do I set the RunNumber to always reset at midnight to 001
4. If I close the program, then reopen, is there a way to have it
always open with a new record for data entry?

Again, Thanks!
Tom


"Steve Schapel" wrote:

> Tom,
>
> Regarding "unless we can make the yymmdd001 change with each new
> record", it is actually very easy to do this, and I can show you how if
> you're still interested. But now we have started down another track,
> which is probably a better one anyway.
>
> My understanding is that the new records in the ABLE_Table1 table are
> entered via a form. Is this correct?
>
> There is also apparently an assumption that there will never be more
> than 999 new records in any given day. Is this correct?
>
> If it was mine, I would never have had a function like your
> GetNextNumber() function in the first place. I recommend you should
> delete it completely.
>
> Now that you have decided to make two separate fields, one should be a
> Date/Time data type and the other a Number data type. Let's say these
> fields are called RunDate and RunNumber.
>
> Ok, here's how I would do it...
>
> 1. Set the Default Value property of the RunDate control on the form to...
> Date()
>
> 2. Set the Format property of the RunDate control on the form to...
> yymmdd
>
> 3. Set the Format property of the RunNumber control on the form to...
> 000
>
> 4. Alter the code on your Command85 button, like this...
> Private Sub Command85_Click()
> DoCmd.GoToRecord , , acNewRec
> Me.RunNumber =
> Nz(DMax("[RunNumber]","ABLE_Table1","[RunDate]=Date()"),0)+1
> Me.Dirty = False
> End Sub
>
> By way of explanation, the actual data stored in the RunDate and
> RunNumber fields in the table will be a date and a number, which will
> look like a date and a number if for some obscure reason you were poking
> around in the table. In my opinion, this is as it should be. For your
> purposes on form and report, you want the date to be shown without /s
> and so forth, and you want the number to be shown as 3 digits with
> leading zeros. This does not mean that the date and the number should
> be stored in the table as text. They should be stored as a date and a
> number, and then getting them to appear the way you want is Formatting.
> Formatting affects the appearance of the data, but does not affect the
> value of the data.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Tom wrote:
> > Steve,
> > Here is the code in the module.
> >
> > Function GetNextNumber() As String
> > Dim strCurrDate As String
> > Dim varHighValue As Variant
> > strCurrDate = Format(Val(Format(Date, "yymmdd")))
> > varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", "Left([RUN NUMBER], 6)
> > ='" & strCurrDate & "'")
> > If IsNull(varHighValue) Then
> > GetNextNumber = strCurrDate & "001"
> > Else
> > GetNextNumber = Format(CLng(varHighValue) + 1, "000000000")
> > End If
> > End Function
> >
> > I've decided to make this easy, and it will problably be more
> > benificial when it comes time for relationships, and create a
> > field next to the date, that will be just for the last 3 digits.
> > The finished product should be in the format:
> > yymmdd 001
> > So if you could tell me what part of the code to keep for just
> > the date automatically changing at midnight and in the format
> > yymmdd. (no slashes "/" ) Right now it is giving me yymmdd001
> > just need the yymmdd.
> >
> > And could you tell me a counting code that will start at 001 at
> > midnight and increase by 1 for every record recorded, that
> > will make this alot easier. It should revert to 001 at midnight
> > when the date changes.
> >
> > Unless we can make the yymmdd001 change with each new record,
> > that might be an easier way to go.
> >
> > You've been a big help so far, thanks for your patience.
> > Tom
>
.

Loading