Convert Excel Formula For Use In Access
Feb 14, 2008
I have an excel worksheet with the following formula
=IF(b40<50000,b40*0%,IF(b40<100000,b40*2.5%,b40*5%))
and I would like to convert it for use in access.
I came up with the following:
=iif([TOTAL]<50000,[TOTAL]*0%,IIF([TOTAL]<100000,[TOTAL]*2.5%,[TOTAL]*5%))
I get the following error: "The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it with quotation marks."
I am probably missing something very simple here, but I have been unable to come up with a solution.
Any help would be greatly appreciated.
Regards,
Michael
View Replies
ADVERTISEMENT
Mar 22, 2013
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),
[Code] ....
In the expression builder I put this:
= IF(AND( [Objective Total] >=2.65, [KSF Totals] <1.85),"Poor",IF(AND( [Objective Total] <2.65, [KSF Totals] <1.85),"Unacceptable",IF(AND( [Objective Total] >=2.65, [KSF Totals] >=1.85, [KSF Totals] <2.65),"Generally",IF(AND( [Objective Total] <2.65, [Objective Total] >=1.85, [KSF Totals] <2.65, [KSF Totals] >=1.85),"Poor",
[Code] ....
It gives me an error 'expression you entered contains invalid syntax, you may have entered a comma without a preceding value.'
View 2 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
Aug 8, 2005
In my worksheet there are 3 columns; 1. Date Completed (J), 2. New Review Date(K) and 3. Status(L)
I'm currently migrating from excel to access, the finishing tounces are needed, like the status.
The following excel function I want to convert to access
=IF(K1="","Outstanding",IF(K1<TODAY(),"Outstanding","Complete"))
(K1= Next Review Date)
I did do a search, but I couldn't really find anything.
How do I convert this please?
View 1 Replies
View Related
Jan 11, 2006
Hi does any body no the access syntax to create this excel formula
=POWER(SQRT(L5/10)*47.1,2)/3769.9
Into access
Thanks
John
View 2 Replies
View Related
Jun 1, 2005
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?
Thanks in advance for any help.
View 1 Replies
View Related
Aug 9, 2005
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
V.
View 14 Replies
View Related
Sep 30, 2012
PRODUCT
VALUE A
VALUE B
WW
10
100
XX
20
200
YY
30
300
ZZ
40
400
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.
View 1 Replies
View Related
Nov 5, 2014
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
View 7 Replies
View Related
Aug 7, 2015
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.
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryDepartment", selectFile, True - 1, "A1:C2"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryEmployee", selectFile, True - 1, "A1:AE2"
Everything is working flawless except that the user has to select the file three times:
1 time for the tsv
2 times for the xls file
Is there a way that the user can select the file only once (tsv file) or at least only twice one of the tsv file and the other for the xls file?
View 8 Replies
View Related
Sep 7, 2004
Need help with basic formula.
In column A, I have a list of dates going down the page.
In column B, I have the number of hours worked on each day.
I need a formula that will give me a total (sum) from column B for only the last 30 days as well as 90 and 365 days.
Thanks
View 1 Replies
View Related
Nov 22, 2004
Greetings,
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.
Jim the data guy
View 1 Replies
View Related
Mar 28, 2007
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"
Can any one help?
Thankyou
View 1 Replies
View Related
Apr 5, 2013
Access 2003
Excel 2003
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
Code:
MyCalc::"IF(T2="","0",TODAY()-T2)"
View 1 Replies
View Related
Sep 25, 2014
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
[code]...
View 12 Replies
View Related
Jan 7, 2015
=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")
Is it possible to replicate the above formula from Excel into criteria in an Access Query.
Assume the cell reference $A2 is a text field which has customized date info in it but is not a recognized date format.
View 7 Replies
View Related
Jul 29, 2015
Is there a way to replicate NETWORKDAYS formula in Excel to Ms Access?
View 2 Replies
View Related
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
Nov 18, 2004
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
2. Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
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.
I really appreciate it. Thanks.
View 1 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
Jan 28, 2012
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.
View 2 Replies
View Related
Apr 24, 2007
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.
Thanks a lot!
Regards,
Anyi
View 5 Replies
View Related
Jan 22, 2005
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:
View 1 Replies
View Related
Apr 21, 2015
My Dataset:
ColunmA ColunmB Value
a b 1
b d 2
a q 3
c a 5
b e 2
d e 10
f q 1
q m 8
. . .
. . .
. . .
In Excel, I would do sumif( ColunmB,a2,Value), which gives me a result:
ColunmA ColunmB Value Result
a b 1 15
b d 2 2
a q 3 15
c a 5 8
b e 2 2
d a 10 2
f b 1 0
q c 8 0
. . . .
. . . .
. . . .
So based on each string in ColunmA, it would search all ColunmB and return an aggregated value.How can i get the same result by Access/SQL query?
View 3 Replies
View Related
Mar 14, 2013
How to apply the formula, IF (COUNTIF (O:O;O3)> 1; K3/SUMIF (O: O;O3; K: K), 1)from an Excel spreadsheet into an Access query.
View 2 Replies
View Related
Jan 21, 2015
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.
View 7 Replies
View Related
