1. Registered User
Join Date
Nov 2006
Posts
43

Hello,

I'm trying the to calculate the projected amount. I'm needing help putting this formula together using 2013 MS Access

for starters I need to be able to
Calculate the number of days to date in a month
Calculate the number of days left in the month.

I tried putting together some calculations below if that helps explain what I’m trying to do

FEES_COLLECTED
\$11433.44
\$476.39
\$3,334.75
\$14,768.19

in excel the formula should look like the following
\$11433.44
=E33/24
=E282*7
=E33+E283

Projected amount: ([\$11433.44]/ ([\$476.39]*[ \$3,334.75] +[\$14,768.19]
Projected Amount: ([\$11433.44]]/[E33/24]
Projected Amount: ([field1]/[feild2]*[field 3],[field 4]+[field 5]

FEES_COLLECTED
\$11433.44
\$476.39
\$3,334.75
\$14,768.19

\$11433.44
\$476.39
\$3,334.75
\$14,768.19

Join Date
Nov 2004
Location
out on a limb
Posts
13,692

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
What I would do is Create some Functions

DateDiff()
DateSerial()
Year()
Month()
Day()

Code:
```'******************************
Function st1month(Tdate)
'******************************
st1month = DateSerial(Year(Tdate), Month(Tdate), 1)
End Function
'******************************
Function LastDay(Tdate)
'******************************
End Function
'******************************```
Calculate the number of days to date in a month = day(date())

Calculate the number of days left in the month.

Code:
```'******************************
Function DaysLeft(ThisDate)
'******************************
Lastd = LastDay(ThisDate) 'get the Lastday of said date
DaysLeft = dateDiff("d",ThisDate,Lastd) 'what the diff of the to dates
End Function```
just put code into module

then in a Query you can call the

Code:
```BLA:DaysLeft([feildname])
or
BLA:DaysLeft(Date())```
Projected Amount: ([field1]/[feild2]*[field 3],[field 4]+[field 5]

just watch your () make shour they in the right place

also NO SPACES IN THE FEILDNAME put the SPACES in at report time ONLY

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•