To Member,
Thanks for your response. Could you pls. let me know where should I add these codes?
Dict("NoInterestDate") = CDate(DateAdd("m", TotalMonthsNoInterest, Date()))
Dict("ContractEndDate") = CDate(DateAdd("yyyy", TotalYearsContract, Date()))
Dict("TotalDaysLeft") = DateDiff("d",Date(), LastDayOfMonth")
To Admin,
Here's the one that I have in my Access Query.
Field1 is DaysLeft
Field2 is Expiry Date
DaysLeft: DateDiff("d",Date(),Users![Expiry Date])
If this will work, pls. let me know where will I put this if I will not bring the query over to ASP. What I have done instead, I just pointed to the table with Field1 and Field2 and I would like to have that formula/code on Field1.
Thanks..
You could put this code in the function 'Before Record Added' and Function 'Before Record Updated'.
Have a look in part of my code:
strSQLExists = "SELECT FROM tbl_Contract_Settings WHERE NameOfContract = '" & Dict("ContractName") & "'"
set rsExists = CreateObject("ADODB.Recordset")
rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then
'Response.write "Record FOUND" & "<Br>"
'Name of the Contract
NC = rsExists("NameOfContract")
'TotalYearsContract
TotalYearsContract = rsExists("ContractLength")
'Fixed or Growth
FG = rsExists("FixedOrGrowth")
'Costprice Seed Unit
CostPrice = rsExists("ValueSeedUnitCostPrice")
'Sell value Seed Unit
ValueSeedUnitPrice = rsExists("ValueSeedUnit")
'Contract Currency
Contract_Currency = rsExists("ContractCurrency")
'Total months NO interest
TotalMonthsNoInterest = rsExists("MonthsNoInterest")
'Total Months Double Interest
TotalMonthsDoubleInterest = rsExists("MonthsDoubleInterest")
'Interest No 1
Int1 = rsExists("percentage_1_100")
'Interest No 2
Int2 = rsExists("percentage_101_250")
'Interest No 3
Int3 = rsExists("percentage_251_500")
'Interest No 4
Int4 = rsExists("percentage_501_1000")
'Interest No 5
Int5 = rsExists("percentage_1001_more")
'Yield No 1
Yield1 = rsExists("yield_1_100")
'Yield No 2
Yield2 = rsExists("yield_101_250")
'Yield No 3
Yield3 = rsExists("yield_251_500")
'Yield No 4
Yield4 = rsExists("yield_501_1000")
'Yield No 5
Yield5 = rsExists("yield_1001_more")
'Create Part of Contract Number
If UCase(FG) = "FIXED" then
Part_Contract_Number = "FD"
Else
Part_Contract_Number = "GT"
End If
'Create Contract Number
NEWContractNumber = Part_Contract_Number & Mid(Now(),7,4) & "-" & Mid(Now(),4,2) & Mid(Now(),1,2) & Mid(Now(),12,2) & Mid(Now(),15,2) & Mid(Now(),18,2)
else
Response.write "Record NOT Found" & "<Br>"
end if
rsExists.Close : set rsExists = Nothing
'Calculate Last day of the month
LastDayOfMonth = DateAdd("d",-1,DateAdd("m",+1,replace(Date(), DatePart("d",Date()), "01")))
Dict("ContractNumber") = NEWContractNumber
Dict("ContractType") = FG
Dict("CurrencyType") = Contract_Currency
Dict("SeedUnitPrice") = ValueSeedUnitPrice
Dict("TotalSeedUnits") = Dict("TotalSeedUnits")
Dict("StartCapital") = ValueSeedUnitPrice Dict("TotalSeedUnits")
Dict("StartDate") = Date
Dict("NoInterestDate") = CDate(DateAdd("m", TotalMonthsNoInterest, Date()))
Dict("DoubleInterestDate") = CDate(DateAdd("m", TotalMonthsDoubleInterest, Date()))
Dict("ContractEndDate") = CDate(DateAdd("yyyy", TotalYearsContract, Date()))
Dict("Active")=1
Dict("CloseDate")=""
Dict("TotalDaysLeft") = CDate(DateDiff("d",Date(), LastDayOfMonth"))
'** Check if DataBase is LOCKED ****
strSQLExists = "SELECT * FROM DB_Status WHERE DataBaseStatus = 'CLOSED'"
set rsExists = CreateObject("ADODB.Recordset")
rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then
Response.Write "<script>alert('MESSAGE: The DataBase is locked.' + '\n' + '\n' + 'Updating Interest Tables, try again in 5 minutes.')</script>"
BeforeAdd = FALSE
ELSE
BeforeAdd = True
end if
rsExists.Close : set rsExists = Nothing
!! Please note that dict("Field_Name") might need changing to values("Field_Name") in version 6.x
