User function

[Robbydobbs]

I have three fields:

[date to text] (Date)"3/1/2003"

[text field] (number code) of "01"

[calculation field] (Session Number) = (Date) + (Number Code)

Is there a way that a user can enter the (Date) from a value list and enter the (number code) manually, run a script that says something to the fact that if the date is "3/1/2003" and number code is "01" that the field (Session Number)would be "31200301".

Then whenever the date field shows the date of "3/1/2003" the (number code) is automatically entered with "01" and the session number would automatically populate with "31200301".

[BobWeaver]

You don't need a script to fill out the Session Number field. Just make it a calculation with the formula:

Right("00"&Month(Date),2)&Right("00"&Day(Date),2)&Right("0000"&Year(Date),4)&Right("00"&Number Code,2)

There is a simpler formula:

Substitute(DateToText(Date)&Number Code,"/","")

but this won't put in leading zeros which you need to avoid confusion between dates like 12/1/2003 and 1/21/2003 which would both produce a code of 1212003. With leading zeros you get an unambiguous result of 12012003 and 01122003.

Now create another calculation field cNumberCode with the formula:

GetField("Number Code")

Once you have this, create a selfjoin relationship using the date field on both left and right side. Then, in define fields, set the Number Code field to auto-enter a looked up value via this relationship from the cNumberCode field.

[Robbydobbs]

Bob your a genius. That worked perfectly. Thank you.

[RussBaker]

Now that you have a text field generated this way, you have lost your logical link with the date function. If you're going to do any sorts on this resulting field, then you might want to consider making the order yyyymmdd## because if you leave it the way it is now as mmddyyyy##, all your records from a day of a month will be grouped together - no matter which year they have been generated in.

[BobWeaver]

Good point. This is how I format all my dates too (for that very reason). I used to rant about it to everyone who would listen, but I must be getting too mellow now.