Update Query w/ DateSerial (1 Viewer)

I'm trying to design a database for my company that calculates interest paid on a loan based on the number of days there is left in that month.
For example, if a loan is taken out on Jan 27, they have to pay 5 days of interest for that month (including the day the loan is taken out).

So i'm trying to write an update query that will automatically update a field based on the current month's number of days - the day the loan was taken out + 1 (to include that day).

What I have is a date serial update that doesnt seem to want to run:

UPDATE tbl_Loan SET tbl_Loan.DaysInMonth = DateSerial(Year(Now()),Month(Now()), Day(Now())-[forms]![frm_Update].[textbox1]+1

The value to update in the field to be just a number (for the number of days of interest to pay). Any help is extremely appreciated. Thank you,

Brendan

I do not understand your logic, I assume that
[forms]![frm_Update].[textbox1]
is the date that the loan was taken out, that is the only date required, the loan may be taken out on 31st of Jan and the data entered on 1st of Feb, you do not want to calculate the number of days in feb.

Datediff("d",[forms]![frm_Update].[textbox1],dateserial(year([forms]![frm_Update].[textbox1]),month([forms]![frm_Update].[textbox1])+1,1))

I believe that the above will do your calculation. The dateserial sets the date as the 1st of the following month to allow for the +1

Brian

Yes, exactly. [forms]![frm_Update].[textbox1] is where the date that the loan was taken out is entered and I understand why I don't want to use DateSerial(Now()) however it is saying there is an syntax error in update statement.


UPDATE tbl_Loan SET tbl_Loan.DaysInMonth = Datediff("d",[forms]![frm_Update].[textbox1],dateserial(year([forms]![frm_Update].[textbox1]),month([forms]![frm_Update].[textbox1])+1,1)


What am I doing wrong?

Ok it needs another closing ) at the end.
That's the trouble with untested air code, the recipient needs to check it carefully.

Brian

wow I'm an idiot.
Brian thank you it works perfectly

No problem, simple syntax errors are the easiest to make and the most difficult to spot as we all think we are perfect.

brian

Brian- If I might pick your brain one more time please…

On my form I have the fields CustomerID, LoanBeginDate, NoOfMonths (unbound, just used to calc LoanEndDate), PrincipleAmt, & InterestRate so when filled in I click the update button that runs VBA RunSQL code to automatically update the fields LoanEndDate, DailyCharge, NoOfDays, and TotalCharge.

The VBA RunSQL code says “WHERE (((tbl_Loan.CustomerID)=forms!UpdateTblForm.customers1)” so only that specific customer gets updated.

My problem is that the customer is charged on a monthly basis. So right now one record in the database is updated with this information for the total number of days but I need it broken down into months.
For example: if CustomerA has LoanX for 4 months beginning Jan 20 and ending May 20 then I would need 5 new records in the DB, one each for Jan, Feb, Mar, Apr & May. The CustomerID, PrincipleAmt, InterestRate & DailyCharge fields would all remain the same, and the LoanBeginDate, LoanEndDate, NoOfDays, and TotalCharge (would rename MoCharge) fields would change.

So basically I need the LoanBeginDate & LoanEndDate fields to look like this on each new record:
LoanBeginDate LoanEndDate
1/20/2009 1/31/2009
2/1/2009 2/28/2009
3/1/2009 3/31/2009
4/1/2009 4/30/2009
5/1/2009 5/20/2009

And from this the NoOfDays & MoCharge fields will update accordingly.

I wish I could say I had an idea how to begin this but honestly I’m lost. I’ve tried looking online but feel like I’m getting nowhere. Any helpful insight would be much appreciated. Thank you,

Brendan

If I understand you you want to convert one record to a number of almost identical records but with the LoanBeginDate(LBD) and LoanEndDate(LED) on a monthly basis.

Edit realised I haven't allowed for year ends

The logic is
Dim LngMTH as Long
A special case of Month(LBD) =Month(LED) then the current values apply in just one record.

1st record LBD as value
LED=Dateserial(Year(LBD),Month(LBD)+1,0) ' This gives the last day of the month.
exit or goto
LngMTH=Month(LBD)
Do until LngMTH=Month(LED)+1
LBD=Dateserial(Year(LBD),LngMTH,1)
LED=Dateserial(Year(LBD),LngMTH+1,0)
LngMTH=LngMTH+1
Loop
'lastrecord
LBD=Dateserial(Year(LBD),LngMTH,1)
LED as given value

The above is logic not program code, and reading through I realise I haven't allowed for loans crossing end oyear incuding my 'special case' as the loan my be from July 2009 to July 2010. But I am sure that you can yake this forward, else post as a new thread so that sharper and more experienced users than I can help.

Brian