Period numbers

[HeatherM]

My organisation reports period numbers in a six figure
format:y1y1y2y2pp, so for the sixth period of year 2003-
2004 it would be 030406.
I have a period field in my transactions table and it
expects six digits to be entered, but is there any way I
can make it expect a valid period number to be entered ie
digits 3and4 should be a number 1 higher than digits 1and2
and digits 5and6 should be a number between 01 and 13?
Do I have to make up a table with all the allowable
combinations of digits and have the form look it up?

 

[Jen]

Hi Heather,

Sounds like you could make a lookup table and go that
route - which has some limitations. You could also make
it more dynamic by writing a function (possibly boolean)
that would have all the logic in it and would return true
if the field value passes the test, and false if it fails.
Maybe something like this - you'll need to maybe change
some of the values to text in order to do string
manipulation....

Function verifyPeriod(fieldName as variant) as Boolean
Dim length as Integer
Dim period as Integer

length = len(fieldName)

If length = 6 then
.......' put logic here-using the left and mid functions
.......' if the field passes then set verifyPeriod = True

else
verifyPeriod = False
End If
End Function

Hope this helps get you on your way.

Jen