Formula ?

I have the following formula which is not
working and I need to know what to change.
I did not create this, as I am new. Got it
yesterday from New User Site.

What I have now:
PART I
1. when a new record is created, a field
displays the date in yr/mm/day format,
with an additional 3 numbers
i.e. 051118001
2. when the day changes at midnight,
the number will reflect that change,
i.e. 051119001
Always starting at ....001 (not ever 000)

That part works fine. When midnight rolls around,
the number is changing and starting at ...001.

PART II
When a button is pushed on the form,
it records the data and brings up the
next blank record for input. I have that part.

The Problem:
What I need to have happen with the number:
When the blank record comes up, the
number needs to change to .....002
and so on .....003, .....004, etc.

It just keeps showing as 051118001

Here is the Formula:

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = 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(Left(varHighValue, 6)) + 1, "000000000")
End If
End Function

Any help would be greatly appreciated
Tom

Tom,

It looks to me that this line in the code is not correct...
GetNextNumber = Format(CLng(Left(varHighValue, 6)) + 1, "000000000")
Try changing it to...
GetNextNumber = Format(CLng(varHighValue) + 1, "000000000")

--
Steve Schapel, Microsoft Access MVP


Tom wrote:
I have the following formula which is not
working and I need to know what to change.
I did not create this, as I am new. Got it
yesterday from New User Site.

What I have now:
PART I
1. when a new record is created, a field
displays the date in yr/mm/day format,
with an additional 3 numbers
i.e. 051118001
2. when the day changes at midnight,
the number will reflect that change,
i.e. 051119001
Always starting at ....001 (not ever 000)

That part works fine. When midnight rolls around,
the number is changing and starting at ...001.

PART II
When a button is pushed on the form,
it records the data and brings up the
next blank record for input. I have that part.

The Problem:
What I need to have happen with the number:
When the blank record comes up, the
number needs to change to .....002
and so on .....003, .....004, etc.

It just keeps showing as 051118001

Here is the Formula:

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = 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(Left(varHighValue, 6)) + 1, "000000000")
End If
End Function

Any help would be greatly appreciated
Tom

That Does Not Work

Tom

"Steve Schapel" wrote:

Tom,

It looks to me that this line in the code is not correct...
GetNextNumber = Format(CLng(Left(varHighValue, 6)) + 1, "000000000")
Try changing it to...
GetNextNumber = Format(CLng(varHighValue) + 1, "000000000")

--
Steve Schapel, Microsoft Access MVP


Tom wrote:
I have the following formula which is not
working and I need to know what to change.
I did not create this, as I am new. Got it
yesterday from New User Site.

What I have now:
PART I
1. when a new record is created, a field
displays the date in yr/mm/day format,
with an additional 3 numbers
i.e. 051118001
2. when the day changes at midnight,
the number will reflect that change,
i.e. 051119001
Always starting at ....001 (not ever 000)

That part works fine. When midnight rolls around,
the number is changing and starting at ...001.

PART II
When a button is pushed on the form,
it records the data and brings up the
next blank record for input. I have that part.

The Problem:
What I need to have happen with the number:
When the blank record comes up, the
number needs to change to .....002
and so on .....003, .....004, etc.

It just keeps showing as 051118001

Here is the Formula:

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = 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(Left(varHighValue, 6)) + 1, "000000000")
End If
End Function

Any help would be greatly appreciated
Tom

Fair enough, Tom.

--
Steve Schapel, Microsoft Access MVP

Tom wrote:
That Does Not Work

Tom

Tom,
Your answer of "That Does Not Work" suggests that you don't want any
additional assistance.

--
Duane Hookom
MS Access MVP


"Steve Schapel" wrote in message
...
Fair enough, Tom.

--
Steve Schapel, Microsoft Access MVP

Tom wrote:
That Does Not Work

Tom

Well, thanks for the translation service, Duane :-)

--
Steve Schapel, Microsoft Access MVP

Duane Hookom wrote:
Tom,
Your answer of "That Does Not Work" suggests that you don't want any
additional assistance.

Quite the contrary.
When I click on "new record" or
use the button I created to do the same,
the same number appears, ....001.

It needs to be ....002. When I enter
data into that record and click on
new record, the number that needs
to be there is ....003.

The first part of the number is working
fine. 051122xxx, the date part.
It's the last part that is not incrementally
changing as a new record is created.

Tom



"Steve Schapel" wrote:

Well, thanks for the translation service, Duane :-)

--
Steve Schapel, Microsoft Access MVP

Duane Hookom wrote:
Tom,
Your answer of "That Does Not Work" suggests that you don't want any
additional assistance.

It look as if there are two potential failure points.

The DMax is returning a null value every time.
The assignment statement is failing. -- I think this is probably the
culprit.

Format(CLng(Left(varHighValue, 6)) + 1, "000000000") this takes the
current date value
"051122" and adds 1 to it to get 51123 and then formats that.

I've rewritten the function to try to make it a bit more efficient and
hopefully return the value you want.

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = Format(Date, "yymmdd")

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like = '" & strCurrDate & "*'")
'Check the value returned - remove this when this is working
Msgbox "varHighValue is " & varHighValue

If IsNull(varHighValue) Then
GetNextNumber = strCurrDate & "001"
Else
GetNextNumber = StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000")
'Check the value returned - remove this when this is working
Msgbox "Returned Value is " & StrCurrDate &
Format(Val(Right(varHighValue,3))+1,"000")
End If

End Function

"Tom" wrote in message
...
I have the following formula which is not
working and I need to know what to change.
I did not create this, as I am new. Got it
yesterday from New User Site.

What I have now:
PART I
1. when a new record is created, a field
displays the date in yr/mm/day format,
with an additional 3 numbers
i.e. 051118001
2. when the day changes at midnight,
the number will reflect that change,
i.e. 051119001
Always starting at ....001 (not ever 000)

That part works fine. When midnight rolls around,
the number is changing and starting at ...001.

PART II
When a button is pushed on the form,
it records the data and brings up the
next blank record for input. I have that part.

The Problem:
What I need to have happen with the number:
When the blank record comes up, the
number needs to change to .....002
and so on .....003, .....004, etc.

It just keeps showing as 051118001

Here is the Formula:

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = 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(Left(varHighValue, 6)) + 1, "000000000")
End If
End Function

Any help would be greatly appreciated
Tom

Syntax Error:

Msgbox "Returned Value is " & StrCurrDate &
Format(Val(Right(varHighValue,3))+1,"000")

remember, the date is working fine 051122
it is the last three numbers after the date
that are not working. 051122001, next record 002,
next record 003, etc
Now what?
tom

"John Spencer" wrote:

It look as if there are two potential failure points.

The DMax is returning a null value every time.
The assignment statement is failing. -- I think this is probably the
culprit.

Format(CLng(Left(varHighValue, 6)) + 1, "000000000") this takes the
current date value
"051122" and adds 1 to it to get 51123 and then formats that.

I've rewritten the function to try to make it a bit more efficient and
hopefully return the value you want.

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = Format(Date, "yymmdd")

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like = '" & strCurrDate & "*'")
'Check the value returned - remove this when this is working
Msgbox "varHighValue is " & varHighValue

If IsNull(varHighValue) Then
GetNextNumber = strCurrDate & "001"
Else
GetNextNumber = StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000")
'Check the value returned - remove this when this is working
Msgbox "Returned Value is " & StrCurrDate &
Format(Val(Right(varHighValue,3))+1,"000")
End If

End Function

"Tom" wrote in message
...
I have the following formula which is not
working and I need to know what to change.
I did not create this, as I am new. Got it
yesterday from New User Site.

What I have now:
PART I
1. when a new record is created, a field
displays the date in yr/mm/day format,
with an additional 3 numbers
i.e. 051118001
2. when the day changes at midnight,
the number will reflect that change,
i.e. 051119001
Always starting at ....001 (not ever 000)

That part works fine. When midnight rolls around,
the number is changing and starting at ...001.

PART II
When a button is pushed on the form,
it records the data and brings up the
next blank record for input. I have that part.

The Problem:
What I need to have happen with the number:
When the blank record comes up, the
number needs to change to .....002
and so on .....003, .....004, etc.

It just keeps showing as 051118001

Here is the Formula:

Function GetNextNumber() As String
Dim strCurrDate As String
Dim varHighValue As Variant
strCurrDate = 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(Left(varHighValue, 6)) + 1, "000000000")
End If
End Function

Any help would be greatly appreciated
Tom

Try trouble shooting? The lines you posted should all be on ONE line in the
code - it appears that the newsreader may have wrapped them into two lines.
If it is wrapping, then fix it to all be on one line or try the alternative
formatting below

Msgbox "Returned Value is " & _
StrCurrDate & _
Format(Val(Right(varHighValue,3))+1,"000")

The idea behind the message box is to see what values are actually being
returned at various stages?

The other method of trouble shooting this code would be to step through the
code a line at a time.

IF the first MsgBox call returns "VarHighValue is " with no value, then you
know that the DMAX is not returning anything and the conditional code is
executing the True value of the test.

If the second MsgBox call gets run, then you will know hopefully which value
it is returning. If it doesn't get called then you know that VarHighValue
is null and this bit of the code never executes.

"Tom" wrote in message
...
Syntax Error:

Msgbox "Returned Value is " & StrCurrDate &
Format(Val(Right(varHighValue,3))+1,"000")

remember, the date is working fine 051122
it is the last three numbers after the date
that are not working. 051122001, next record 002,
next record 003, etc
Now what?
tom
SNIP