By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,384 Members | 845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,384 IT Pros & Developers. It's quick & easy.

Automatically Updating data end of month

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
DFS
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

Nov 12 '05 #2

P: n/a
Another way to get the last date in a month for any given date:

Private Function LastDateInMonth(TargetDate As Date)
Dim DD As Date
DD = DateAdd("m", 1, TargetDate)
DD = DatePart("m", DD) & "/1/" & DatePart("yyyy", DD)
LastDateInMonth = DateAdd("d", -1, DD)
End Function

"DFS" <no******@nospam.com> wrote in message
news:vt************@corp.supernews.com...
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


Nov 12 '05 #3

P: n/a
You can get the last date in the month for any given date using:

DateSerial(Year(InputDate), Month(InputDate) + 1, 0)

For example:
?DateSerial(Year(Date()), Month(Date()) + 1, 0)
31/12/2003

--
HTH
Van T. Dinh


"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Mp**********************@newssvr28.news.prodi gy.com...
Another way to get the last date in a month for any given date:

Private Function LastDateInMonth(TargetDate As Date)
Dim DD As Date
DD = DateAdd("m", 1, TargetDate)
DD = DatePart("m", DD) & "/1/" & DatePart("yyyy", DD)
LastDateInMonth = DateAdd("d", -1, DD)
End Function


Nov 12 '05 #4

P: n/a
"Van T. Dinh" <Va***********@PlseUseNewsGroup.bigpond.com> wrote in message
news:fN******************@news-server.bigpond.net.au...
You can get the last date in the month for any given date using:

DateSerial(Year(InputDate), Month(InputDate) + 1, 0)

For example:
?DateSerial(Year(Date()), Month(Date()) + 1, 0)
31/12/2003

--
HTH
Van T. Dinh
Oooh. Even better. Thanks!

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Mp**********************@newssvr28.news.prodi gy.com...
Another way to get the last date in a month for any given date:

Private Function LastDateInMonth(TargetDate As Date)
Dim DD As Date
DD = DateAdd("m", 1, TargetDate)
DD = DatePart("m", DD) & "/1/" & DatePart("yyyy", DD)
LastDateInMonth = DateAdd("d", -1, DD)
End Function


Nov 12 '05 #5

P: n/a
DFS
Doesn't the FAQ state

"Don't show up another poster by providing a one-line fix that replaces 20
lines of the original poster's spaghetti code."?
"Van T. Dinh" <Va***********@PlseUseNewsGroup.bigpond.com> wrote in message
news:fN******************@news-server.bigpond.net.au...
You can get the last date in the month for any given date using:

DateSerial(Year(InputDate), Month(InputDate) + 1, 0)

For example:
?DateSerial(Year(Date()), Month(Date()) + 1, 0)
31/12/2003

--
HTH
Van T. Dinh


"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Mp**********************@newssvr28.news.prodi gy.com...
Another way to get the last date in a month for any given date:

Private Function LastDateInMonth(TargetDate As Date)
Dim DD As Date
DD = DateAdd("m", 1, TargetDate)
DD = DatePart("m", DD) & "/1/" & DatePart("yyyy", DD)
LastDateInMonth = DateAdd("d", -1, DD)
End Function


Nov 12 '05 #6

P: n/a

"DFS" <no******@nospam.com> wrote in message
news:vt************@corp.supernews.com...
Doesn't the FAQ state

"Don't show up another poster by providing a one-line fix that replaces 20
lines of the original poster's spaghetti code."?
"Van T. Dinh" <Va***********@PlseUseNewsGroup.bigpond.com> wrote in message news:fN******************@news-server.bigpond.net.au...
You can get the last date in the month for any given date using:

DateSerial(Year(InputDate), Month(InputDate) + 1, 0)

For example:
?DateSerial(Year(Date()), Month(Date()) + 1, 0)
31/12/2003

--
HTH
Van T. Dinh


"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Mp**********************@newssvr28.news.prodi gy.com...
Another way to get the last date in a month for any given date:

Private Function LastDateInMonth(TargetDate As Date)
Dim DD As Date
DD = DateAdd("m", 1, TargetDate)
DD = DatePart("m", DD) & "/1/" & DatePart("yyyy", DD)
LastDateInMonth = DateAdd("d", -1, DD)
End Function




It may do - but I find it useful to see ALL the ways that people use to
code. I can then extend my own knowledge of code this way - I can often
understand the verboser code and link it to the terse code. I would not
necessarily be able to code tersely by myself and am grateful for the
'learning curve' shortener!! :>]}

Nick
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.