Assess fee on a monthly basis automatically

[Guest]

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!

 

[Arvin Meyer]

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?

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

 

[Guest]

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

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?

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