If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Formula ?
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 |
#3
|
|||
|
|||
Formula ?
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 |
#4
|
|||
|
|||
Formula ?
Fair enough, Tom.
-- Steve Schapel, Microsoft Access MVP Tom wrote: That Does Not Work Tom |
#5
|
|||
|
|||
Formula ?
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 |
#6
|
|||
|
|||
Formula ?
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. |
#7
|
|||
|
|||
Formula ?
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. |
#8
|
|||
|
|||
Formula ?
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 |
#9
|
|||
|
|||
Formula ?
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 |
#10
|
|||
|
|||
Formula ?
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IF formula? | meris | Worksheet Functions | 1 | September 6th, 2005 07:14 AM |
adding row to forumla | carrera | General Discussion | 9 | August 23rd, 2005 10:24 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
generating formula with a formula | Lisa Linard | Worksheet Functions | 2 | November 27th, 2003 12:03 PM |