Input Mask and Extracting Characters

[SMHSleepy]

Hello, my key field is "RecNum" in the following format: YY-MMDD-?...?L
Where ?...? is "ID"(an integer which can vary from 1 to 10 digits) and L is a capital letter (A-Z). I tried creating an input mask but can't figure a way to account for the variability in the number of digits for the unique identifier.
Also, is there a way to be able to extract the ?...? portion of "RecNum" and have it automatically posted to the "ID" field? Furthermore, can I extract the YY-MMDD portion and have it posted to the "Study Date" field?
Thank you.

 

[missinglinq]

I guess you need to use 999999999. From Help

9 Digit or space (entry not required, plus and minus signs not allowed).

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

Hit "send" too soon! Meant to add that it would make more sense, to me, to enter the ID and StudyDate, then concatenate these two together for the RecNum, rather than doing just the opposite! Much easier, really, considering the variable length of the ID portion.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[RoyVidar]

What you are describing, are some of the reasons why there's a rule of atomicity when working with databases. Basically, you shouldn't be able to get more than one piece of information out of one field.

Here you're getting a date and a and an additional number.

Keep them in separate fields, and if you need to, then make them a compound/composite primary key - which means one primary key consisting of two fields/columns.

Roy-Vidar

 

[TheAceMan1]

How are ya SMHSleepy . . .

As a primarykey, [blue]RecNum[/blue] as you have it is quite unwieldly and is already causing you problems. I'm suggesting you make [blue]RecNum[/blue] a part of the table (if you really need it) and prescribe a much simpler numeric field for for your primarykey (autonumber or equivalent).

In access there's no such thing as [blue]record numbers[/blue], just unique identifiers.

Post back a sample RecNum and quote the function of its parts!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Thank you both for your suggestions. I'm actually converting an old database done on Paradox 8 over to Access. There are over 20,000 entries with only the RecNum, hence my desire to extract rather than concatenate.

 

[missinglinq]

You don't say where you want to do this, but the general syntax,in VBA code, would be

Me.Study_Date = Right(Me.RecNum, Len(Me.RecNum)- InStrRev(Me.RecNum, "-"))

Me.ID = Left(Me.RecNum, InStrRev(Me.RecNum, "-") - 1)

Creating calculated fields in a query it would be

Study_Date:Right([RecNum], Len([RecNum])- InStrRev([RecNum], "-"))

ID:Left([RecNum], InStrRev([RecNum], "-") - 1)

[/code]

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!