A figure appear automatically once a year in a field (1 Viewer)

Khalil Islamzada · Mar 14, 2018

Dear all,
I have an access database which use to print bill for customers. one of the fields which I have is meter rent, I want that automatically once a year this field fills by 100, can anyone help me?

For your more information, I have many other fields including current date/time.

Best,

Ranman256 · Mar 14, 2018

use an update query
set the field = 100.

missinglinq · Mar 14, 2018

Does the rent come due the first billing month of the year, for everyone?

Does the rent come due on the anniversary of the initiation of service?

When?

Linq ;0)>

Khalil Islamzada · Mar 14, 2018

Actually this figure should appear once a year automatically, for example if a customer was billed in January 2015, he should be billed in January 2016 or after that time. because sometimes billing don't occur on the same date. maybe next time billing would be February 2016.

Please if anyone has any idea.

Pat Hartman · Mar 15, 2018

Typically you will have a table that holds invoices and some of those invoices will be paid and some will be unpaid. When an invoice is paid, it should update the paid through date. Then you have a query that runs once a week, once a month, once a year, or however frequently you need to run it to do billing. That query looks for accounts that are expiring in the next x days. If your accounts are billed on an annual basis, it would typically be 40 days. That gives you enough time to get the bills printed and mailled and still give the customer 30 days to pay them. This query is what generates the new invoices. You have a second query that runs fairly frequently that looks at unpaid invoices and ages them. This query would not generate new invoices, it would reprint the existing invoice with a late notice.

Khalil Islamzada · Mar 17, 2018

Hi Pat Hartman,

Thanks for your comment, I think I should say it this way,

I have created a billing database for a water utility, all the billing and payments process are fine and have no problem, only issue I have now is that the water meter is asset of the water utility and they bill customers once a year 20$. I have to mention that customers are billing 4 times a year for water consumptions but this water meter rent should be billed once a year only.
That is why I need this 20 appear once a year in this field automatically.

Hope you got my issue.

Best,

arnelgp · Mar 18, 2018

firstly, you need to put the Cost of the meter in a table together with customer number.
customers may have different cost of meter when they acquired them.
also you don't just bill each customer per year without knowing if they
already have paid the cost of the meter over the years.
unless this cost is perpetual.

now i assume you have a form for entry of bills.
the thing to do is on the Open event of this form, to check if
a bill of rent for the meter has already been made for each
customers:

Code:

Private sub form_open()
	' gather all customer's who has not been billed
	' for the meter rent this year
	dim rs as dao.recordset
	set rs=currentdb.openrecordset("select distinct [customer number] from [billing table] where " & _
		"[customer number] not in (select t1.[customer number] from [billing table] as t1 " & _
		"t1.[particulars]='meter rent' and year(t1.[billing date])=year(date());", dbopensnapshot)

	with rs
		if not (.bof and .eof) then .movefirst
		while not .eof
			' add entry to billing table for customers
			' not yet billed for meter rent
			'
			' also check if this is a new customer for this year
			' coz we dont want him to bill this year but next year

			dim dte as variant
			dte = dmin("[billing date]", "[billing table]", "[customer number]='" & ![customer number] & "'"
			if isnull(dte)
				'no billing yet (maybe new customer) then don't bill
			elseif year(dte) = year(date())
				'only got billed this year (new customer)
			else
				'old customer, we bill him
				currentdb.execute _
				"insert into [billing table] ([customer number],[bill amount],[particulars]) " &  _
				" select '" & ![customer number] & ", 20, 'meter rent';"
			end if
			' move to next customer
			.movenext
		wend
		.close
	end with
	set rs=nothing
end sub

A figure appear automatically once a year in a field (1 Viewer)

Khalil Islamzada

Registered User.

Ranman256

Well-known member

missinglinq

AWF VIP

Khalil Islamzada

Registered User.

Pat Hartman

Super Moderator

Khalil Islamzada

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread