Incrementing characters

[rbeach]

I need to increment text from a table for new records. If Null set to
000000A. If 000000A exists, then it would need to increment to 000001B. Below
is a sample of the code I have to increment the number but no leading zero's
are added from this formula. The letter update has not been added, but i will
need this updated also. Please assist if at all possible.

RedNum = Chr(Asc(DMax("Nz(Left([RedNum],6),000000A)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

 

[John W. Vinson]

I need to increment text from a table for new records. If Null set to
000000A. If 000000A exists, then it would need to increment to 000001B. Below
is a sample of the code I have to increment the number but no leading zero's
are added from this formula. The letter update has not been added, but i will
need this updated also. Please assist if at all possible.

RedNum = Chr(Asc(DMax("Nz(Left([RedNum],6),000000A)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

Is this a hexadecimal number (i.e. the next value after "000000F" would be
"0000010"? or do you want to go on to Z, or what? Access will NOT recognize
hex numbers as numbers unless you use the syntax. And it will not recognize
mixed text and numbers as numbers in any case!

If so, try using the syntax &000000A as a hex constant (or just use the number
10, which is the same thing); you can use the Hex() function to return the hex
string equivalent of a number.

 

[rbeach]

This is not hex. I have the format as text and the first six are numeric with
a letter at the end.
--
Rick

I need to increment text from a table for new records. If Null set to
000000A. If 000000A exists, then it would need to increment to 000001B. Below
is a sample of the code I have to increment the number but no leading zero's
are added from this formula. The letter update has not been added, but i will
need this updated also. Please assist if at all possible.

RedNum = Chr(Asc(DMax("Nz(Left([RedNum],6),000000A)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

Is this a hexadecimal number (i.e. the next value after "000000F" would be
"0000010"? or do you want to go on to Z, or what? Access will NOT recognize
hex numbers as numbers unless you use the syntax. And it will not recognize
mixed text and numbers as numbers in any case!

If so, try using the syntax &000000A as a hex constant (or just use the number
10, which is the same thing); you can use the Hex() function to return the hex
string equivalent of a number.

 

[BruceM]

The sequence is not clear. The first value is 000000A. The second is
000001B. Is the third 000002C? If so, what comes after 000009J? You can
use the Chr function to convert a number to a letter, but you need to use
the Format function to get leading zeros. As you have described the
sequence, just increment a number using DMax + 1, and format it with 6
zeros. For display purposes you could use:
FullNumber: Format([RedNum],"000000") & Chr(Right([RedNum],1) + 65)
Again, there is no way to tell what you would have happen after 000009J.

This is not hex. I have the format as text and the first six are numeric
with
a letter at the end.
--
Rick

I need to increment text from a table for new records. If Null set to
000000A. If 000000A exists, then it would need to increment to 000001B.
Below
is a sample of the code I have to increment the number but no leading
zero's
are added from this formula. The letter update has not been added, but i
will
need this updated also. Please assist if at all possible.

RedNum = Chr(Asc(DMax("Nz(Left([RedNum],6),000000A)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

Is this a hexadecimal number (i.e. the next value after "000000F" would
be
"0000010"? or do you want to go on to Z, or what? Access will NOT
recognize
hex numbers as numbers unless you use the syntax. And it will not
recognize
mixed text and numbers as numbers in any case!

If so, try using the syntax &000000A as a hex constant (or just use the
number
10, which is the same thing); you can use the Hex() function to return
the hex
string equivalent of a number.

 

[John W. Vinson]

This is not hex. I have the format as text and the first six are numeric with
a letter at the end.

I agree with Bruce. It's not at all clear what result you want. What is the
context? How will this be used? What is incrementing - the number, the letter,
or both together? What happens after J (or maybe after Z)?

 

[rbeach]

This will increase both the letter and number individually. The number needs
to have from zero to 999999 available and the letter will need to have a to z
available.(I am sure all letters will never be used). Once I have the basic
sequence to increment both of these values, I will be able to state when
these are to increment. Below are the statements that I have in place for
each:

RedNum = Chr(Asc(DMax("Nz(Right([RedNum], 6), 000000)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

RedNumSuffix = Chr(Asc(Nz(Right(RedNumSuffix, 1), "A")))

If I add the DMax statement as below, the letter increment stops working. I
am not a seasoned user of Access.

Thanks,

 

[rbeach]

My apologies, I did not add the statement I use with the DMax function. It is
listed below:

RedNumSuffix = Chr(Asc(DMax(Nz(Right([RedNumSuffix], 1), "A"),
"[InstalledCircuitTable]", "[RedNumSuffix]") + 1))

--
Rick

This will increase both the letter and number individually. The number needs
to have from zero to 999999 available and the letter will need to have a to z
available.(I am sure all letters will never be used). Once I have the basic
sequence to increment both of these values, I will be able to state when
these are to increment. Below are the statements that I have in place for
each:

RedNum = Chr(Asc(DMax("Nz(Right([RedNum], 6), 000000)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

RedNumSuffix = Chr(Asc(Nz(Right(RedNumSuffix, 1), "A")))

If I add the DMax statement as below, the letter increment stops working. I
am not a seasoned user of Access.

Thanks,




--
Rick

I agree with Bruce. It's not at all clear what result you want. What is the
context? How will this be used? What is incrementing - the number, the letter,
or both together? What happens after J (or maybe after Z)?

 

[BruceM]

So the sequence is as I speculated: 000000A, 000001B, 000002C, 000003D,
etc.? The reason I asked about 000009J is that after that the number would
be 000010K, and so forth to 000025Z. What then? 000026A?

You are using the Right function for the Suffix field. Why is that? How
many letters could the suffix contain? You have mentioned only a single
letter.

I have discovered, rather to my surprise, that DMax + 1 will increment a
letter. However, I have not had any luck applying the Nz function in that
context. Maybe type conversion is necessary, but I'm not going to
experiment any further. As I mentioned, I increment a number and convert it
to a letter for display when I need to increment a letter.

The question remains: what is the sequence you seek?

My apologies, I did not add the statement I use with the DMax function. It
is
listed below:

RedNumSuffix = Chr(Asc(DMax(Nz(Right([RedNumSuffix], 1), "A"),
"[InstalledCircuitTable]", "[RedNumSuffix]") + 1))

--
Rick

This will increase both the letter and number individually. The number
needs
to have from zero to 999999 available and the letter will need to have a
to z
available.(I am sure all letters will never be used). Once I have the
basic
sequence to increment both of these values, I will be able to state when
these are to increment. Below are the statements that I have in place for
each:

RedNum = Chr(Asc(DMax("Nz(Right([RedNum], 6), 000000)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

RedNumSuffix = Chr(Asc(Nz(Right(RedNumSuffix, 1), "A")))

If I add the DMax statement as below, the letter increment stops working.
I
am not a seasoned user of Access.

Thanks,




--
Rick

On Mon, 20 Oct 2008 23:02:02 -0700, rbeach
<[email protected]>
wrote:

This is not hex. I have the format as text and the first six are
numeric with
a letter at the end.

I agree with Bruce. It's not at all clear what result you want. What is
the
context? How will this be used? What is incrementing - the number, the
letter,
or both together? What happens after J (or maybe after Z)?

 

[John W. Vinson]

This will increase both the letter and number individually. The number needs
to have from zero to 999999 available and the letter will need to have a to z
available.(I am sure all letters will never be used). Once I have the basic
sequence to increment both of these values, I will be able to state when
these are to increment. Below are the statements that I have in place for
each:

RedNum = Chr(Asc(DMax("Nz(Right([RedNum], 6), 000000)",
"[InstalledCircuitTable]", "[RedNum]") + 1))

RedNumSuffix = Chr(Asc(Nz(Right(RedNumSuffix, 1), "A")))

If I add the DMax statement as below, the letter increment stops working. I
am not a seasoned user of Access.

Thanks,

This would be a LOT easier if you had two fields, RedNum and RedNumSuffix,
number and letter independently. That way you could have code like this to
increment them:

Private Sub cmdIncrementRednum_Click()
Me!RedNum = DMax("[RedNum]", "[InstalledCircuitTable]") + 1
End Sub

and

Private Sub cmdIncrementRednumSuffix_Click()
Const Alpha String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Me!RedNumSuffix = Mid(Alpha, InStr(Alpha, DMax("[RedNumSuffix]", _
"[InstalledCircuitTable]", "RedNum = " & Me!RedNum)) + 1, 1)
End Sub

 

[Clif McIrvin]

This would be a LOT easier if you had two fields, RedNum and
RedNumSuffix,
number and letter independently. That way you could have code like
this to
increment them:

Private Sub cmdIncrementRednum_Click()
Me!RedNum = DMax("[RedNum]", "[InstalledCircuitTable]") + 1
End Sub

and

Private Sub cmdIncrementRednumSuffix_Click()
Const Alpha String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Me!RedNumSuffix = Mid(Alpha, InStr(Alpha, DMax("[RedNumSuffix]", _
"[InstalledCircuitTable]", "RedNum = " & Me!RedNum)) + 1, 1)
End Sub

John, I'm curious why you chose your approach instead of

Me!RedNumSuffix = chr$(asc(DMax("[RedNumSuffix]", _
"[InstalledCircuitTable]", "RedNum = " & Me!RedNum)) + 1)

 

[John W. Vinson]

John, I'm curious why you chose your approach instead of

Me!RedNumSuffix = chr$(asc(DMax("[RedNumSuffix]", _
"[InstalledCircuitTable]", "RedNum = " & Me!RedNum)) + 1)

Just didn't think of it <g>... yours is better.

I would check for the On Beyond Zebra phenomenon, e.g. pop an error if the
Suffix is already Z rather than inserting a [.

 

[Clif McIrvin]

John, I'm curious why you chose your approach instead of

Me!RedNumSuffix = chr$(asc(DMax("[RedNumSuffix]", _
"[InstalledCircuitTable]", "RedNum = " & Me!RedNum)) + 1)

Just didn't think of it <g>... yours is better.

I would check for the On Beyond Zebra phenomenon, e.g. pop an error if
the
Suffix is already Z rather than inserting a [.

Absolutely.

Some time back I built a function for incrementing ... it's not very
smart, and also has the on beyond zebra issue but in my usage (so far,
at least) the user simply makes the appropriate adjustment to the
control value before moving on:

Public Function Incr(ByVal var As Variant, _
Optional step As Single = 1) As Variant
Select Case VarType(var)
Case vbInteger To vbDate, vbDecimal
Incr = var + step
Case vbString
Incr = Chr(Asc(var) + step)
Case vbObject
Incr = var 'just return the object
Case Else
Incr = CVErr(2005) 'return an error value instead of Null
'Error 5 is invalid procedure call
End Select
End Function

A typical call would be something like

Me.SomeControl.Value = Incr Me.SomeControl.Value

 

[rbeach]

I appreciate everyone's assistance. This is now working properly.
--
Rick

John, I'm curious why you chose your approach instead of

Me!RedNumSuffix = chr$(asc(DMax("[RedNumSuffix]", _
"[InstalledCircuitTable]", "RedNum = " & Me!RedNum)) + 1)

Just didn't think of it <g>... yours is better.

I would check for the On Beyond Zebra phenomenon, e.g. pop an error if the
Suffix is already Z rather than inserting a [.