I am trying to convert an excel formula into the expression builder on my form.
the excel formula is this: IF(AND(E4>=2.65,F4<1.85),"Poor",IF(AND(E4<2.65,F4< 1.85),"Unacceptable",IF(AND(E4>=2.65,F4>=1.85,F4<2 .65),"Generally",IF(AND(E4<2.65,E4>=1.85,F4<2.65,F 4>=1.85),"Poor",IF(AND(E4<1.85,F4<2.65,F4>=1.85)," Unacceptable",IF(AND(E4>=3.45,F4<3.45,F4>=2.65),"H igh",IF(AND(E4>=2.65,E4<3.45,F4<3.45,F4>=2.65),
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
I have 5 fields(First,Second,Third,Sum,Sum1). The Sum field is the sum of the first, second and third. In excel I am using the following formula/expression to calculate the Sum1 field =IF(E1<0,E1+10,E1+0 - and it brings back single digit. e.g. First=3, Second=5, Third=8, Sum=16, Sum=6 Can I do this in an Access query, if so how?
Really need help on this one, here is an excel expression:
X Multipliers $ ValueMultiplier
is less than5then *3.8 is less than50*3.2 is less than100*2.8 is less than200*2.4 is less than500*2 is greater than500*1.8
so if price is less then 5 then multiply by 3.8 and so on. In excel it's in a separate worksheet, and other worksheets linked to it and adjust their Sale Price from the Cost column. Someone else created this spreadsheet, and i couldn't find a multiple discount help in access. I tried creating a query =([PriceBook_OLD].[Cost]<=5)*3.8 Or ([PriceBook_OLD].[Cost]<=50)*3.2 Or ([PriceBook_OLD].[Cost]<=100)*2.8 Or ([PriceBook_OLD].[Cost]<=200)*2.4 Or ([PriceBook_OLD].[Cost]<=500)*2 Or ([PriceBook_OLD].[Cost]>500)*1.8 but it doesn't work. Please help!!!! Thank you very much
I have above table in access. I want to use this table in query to find value of any product using a formula (Value A*X)+Value B. The product and X query picks up from another table. The same can be done easily in Excel using Index, Match formula but how in Access.
I am calculating the check digit for a 13 digit number in Excel and I need to be able to do it in Access.
My Excel formula; =1&E1&MOD(-SUM(MID(1&E1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14}, 1)*{3;1}),10) -- cell E1 holds the base 12 digit number. My attempt to convert to Access; =1 & [txtBase] & [-SUM(MID("1" & [txtBase] &0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}) mod 10] -- txtBase holds the base 12 digit number
I have files that have extension of TSV which are text files but viewable in exel. I figured out a way for the user to click on a button in Access which does the following
1. Run Macro in Excel: The macro prompts the user to select the TSV file. After selection, macro opens the employee.tsv file in the excel (with excel being invisible) and saves it as employee.xls
Code: Sub SaveTSVtoXLS() Dim myPath As String Dim myString As Variant Application.DisplayAlerts = False With Application.FileDialog(msoFileDialogOpen)
[Code] .....
2. Imports the Excel file (employee.xls) into two tables: tblEmployee and tblDepartment using the following codes.
This is my first try at asking for help on the net. I hope I do it correctly.
I have an Excel spreadsheet that has three columns. One column has numbers and two have text. I have about 15,000 entries (records). The number column is the week of the year. It contains the WEEKNUM formula Sun-Sat (1 through 52 based on the date) One text column contains event descriptions. One text column contains names.
What I want is a count of each description for each of the 52 weeks. (i.e. week 36 there were 120 "traffic accidents", 300 "traffic citations", 13 "assaults", 1 "armed "robbery", etc. etc.. I have been surfing and tried every formula that seems to say it will do this but nothing works.
I designed a special spreadsheet that looks at the "master" spreadsheet for the data and I had hoped would keep a running total on all descriptions for each week. I am self taught in Excel and need a no brainer. And yes, I now know excel is not a data base program.
Hi, Sorry to be a pain but I have not used this in a while and need some assistance - eg
I need to create an If And statement to formulate in bonus paid (Cell B8) - if number of new customers greater than 5 (cell B2), and the sales value (cell B5) is greater than 100,000, then return the message, "bonus paid" otherwise return "no bonus"
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
I have a report exporting to excel using late binding techniques. When exported into excel i have numbers for 1,2,3,4 tblPreSiteSurveys. PreSiteSurvey Stop TheClockReason entitie and I am trying to either change the numbers here casting from int to string
1 = a 2 = b
looping through the record set, Or i have a blank field in my SQL for the column "P" and adding the formular to that column, but it only goes into the first row of the record set, which is a expandable table.
On Error GoTo Command29_Click_Err 'Utilergy Master Update report Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Dim UserDate As Date
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)))
I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:
In an excel sheet, A1: Smith, John A2: 111 Pine St. A3: San Diego, CA A4: (555) 128-549 A5: Jones, Sue A6: 222 Oak Ln. A7: New York, NY A8: (555) 238-1845 A9: Anderson, Tom A10: 333 Cherry Ave. A11: Chicago, IL A12: (555) 581-4914
3. Fill this formula across to column F, and then down to row 3.
4.Note that the data is now displayed in cells C1 through F3 as follows: Smith, John 111 Pine St. San Diego, CA (555) 128-549 Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845 Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But, My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.
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.
I was wondering if I could be able to use the average formula on calls in forms. I have begin date, end date, agent name, and I would want to take the difference between those dates and returns the average of calls. I was successfully able to return the difference dates, but I can't get the average calls to work. Here is what I use for the average formula:
=IIf([End Date]>[Date],DateDiff("d",[Date],[End Date])=Sum([Average wrap]),0). I put this formula inside TEXTBOX inside the form.
Hello, I am completely new to Access, so thanks to anyone who does not think my questions are dumb :) Ok, say for example I have a table that has my income information and my tax rate and I want to compute the income tax I need to pay by simply modifying total income with tax rate, how should I do it? there does not seem to be a function like formular bar in Excel in Access.
I have a form that has 3 fields each representing a different table. I want the sum of 2 of the fields to update the third field. The third table has an existing value in it, but I need it to change when the formula is executed. I have a formula embedded in the form and can make the formula work, but it doesn't update my table. How can I make it do this? :confused:
I put the formula in the Text box field in the control source in the property's tool box and i don't know if it will save that calculation to the table. I was wondering if i can do VBA code to save the the calculation in the table.I have a field called CA Weigt and i put the formula in there as =[Weight] * 0.4536 and it works. How would i put it in a vba code.
