Calculating when a balance is due

[tinams]

This is very hard for me to explain. So I need to give specific examples. I have a field called Program Name. It's a pop-up list, and an example would be, "SHP Level III, Oct 29-Nov 1, 2002". I need a calculation that will separate Oct 29, the beginning date, so that I can then calculate for individual records when a balance is due. Can I do this without having to create two separate fields-one for Program Name and one for Program Date? I hope this question is clear. Thanks!

[LiveOak]

I would recommend you create a separate field. The problem with free form entry of data into one field is just that, it will be free form. Someone will enter the date as October-November, 1992 or October 1-5, 1992. Whatever parser you create to break out the date will be defeated by yet the next interpretation of how a date can be entered. I would have separate fields for Program Name, Start Date and Due Date, it's just good design practice.

-bd

[tinams]

But my field is not free form. It's a pop-up list. I have control of what is put into a pop-up list. By putting the program and the date into the same field I am trying to eliminate data entry time. We'll have something like 50 people register for the same class, which will be on the same days. So there's no way do this with only one field?

[kennedy]

Are you sure its going to save data entry time? How long will that list get?

If its longer than about 6 or 7, then your data entry people will actually prefer two short pop-up lists rather than one long one.

Alternatively, you can create a separate file of all your programs and start dates with a calculated field that is the combo of the two. Then you can create a value list off that calculated field. Then, rather than parse it, do a lookup in that other file to get the separate info.

Finally, if you just want to parse it, just put a unique character right before the date, such as "-" and then do

Middle(Program, Position(Program, ".", 1)+2, 6)

but make sure you write "Nov 1" with two spaces or "Nov 01" so that its always six characters, unless you want that calc much more complicated.