Text Box Help

[LHolden]

I have a text box on a form which shows the number of people covered under medical coverage for a certain company. My dilemma is that the number of people covered may change month to month, and invoices need to be run quarterly, so at least 3 months worth of covered lives need to be stored. I am trying to get the text box to show how many lives are covered based on month, but can't figure it out.

Any help is greatly appreciated!

 

[rctjoe24]

Is the data all stored in one field in the table or is it compiled together on the form for display? Is the data hand entered into this text field?

To me this doesn't seem like the wisest approach to storing your data.

From the sound of it, you need a seperate record for each person covered.

 

[LHolden]

Sorry, maybe I wasn't as clear as I thought I was. The table is set up in a manner such as this:

Client|LivesJan|LivesFeb|LivesMar...|LivesDec
A .......5 ............. 6 .......... 7................ 6
B.......10.............8.............12.............11

The form that the textbox is on has a combo box which picks what client the end user wants to see. My goal with the textbox is to show the proper number of lives covered for a specific client for the current month.

Hope this is more clear.

 

[LHolden]

I'll answer my own question here for the benefit of anyone who comes looking for an answer to this question.

The trick is to use the MonthName() Function. Combining this fucntion with the Month() and Date() functions will produce simply the current month. In other words, today the function MonthName(Month(Date())) would produce "August" or MonthName(Month(Date()), Yes) would produce "Aug". So the way I have coded the text box is using this function in combination with a switch() function:

Switch(MonthName(Month(Date()))="January", [LivesCoveredJanuary], MonthName(Month(Date()))="February", [LivesCoveredFebruary], etc.)