Hasanain,
Try this:
'DAYS IN THE MONTH ARRAY
Dim monthMaxDay(12) As Byte
monthMaxDay(1) = 31 'JAN
monthMaxDay(2) = 28 'FEB
monthMaxDay(3) = 31 'MAR
monthMaxDay(4) = 30 'APR
monthMaxDay(5) = 31 'MAY
monthMaxDay(6) = 30 'JUN
monthMaxDay(7) = 31 'JUL
monthMaxDay(8) = 31 'AUG
monthMaxDay(9) = 30 'SEP
monthMaxDay(10) = 31 'OCT
monthMaxDay(11) = 30 'NOV
monthMaxDay(12) = 31 'DEC
'HANDLE LEAP YEARS - occur in years exactly divisible by four,
' except that years ending in 00 are leap years
' only if they are divisible by 400.
' So, 1700, 1800, 1900, 2100, and 2200 are not leap years.
' But 1600, 2000, and 2400 are leap years.
dim calYear as integer
calYear = datepart("yyyy",date())
If (calYear Mod 4 = 0 And Right(CStr(calYear), 2) <> "00") Or
(Right(CStr(calYear), 2) = "00" And calYear Mod 400 = 0) Then
monthMaxDay(2) = 29
End If
'TODAY IS THE LAST DAY OF THE MONTH
if datePart("d",date()) = monthMaxDay(datePart("m",date())) then
'DETERMINE PRIOR MONTH AND YEAR
Dim priorMonth as byte, transYear as integer
priorMonth = datePart("m",date()) - 1
transYear = datePart("yyyy",date())
if priorMonth = 0 then 'IF CURRENT MONTH = JANUARY
priorMonth = 12
transYear = transYear - 1
endif
'PROCESS FOR ALL TENANTS
set rs = db.openRecordset("SELECT TenantID, TenantName, TenantRent FROM
tblTenant ORDER BY TenantID;")
do until rs.eof
'LOOK FOR PRIOR MONTH PAYMENT
set rs1 = db.openRecordset("SELECT DISTINCT * FROM tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate]) = " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")
'NO PRIOR MONTH PAYMENT
if rs1.recordCount = 0 then
msgbox "Prior month rent for Tenant " & tenantID & " not entered"
'note - you might want to write these exceptions out to a log table
endif
'PRIOR MONTH PAYMENT FOUND, WRITE CURRENT MONTH TO TABLE
if rs1.recordCount > 0 then
db.execute("INSERT INTO tblTenantTransaction (TenantID, TransDate,
DebitAmt) VALUES (" & tenantID & ", #" & date() & "#, " & rs("TenantRent") &
");")
endif
'NEXT TENANT
rs.movenext
Loop
rs.close
endif
*** That should do it for you, or come close.
"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Hi all,
I sincerly thank you all in advance for your help to solve this
problem.
I have been trying to find a solution to this problem for sometime now
but have failed.
I am working on a project that requires updating data every month.
A typical examle is an apartment rental software but could be applied
in any other database.
I have a tenant table (tblTenant) with all its relevent fields.
I also have a tenant transaction table (tblTenantTransaction) with all
its relevent fields
tblTenant is one side of the table and tblTenantTransaction is many
side of the table.
tblTenant table and tblTenantTransaction table are linked by TenantID
field
In tblTenant table, among other details I have a field that stores
tenants monthly rent (this stays static). Each Tenant pays different
amount of rent every month.
In tblTenantTransaction table, among other fields I have these fields
as well
I)TransDate field (Transaction date field)
II)Debit field (Hold the amount that tenant has to pay every month)
I am looking for a way (either in SQL or VBA) to automatically enter
the date in TransDate filed (taking in the last day of the month)and
also required amount in the bedit field of tblTenantTransaction table
by looking for its respective rent amount in tblTenant table.
The code should first check to see if transaction for the previous
month has been entered or not, if it has than it should continue with
the above mentioned transacton, otherwise warn me with error msg.
This should also take care of the months that have either 28, 29, 30
or 31 days.
Once again I thank you all in advance for your help
