Numeric Alpha Field

[DSTR3]

I have a field that contains data like this.
1
1a
1b
2
2a
3
3a
3b
3c
etc... I need a DMax statement that will find the highest value of a series, then add the next alpha to it. For instance if I had a 3, the next value would be 3d, if it's 1 then it would be 1c. The Table is called called tblChecks and the field is ChkAlias. Any help appreciated.
Thanks
DSTR3

 

[PHV]

You may play with the Val, Right, Asc and Chr functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DSTR3]

I tried this but I get...
1,2,3 instead of 1,1a,1b,1c etc...

Dim strWhere As String
Dim intChar As String
intChar = Asc(Nz(DMax("NumberID", "tblTest", "NumberID = '" & Me.Text3 & "'"), "@"))
Me.TxtNumber = Me.TxtNumber & Chr(intChar + 1)

Thanks
DSTR3

 

[Zion7]

Maybe something like this,

strLetter = DMax("Right(NumberID,1)", "tblTest", _
"Left(NumberID,1) = " & Me.Text3 )

''''Me.Text3 = a single digit

strLetter = Asc(strLetter) + 1


varResult = Me.Test3 & strLetter

 

[DSTR3]

Can't quite make out what is happening here. I'm still new!
But I do see the problem with this...
1a
22a
234a

What happens in this situation? With this?
DMax("Right(NumberID,1)

Thanks
DSTR3

 

[SkipVought]

Isn't this more of a dilemma?
[tt]
1a
11a
2a
22a
[/tt]
on the pure numbers, have you tried a trailing space?
[tt][highlight white]
1
11 [/highlight]
[/tt]


Skip,

_[red][/red]

 

[DSTR3]

Its becoming one. It's Simple. I have a field with Numbers and Alpha...
1
1a
1b
33
33a
343
343a
343b
5
3
etc...
Why when I go to add a record can't I merely check fr the highest alpha that corresponds with the number I've choosen. If I pick 1 then t would be 1b and the new record would become 1c. If it's 3, the new record would become 3a, so on and so forth. Is it really this dificult? I'm going on three days trying to figure this out!
Thanks
DSTR3

 

[Zion7]

I didn't realise, your number would et in the tens.

maybe this,

strLetter = DMax("Right(NumberID,1)", "tblTest", _
"Left(NumberID,Len(Me.Text3)) = " & Me.Text3 )

this returns just the end letter, that corresponds,
with the number you gave.

So once you get the letter, you up it a notch,
in the alphabet, then append the initial number.

...i forgot this

strLetter = Chr(Asc(strLetter) + 1) 'raises letter a notch

complete the desired result...
varResult = Me.Test3 & strLetter

 

[TheAceMan1]

How are ya DSTR3 . . .

Your scheme appears to be [blue]ambiguous! . . .[/blue]

If you have say [blue]1z[/blue] and [blue]2[/blue] already exist, whats the next number to be and how do you tie it into the [blue]1 series[/blue] (believing you allowing for series with this many members)? . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .