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.
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.'
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
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?
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
Hi All,
I have an Excel file with multiple worksheets using VLOOKUPS betweent he sheets and various formulas within the sheets, mainly basic arithmetic and also ROUNDUP.
I am wanting to transfer all this over to Access so that it can be edited by 2 computers at the same time and I need all the formulas to go with it. What's the easiest way to do this? I'm not a very experienced Access user and basically have no idea how to put in formulas in Access.
Any help would be greatly appreciated. I am happy to email to the excel file to anyone who wants to take a look.
Thanks,
Michael
The data I have in excel is basically everything about the "site". In access this cant be stored in one table.
What's the best way to move data from excel into access across multiple tables? I was hoping I could create a query and assign the columns from excel to the columns in the query. but I cant see an option for this.
I am treying to move data from an Excel worksheet to Access. My code is below. My program does not like my "Set rs = OpenTable("GL")" line
Option Explicit
Public Const gconConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:usersc156281my documentsPLANPlan New.accdb'"
Public rsData As DAO.Recordset
Sub PostData()
'TryPostAgain:
'On Error GoTo BadPost
[Code]...
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.
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.
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
How can I import data from excel to access, i have a huge file more then 5000 entries in there....
View 1 Replies View RelatedI've build an Excel workbook that contains extensive use of VBA code for UserForms, Control Buttons, etc. and also contains several sheets that are formatted as Tables and function as databases. We've outgrown Excel's capabilities and need to move over to Access.
View 1 Replies View RelatedI need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa
Date
01012013
02012013
03012013
CustID: bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID: aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID: bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
Hi there
I have some information in a database that I need to make sure sure is correct.
There should be 11 characters (numbers, text and items such as /) within this particular field.
I know that there is a number of entries that either has more or less than the 11 characters in this particular field.
How do I create a query to look for and tell me all the entries that are less than 11 characters and all the entries that are more than 11 characters?
Thanks. Hope I explained clearly
Jools
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
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
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
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)"
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]...
=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.
Is there a way to replicate NETWORKDAYS formula in Excel to Ms Access?
View 2 Replies View Relatedi 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.
I have this file that I created from work,(.pdf). I converted it into an excel spreadsheet. I am now trying to move the data around and am having trouble, I have tried to use ACCESS to do this but I cannot figure it out.
Is there any way I can flip the rows with the columns?
Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
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