Formula To Text Conv
Jan 3, 2007
I currently have the following formula as the control source for an Entitlement field:
=Round([Position1]/[Stk Ratio 2]*[Stk Ratio 1],2). Occasionally Stk Ratio 2 or Stk Ratio 1 is unknown and users enter 'TBC' but obviously this returns an 'Error!' where the formula sits as its not numeric.
How could I adapt this to pick up if Stk Ratio 1 or Stk Ratio 2 contains 'TBC', overide the formula to convert to text or just somehow show 'TBC' in the Entitlement field?
Thanks in advance.
View Replies
ADVERTISEMENT
Dec 12, 2005
Hi.
My name is Amanda, and recently I’ve started using Access again for work. I haven’t touched the program in about five years, so I am rusty. In addition, it turns out that I am the only one in my department who’s ever even opened Access. I’ve tried hunting down a manual around here, and it seems to be an ever ending scavenger hunt. I also have not been able to find the answer to my question via web, FAQ, and experimentation, although, I feel it’s a fairly basic question.
So here it goes. I want to return all fields that contain part of a text. For example, I want all fields that contain the word “lux”, regardless of what else the field contains to be returned. So if a field contains “lux. cars” it will be returned in my query.
I’ve tried goggling “Microsoft Access” with “text formulas,” however, all I receive are excel formulas.
Thank you.
View 2 Replies
View Related
Dec 15, 2006
hi i have a text field in with this calculation
=([Total amount requested]+[text3])/[Initial valuation]
it displays a div/0 error which i want to remove, i know i need to put this calcualtion in an iif statement but whenever i try and make the formula it never works
=iif([Total amount requested]+[text3])/[Initial valuation],?,""
where the question mark is i am not sure what should go there, as i want the total to of the calculation there and if 0 hide 0 with the ""
this has been bugging me for a while now and i can just get my head around it, probably very simple for someone to answer
regards
chris
View 3 Replies
View Related
Jul 28, 2014
apply my situation / formula to others who had similar questions, but I get the #error output with no messages from access telling me what I did to cause this.
What I'm trying to do is create a formula that checks if two conditions are met, then applies an output. So I have a starting location [StartLocation] and [Stop2]...Both can be a small variety of locations.
Currently I have as follows:
Leg1: iif([StartLocation]="Location A" AND [Stop2]="Location B",500,0)
The formula would run longer in the end, going up to 10 stops, nesting the ifs and checking multiple locations for each stop.
Both my conditions are Text, and I want a number output depending on the location. Is it a simple error I'm looking past and missing? Or is what I'm trying not possible, I feel like it should be relatively easy. Access give me no trouble for save and running, but it outputs #error.
View 2 Replies
View Related
Nov 21, 2014
I've been trying to put a formula in a text box within a form, with not much successes.
What I want the formula to do is:- From the Date field in the form, if there no date entry's then display a Blank, if there is only one date entered, I would like it to display the time in week since that entry. But if there are multiple dates entered I want it to display the Ave time between the dates, in weeks. BUT only as far back as 12 months. Any dates further back can be ignored.
View 2 Replies
View Related
Apr 23, 2014
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
View 10 Replies
View Related
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
Nov 26, 2004
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Dec 8, 2006
Here it is:
=Count(IIf([Calendar Date]>=[First Day] and <=[Actual Last Day])
???
Maria
View 4 Replies
View Related
