Formula ?

[Tom]

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

 

[Steve Schapel]

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")

 

[Tom]

That Does Not Work

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

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

 

[Steve Schapel]

Fair enough, Tom.

 

[Duane Hookom]

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

 

[Steve Schapel]

Well, thanks for the translation service, Duane

 

[Tom]

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

 

[John Spencer]

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]

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

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

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

 

[John Spencer]

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.

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

 

[Steve Schapel]

Tom,

The code we have been discussing is a function that returns a certain
value. I believe the modification I suggested before willl be necessary
to get it to return the value you want. But here's the main point...
when is this value being applied? So far we have seen the function that
calculates the value, but we have not seen the procedure for how this is
supposed to be inserted into the relevant field in a new record. Can
you please let us know how this works? You mentioned a command
button... what is the code on the Click event of that button? Thanks.

 

[Tom]

I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075'

Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE=
'051122*".

I have no idea what I'm doing, I'm plugging stuff in
following along. I do not know any formulas, calculations
or anything else for that matter. Somewhat familiar
with excel and those formulas, not this however.

Tom

 

[John Spencer]

Don't shoot. Please!!

My fault.

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like '" & strCurrDate & "*'")

I should not have left in the "=". My apologies for not double-checking the code.

I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075'

Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE=
'051122*".

I have no idea what I'm doing, I'm plugging stuff in
following along. I do not know any formulas, calculations
or anything else for that matter. Somewhat familiar
with excel and those formulas, not this however.

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.


SNIP

 

[Tom]

Alrighty.
No I get the message:

varHighValue is
with an "OK" button

Now what do I do?
Tom

Don't shoot. Please!!

My fault.

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like '" & strCurrDate & "*'")

I should not have left in the "=". My apologies for not double-checking the code.

I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075'

Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE=
'051122*".

I have no idea what I'm doing, I'm plugging stuff in
following along. I do not know any formulas, calculations
or anything else for that matter. Somewhat familiar
with excel and those formulas, not this however.

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.

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

 

[John Spencer]

So, now you know that DMax function is not finding any match in ABLE_Table1.

Open that table. Is there a value in the table that should match? If so,
what is it? If not, then I suspect that you aren't storing the newly
created value in this table.

Are you assigning the new value to a control on a form? Is that control's
source set to the Run Number field in Able_Table1?

Alrighty.
No I get the message:

varHighValue is
with an "OK" button

Now what do I do?
Tom

Don't shoot. Please!!

My fault.

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like '" & strCurrDate & "*'")

I should not have left in the "=". My apologies for not double-checking
the code.

I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075'

Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE=
'051122*".

I have no idea what I'm doing, I'm plugging stuff in
following along. I do not know any formulas, calculations
or anything else for that matter. Somewhat familiar
with excel and those formulas, not this however.

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.

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

 

[Steve Schapel]

Open that table. Is there a value in the table that should match? If so,
what is it? If not, then I suspect that you aren't storing the newly
created value in this table.

... or that the function that calculates the value is being applied
before the record is saved, so even if there is a record in the table,
it could have been saved there after the processing of GetNextNumber().
This is the reason, Dev, why I suggested for you to post the code
(which I can only asssume is on the Click event of your command button)
that applies the new number. All we have seen is the function that
calculates a value, but when and how that value is supposed to be
applied to the database is the important aspect here.

 

[Steve Schapel]

Sorry, Tom, called you the wrong name... got mixed up with someone in
another thread!

 

[Tom]

when i enter the data into the record on the FORM,
it gives the number 51123001. I record it. Then I check the TABLE,
and the number has been recorded as 51123001.
When I go back the FORM, and right click on the control, then
properties, the control source lists it as RUN NUMBER,
not [ABLE_Table1]![RUN NUMBER].
So I tried changing it to that, and it gives me the same msg
varHighValue is
when I go to "next record" the number should not be 51123001
but 51123002. It is still 51123001.
I've got no hair left.
Tom

So, now you know that DMax function is not finding any match in ABLE_Table1.
Open that table. Is there a value in the table that should match?
If so, what is it?

If not, then I suspect that you aren't storing the newly

created value in this table.

Are you assigning the new value to a control on a form? Is that control's
source set to the Run Number field in Able_Table1?

Alrighty.
No I get the message:

varHighValue is
with an "OK" button

Now what do I do?
Tom

Don't shoot. Please!!

My fault.

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like '" & strCurrDate & "*'")

I should not have left in the "=". My apologies for not double-checking
the code.

Tom wrote:

I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075'

Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE=
'051122*".

I have no idea what I'm doing, I'm plugging stuff in
following along. I do not know any formulas, calculations
or anything else for that matter. Somewhat familiar
with excel and those formulas, not this however.

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.

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

 

[Steve Schapel]

Tom,

No, this won't work unless you have the leading 0 in the Run Number.
varHighValue is looking in the table for a Run Number that starts with
051123 so if you enter Run Number as 51123001 this will not match. You
will have to enter it as 051123001.

 

[Tom]

John,
That number is already set by the first part of the formula.
It is automatic by yrmmdd. It only takes the last 2 digits of the year.
By default, it is leaving off the "0". until 2010 then it would b be 101123
The date is changing fine, it's the last three numbers. So:

To make it easy:
I'm going to create another control, without a label on the FORM.
place it next to the YYMMDD. That control will be the counter.
starting at 001

So I need two things:
1. Formula for changing the date only in yymmdd format no "/"(without the
001)
2. Formula for changing the number, incrementally (max 999) always starting
at 001 at 2400 hours.

should appear on form as
RUN NUMBER 51123 001
then, after I record an entry should read
RUN NUMBER 51123 002 (next entry 003, etc. max 999)
then, at midnight tonight should read
RUN NUMBER 51124 001

That will make it a whole lot easier
Tom

So, now you know that DMax function is not finding any match in ABLE_Table1.

Open that table. Is there a value in the table that should match? If so,
what is it? If not, then I suspect that you aren't storing the newly
created value in this table.

Are you assigning the new value to a control on a form? Is that control's
source set to the Run Number field in Able_Table1?

Alrighty.
No I get the message:

varHighValue is
with an "OK" button

Now what do I do?
Tom

Don't shoot. Please!!

My fault.

varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _
"[RUN NUMBER] Like '" & strCurrDate & "*'")

I should not have left in the "=". My apologies for not double-checking
the code.

Tom wrote:

I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075'

Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE=
'051122*".

I have no idea what I'm doing, I'm plugging stuff in
following along. I do not know any formulas, calculations
or anything else for that matter. Somewhat familiar
with excel and those formulas, not this however.

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.

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