Date Formula

May 2, 2008

I have got myself in a formula nightmare...I've written it so many different ways, that I forgot what exactly I was trying to do!

Here is my scenario:

A contract's original expiration date is mm/dd/yyyy [EndTerm]
Once it passes that date, it automatically renews for nn years [Renew]

Basing everything on the current date, I am trying to calculate that it has autorenewed x times [AutoRNo] .... whole number...no decimal places

I then need to calculate a "Next Expiration Date" which adds the number of years that have renewed to the [EndTerm]. So if a contract renewed for 3 years, and it renewed twice, the next expiration date would be 6 years after the orig expiration date [EndTerm].

View Replies

Queries :: Formula To Calculate Date Cycle

Jan 27, 2015

I need a formula to calculate date cycles.

Each person have an employment date on which a 3 year cycle is based.

So at the end of their 3 years the cycle needs to update to the next 3 year cycle.

For example
Person Employment date Cycle Start
1 01 Jan 2015 01 Jan 2015
2 01 Jan 2006 01 Jan 2012

In the case of Person 2 above the cycle would have started at 2006 - 2009 and then moved to 2009 - 2012 and then 2012 - 2015 and now 2015 - 2018

I have the following formula at the moment but need to make 100% I'm not missing something.

Also - would it be fine to run the update query each time for all persons or must it be only for the ones where the current date does not fall within the current 3 year cycle?

CycleStart = DateAdd("yyyy",Int(DateDiff("d",[EmpDate],Date())/365/3)*3,[EmpDate])

Would value your input on another way to do this as well.

View 3 Replies View Related

Queries :: Access 2010 - Date Formula Not Working?

Apr 7, 2014

date formula that I was using in access 2007 doesn't seem to be working in 2010.

The formula that I had was

Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1)
And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3 ,31).

The calculation allowed me to count holiday hours taken between 1st April and 31st March. The problem is that it was working up till 31st march but is not now showing holidays taken since 1st April.

View 2 Replies View Related

Date Difference Query Based On Excel Formula

Aug 24, 2015

i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.

in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))

how to do it in access query.

View 4 Replies View Related

Calculation (Formula) On Report Will Change Every Year - Preserve Historical Date

Oct 21, 2015

So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.

I have that working on a variable detail DB where the historical data is correct for the report.

i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)

So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50

Good - no problem

NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)

So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15

I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014

Would it require a different report per year?

View 1 Replies View Related

Convert Formula Into Formula Result

Apr 6, 2007

Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks

View 7 Replies View Related

Using Query Formula Information To Create Another Formula In A Seperate Query

Nov 20, 2006

I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!

View 2 Replies View Related

Formula Help

Apr 28, 2005

i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?

thanks in advance

View 6 Replies View Related

Formula Help

May 23, 2005

I have a nice database which is working well apart from one part

I have 2 date fields, you enter in any date in the first and i need a formaula which will generate a date 6 weeks in the future

can anybody help or suggest what to do?

View 1 Replies View Related

Formula

Apr 16, 2007

Hi

I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.

I have tried using the iif formula without success. Here is what I want to calculate.

I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.

If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....

If both fields are equal then score is 4.0
.05 score is 3.5
1.0 score is 3.0
1.5 score is 2.5
2.0 score is 2.0
2.5 score is 1.5

Can anyone help me with this formula.

View 4 Replies View Related

Sum Formula?

Apr 6, 2006

I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:

Transaction ID
Order Number
Customer ID
Title ID
Date
Status

I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?

View 12 Replies View Related

Formula...

Nov 7, 2006

I have a form that people fill in where they can choose from 4 boxes (and they can tick as many as they want - it is a yes/no box)

I have tried to write a formula for a queryto show where none of the boxes have been ticked.....this doesn't work:

=(IIf(IsNull([Q3(i)]) And iif(IsNull([Q3(ii)]) And iif (IsNull([Q3(iii)]) And iif(IsNull([Q3(iv)]),"1","0"))

???? what should I write?

View 3 Replies View Related

Formula

Nov 4, 2004

Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.

Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.

View 2 Replies View Related

Formula

Mar 21, 2008

I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.

What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.

I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.

Can anyone help - I have tried various ver. of the IIf formula but to no avail.

View 10 Replies View Related

Sum Formula

Jul 6, 2006

I have the following idea in my mind. The only problem is that I have no clue how to make this work.

[IMAGE]

Anynone know how to make this happen?

View 14 Replies View Related

Need Help With Formula...

Dec 7, 2006

Could anyone give me information on learning how to use the formulas in Access 2003? A good book, site..etc. Example I need to know the formula that takes
20061015 and puts it into 10/15/2006 format. Any help is appeciated!

View 2 Replies View Related

Formula?

Feb 1, 2007

I am trying to Automate the creation of a quote number. I plan on making a form that contains a SalesID ComboBox and a Command button that opens a new form. On Click I want the new form to open a new form and insert the created quote#

Basically the number needs to consist of:

The letter "Q-", "Date", "SalesID" (selected from the drop down menu) and "-#" (sequenced number)

this should give me something like this:

Q-13107BOB-1
Q-13107BOB-2
Etc.

How can I do this?

Could someone provide a name for what i am trying to do so I can look it up on Google?

View 1 Replies View Related

Abs Formula Help

Jun 9, 2007

Hello, I have a formula on my report in a text box that just shows the total delay for a crew by using the supervisors name. This information isnt stored anywhere its just to show a percent on the report! the formula is:

=Abs(Sum([Supervisor]=�Chad Zablackas�)*([DT REGULAR]+[DT MAINTENANCE]+[DT Reason 1]+[DT Reason 2])/Abs(Sum([Supervisor]="Chad Zablackas")*[EMPLOYEE TIME]))

I keep getting an error saying invalid syntax: Your may have entered an operand without an operator? What am I missing in my formula? Thanks!

View 6 Replies View Related

Formula In Access

Jan 10, 2006

Ok so I'm kinda new to access. What i want to happen is for there to be a textbox on a form that changes depending on what is in the other text boxes. Eg 1st box = 1, 2nd box = 2. I want the third box to equal the ammount of the first two added together (textbox1 + textbox2 = textbox3) how do i do it? Where do i put the formula. Thanks.

View 12 Replies View Related

Formula 1 Database

Oct 23, 2006

I am a big Formula 1 fan and I am thinking about creating a F1 database. However, I can't decide which is the best way to store the data. I don't know if one race should be a one field or if one driver for one season should be one field. Obviously I want to store it in the best way possible so that I can easily pull out relevant data in queries. My knowledge of Access is relatively limited so any help you could give me would be appreciated.

View 2 Replies View Related

Formula In A Field

Apr 27, 2005

I've got a table set up with sales info and all other kinds of data. How do I get a formula to work? Here are the inputs I have: Total sales (by outside company), total purchases (from my company). I want to know what percent of their total sales are made up of the poducts they purchase from me and have that field update automatically. Its a simple formula really, I just don't know how to enter it properly. Thanks.

View 2 Replies View Related

Formula In Field

May 31, 2007

Hey guys hope someone can help me out here...

i need to have a currency field in one of my tables, the user will enter the price in euros, and the output will be in pounds... therefor i need the following formula to work:

[enterdprice]*[current exchangerate] = price in pounds

i would very much appreciate suggestions/help

View 3 Replies View Related

Parameter On An Formula

Mar 18, 2006

Hi, i have created a query where for 2 fields i have used a formula, and for the 1 field i need to do a parameter on it, but im having a bit of trouble with it, instead of 1 dialogue box appearing asking for the parameter value, i get the 1 i want and 1 for the other formula i have created. any ideas how to solve this ??
thnx chia

View 1 Replies View Related

What's Wrong With This Formula???

Dec 8, 2006

Here it is:

=Count(IIf([Calendar Date]>=[First Day] and <=[Actual Last Day])

???
Maria

View 4 Replies View Related

Error In Formula

Jan 19, 2007

Hi,

I've currently got several formulas like the one below in my query for which when dividing by 0 I get #error (which I was aware occurred). However, I though my formula would return 0 in such a case (lilke Excel would), but it hasn't

The fields which it is using to calulate the formula have number values, for which if no score is entered then the default value is 0. I would rather not leave this blank and retain the zero value (I haven't looked in to nz function any further because this if for blank fields?) so any help is gratefully appreciated.

The forumla returning #error is:

UKAdj%: IIf(IsError([UKAdjTot]/[UKTotal]),0,([UKAdjTot]/[UKTotal]))

Cheers,
Iain

View 6 Replies View Related

Query Formula

Mar 6, 2007

I am new to the access database world- I have a database in which I have projects that each have a priority- to get the priority # there are about 10 categories that need to total up to give me that priority #

Every formula I have tried, has given me an error message of some sort! PLEASE HELP, and please let me know if I am not explaining this enough!:confused:

View 9 Replies View Related