Assess fee on a monthly basis automatically

A

Ami

I have a main form and a subform. The main form holds client information;
name, account #, etc. The subform is to record new payments on accounts. I
would like to automatically assess a five dollar fee on the first of every
month. I am not quite sure how to go about this. Any suggestions?

Thanks!
 
A

Arvin Meyer

Ami said:
I have a main form and a subform. The main form holds client information;
name, account #, etc. The subform is to record new payments on accounts. I
would like to automatically assess a five dollar fee on the first of every
month. I am not quite sure how to go about this. Any suggestions?
Click to expand...

First you must see if the fee has already been assessed for this month. To
do that, you count the records after the first of the month. So:

Function FirstOfThisMonth() As Variant
' PURPOSE : Returns the first day of the current month.
FirstOfThisMonth = DateSerial(Year(Date), Month(Date), 1)
End Function

Then you can run a query:

SELECT MyTable.DateField
FROM MyTable
WHERE (((MyTable.DateField)>FirstOfThisMonth()));

And if it returns a record, exit the sub, or if not add a record:

INSERT INTO MyTable ( [DateField], [CurrencyField] )
SELECT FirstOfThisMonth() AS Expr1, 5 AS Expr2;

If you need more help, post back.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
A

Ami

Thank you so much for your help! I was struggling.

Ami

Arvin Meyer said:
Ami said:
I have a main form and a subform. The main form holds client information;
name, account #, etc. The subform is to record new payments on accounts. I
would like to automatically assess a five dollar fee on the first of every
month. I am not quite sure how to go about this. Any suggestions?
Click to expand...

First you must see if the fee has already been assessed for this month. To
do that, you count the records after the first of the month. So:

Function FirstOfThisMonth() As Variant
' PURPOSE : Returns the first day of the current month.
FirstOfThisMonth = DateSerial(Year(Date), Month(Date), 1)
End Function

Then you can run a query:

SELECT MyTable.DateField
FROM MyTable
WHERE (((MyTable.DateField)>FirstOfThisMonth()));

And if it returns a record, exit the sub, or if not add a record:

INSERT INTO MyTable ( [DateField], [CurrencyField] )
SELECT FirstOfThisMonth() AS Expr1, 5 AS Expr2;

If you need more help, post back.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
 
Top