Do I use marcos or codes??? 3

[nathanieldross]

How can Access fill in a number field, filtering from 2 date fields in my form?
For example:
I have two fields formatted in a date mask (mm/dd/yy)
and one other field formatted in a currency mask,
I would like the database to populate the currency field from the two fields in a date mask, after a 30 day date.
11/19/01 - 12/19/01- No Fee
11/19/01 - 12/25/01- 120 Late fee

 

[JerryDennison]

This would be best handled using a user defined function. In a module, create a the following function:

Public Function AgeAccount(BeginDate As Date, EndDate As Date) As String

Select Case DateDiff("d",BeginDate, EndDate)
Case < 30
AgeAccount = &quot;No Fee&quot;
Case 30 To 59
AgeAccount = &quot;30 Late Fee&quot;
Case 60 To 119
AgeAccount = &quot;60 Late Fee&quot;
Case Else
AgeAccount = &quot;120 Late Fee&quot;
End Select

End Function

You can modify it to meet your specific needs. To use the function place the following in an unbound control on your form:
=AgeAccount([AccountBeginDate], Date())

Substitute your actual control/field name for the placeholder given.

 

[nathanieldross]

Hi Jerry, thank very much for your input and help.
As I look at my previous email, some of the information I supplied was not clear. I apologize for that.
My database has 3 fields that are very important,
The first field is a date field called (date of termination), the second field is called (date filed with the NASD) and the third field is called (Late Fees)a currency field. When the user inputs a date in both of the (dates) field that is more than 30 days apart(11/20/2001 and 12/22/2001, the (late fees) field will automatically generate a fee for the amount of $120 dollars.
If the user inputs a date in both date fields that is less than 30 days, the (late fees) field will remain at $0 dollars. Is this possible?? or do I still use the same function??
Thank you once again for your help

 

[JerryDennison]

Yes, the function can work for either method. It is setup to allow you to send the dates you want to it. You just need to change it to return a numeric value instead of a text string. You can modify it anyway you wish, adding or removing &quot;levels&quot; etc.

 

[nathanieldross]

Hi Jerry, the function that you have came up with, WORKS!!!
but there is still one little problem with this function.
When the dates are inputed into the fields, that are over 30 days, the table does not record the fee amount of $120. I think that the &quot;amount field&quot; is unbound. How do I change the &quot;Amount Field&quot; from an unbound field to a bound field.
When I go into the Form, and click properties, the &quot;Control Source&quot; is fill with the function, so the tables can not identify the &quot; Amount Field&quot; in the table.
Can this be changed or corrected??

Thank you so much for you help in this.
Nathaniel

 

[JerryDennison]

It isn't necessary to store this calculated value in your table to still get the desired result. There are several ways to approach this and not knowing your specific situation it is difficult to advise. Normally, you should not store a calculated result in your table. But, this may be one of those rare occasions where doing so is ok PROVIDED you do such as a transaction. For example, this fee should only be applied and stored when payment is actually made or the maximum fee can be applied whether payment is made or not. In any case, you need to add or modify the record as the result of a specific transaction. This can be done through automation during billing cycle processing or as a result of individual postings. How you update the record will depend on which method you choose (you can do them all if you wish) but each would be handled differently. For example, you could use an update query to update the fee field for each sale based on the date the query is run and the original sale date.

 

[nathanieldross]

Thanks again Jerry for ypur help. But I am still unclear on your advise.

I have two required fields that are very important in this &quot;Monthly Termination Form&quot;
The two dated fields are called &quot;Termination Date&quot; and &quot;Date Filed with NASD&quot; if these two dated fields are more than 30 days apart a $120.00 fee is filled in a third field called &quot;Late Fees&quot;.
ok, here the problem, when a user inputs dates into the two dated fields, the third field &quot;Late fees&quot; fills in the correct amount of $120.00 in the Form, But when I check the Table and Query under this Form it does not show the $120.00 amount. The Table and Query show $0.00. How do I fix this? -------- Jerry I also have one other question.
In the same Form &quot;Monthly Termination Report&quot; the &quot;Late Fee&quot; field shows this &quot;#error&quot;, can this be corrected???
Let me know if I can email a picture of the &quot; Monthly Termiantion Form&quot; in my database.

Thank again for your help...
Nathaniel Ross

 

[nathanieldross]

Hi Jerry, thanks again for your help, the function you made worked great.
I have another question for you, In a Form that I call &quot;Review Criteria Form&quot;, I have 4 check boxes that are Linked to a table, the table displays the information correctly when checked, but I would the checked boxes to display the word labels of a check box, when check ,on diffrent Form. Is that possible???

Thanks again
Nathaniel Ross

 

[JerryDennison]

I'm sorry, I don't really understand the question. You want the name of each checkbox that's been checked on one form displayed on another form?

 

[nathanieldross]

Yes, I have two main forms, called the Review Criteria Form and the Summary Form. In the &quot;Review Criteria Form&quot; there is a command buttom that displays a small form called &quot;Bankruptcy Form&quot; in this form there are 4 checkboxes, each of these boxes are named with labels. I would the labels to display on the &quot;Summary Form&quot; when checked on the &quot;Bankruptcy Form&quot;.
I hope I have provides mores details, thank again for your help