assess fee

[Ami]

I posted this message a while back, I thought i understood what to do but
have run into problems with it, Is it safe to assume that this should be
typed in a module. I dont know how to go about implementing this. Please
Help!
My question was...
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?


This was the reply i got from a very knowledgeble person:

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;

I just dont know where to start in putting this into play... Ive never
written the programming side before.

 

[Larry Linson]

Databases are not based on Forms, but on Tables of Data. Depending on your
Table design, you could calculate the monthly fees when you create Invoices,
or when you make in Inqury about the clients financial standing. With a
different design, you might need to do something similar to what you
describe. Without knowing how you have defined the tables and fields that
contain your data, we really can't say.

Larry Linson
Microsoft Access MVP

I posted this message a while back, I thought i understood what to do but
have run into problems with it, Is it safe to assume that this should be
typed in a module. I dont know how to go about implementing this. Please
Help!
My question was...
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?


This was the reply i got from a very knowledgeble person:

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;

I just dont know where to start in putting this into play... Ive never
written the programming side before.

 

[Ami]

I have two tables, Table 1 holds the client name and beginning balance.
Table 2 has date, payment, fee, adjusted balance fields. I would like to
assess the $5 fee on all clients at the same time. No invoices will be
created, this is a situation where people make monthly payments with no
invoice sent out. So if they come in after 3 months and havent made a
payment in that time, when I pull up their name, I would like for it to show
me that they owe $15 in service fees automatically. I hope this helps you
understand what I have done so far...
Thank you for helping me!

Databases are not based on Forms, but on Tables of Data. Depending on your
Table design, you could calculate the monthly fees when you create Invoices,
or when you make in Inqury about the clients financial standing. With a
different design, you might need to do something similar to what you
describe. Without knowing how you have defined the tables and fields that
contain your data, we really can't say.

Larry Linson
Microsoft Access MVP

I posted this message a while back, I thought i understood what to do but
have run into problems with it, Is it safe to assume that this should be
typed in a module. I dont know how to go about implementing this. Please
Help!
My question was...
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?


This was the reply i got from a very knowledgeble person:

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;

I just dont know where to start in putting this into play... Ive never
written the programming side before.