• 100% Satisfaction Guarantee
Steve, Consultant
Category: Computer
Satisfied Customers: 289
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Steve is online now

# I need to know if and then how an excel formula can be

### Resolved Question:

HiI need to know if and then how an excel formula can be translated to Access2013. I need it as an expression in a calculated column on a table.IF(A1<=B1,11-sum(a1-b1,b1-a1)I know most how to change to Access but the minusing the outcome of a separate sum (so an equation in an equation) is where i have difficulty.I have tried:IIF([field1]<=[field2],11-([field2]-[field1]),[field2]-[field1])It works sometimes but appears anything 10 or above in one of the fields, then it does strange things.If the above is possible, then i need to be shown how and also if I can do two sets of separate IIFs in one expression. Meaning two separate answers appear in the same cell:IIF() + '.' + IIF()so if answer 1 was 20 and answer 2 was 4, cell would read 20.4I really don't know if either can be done as an expression on the table (not as a query).Thanks
Submitted: 1 year ago.
Category: Computer
Expert:  Pete replied 1 year ago.

Hi there,

The easiest way to do this is use the Expression Builder tool within Access and select the appropriate fields and it will generate the formula for you. Without seeing the data I'm not sure what exactly the scenario is or what you are trying to calculate.

Depending on the scenario, you will likely end up with more round brackets around the formula to make it work correctly, e.g.

=Iif(([field1]<=[field2]),(11-([field2]-[field1])),([field2]-[field1]))

Customer: replied 1 year ago.
HiThe brackets do not seem to make any difference. Sometimes either the true or false equation bring back the correct answers, other times it does not.... seems to have an issue when one of the referenced fields is double digits.What I needed to know is can you replicate 'sum' from excel, which you seem to say you can with brackets, although it is not working.The scenario is this.I have a year of marriage column and a month of marriage column, I then have 'end marriage' year and month in columns. I need to work out duration of marriage so it can sit in its own column on the table. This same equation would be used for age at death etc.The big issue is that this is for a character profile table, so it is fictitious and to make it worse, I am using an 11 month calendar not 12.So i need to do this: If marry end month is lower than marry begin (so not a full year(11 months)) I need to take the difference from the two and minus from 11 so as to find how close to a year we are.So if married in month 5 and ended in month 3, 5-3 = 2, then 11-2 = 9. so we are 9 months in.If married in month 6 and end month is 8, we are simply (8-6) 2 months into the next year of marriage.Ideally I would include a year calculation in the same column, so if month end is less than month begin, 'year end' - 'year begin' - 1 (as not a full year then separate by . and do the month equation, e.g. 24.3
125-100 -1 '.' 11-sum(8-1) = 24.4If moth end => month begin: 'Year end' - 'year begin' + '.' + 'month end' - 'month begin', e.g. 25.5125-100 '.' 7-2 = 25.5I know it sounds bizarre but I got it to work on Excel.If year and month duration have to be separate columns, so be it, I just need the month calculation to work.Thanks
Expert:  Pete replied 1 year ago.

Ok, I've been struggling to get my head around what is required here..!

Let me check if another expert can come up with a working formula for you...

Expert:  Jason Jones replied 1 year ago.

Hello,

I am the person that will be helping you today. May I take a look at the document or a test version of the document?

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
I will be standing by, looking forward to your response.

Thank you,
Jason

Customer: replied 1 year ago.
HiI have uploaded the file, file ID 692892.It is very strange, I went to set you up a test file and imported the original source from Excel. However when I did it, the formula worked.... So i copied the expression into the existing file and it failed.... I do not know what the difference is and why it works in one and not the other.I have now saved both versions under the uploaded file. Marriage is correct Marriage1 has the errors. they are the same records and in the same order, i just deleted the names (although I'm sure they wouldn't interest you).So it appears the original formula I did does work, but what is different about the original Access file that I get different and even negative numbers?the column is Duration Month.Also, on Marriage1 there is a Duration Year, if we resolve the issue, can I have two expressions in 1 column and separate the two sets of numbers with'.' (10.3) or do I have to create another column to merge them?ThanksRob
Expert:  Jason Jones replied 1 year ago.

Which version of Access are you using? I attempted to open the file, but cannot.

Customer: replied 1 year ago.
2013, but I have just upgraded from 2007. Even though this was crerated today, it says in properties it is 2007 .accdbI just pressed 'change' and now it appears as a 2013 file on my desktop. I have loaded it up again in case this makes a difference, ID 326576Thanks
Expert:  Jason Jones replied 1 year ago.

Hello,

It still is not opening for me. I may be having an issue with my installation of Access.

For this reason, I am opting out of the question and allowing another expert the chance to help.

The next expert will see all that is on this page, so there will be no need to repeat anything.

When another expert picks this up, you will be notified by email.

- Jason

- Jason

Expert:  Steve replied 1 year ago.

Hi, my name is***** original question was whether you could store a calculation in an Access field, and the answer is "not really." You can create a Macro and have the macro trigger when you open a form or a report to populate data based on a formula, but you can't store a formula in an Access field like you do in Excel. Of course, you know you can create calculations through queries, but you said you didn't want to do a query.

Does this make sense? What else can I tell you that would answer your question?

Customer: replied 1 year ago.
Ok, I just do not understand why it works on some rows and not others. Then in a new doc with exactly same expression and data it works on all rows..... I'm Access just does not 'guarantee' this kind of 'coding' and anything can disrupt/destabilise it, meaning no guarantee of data integrity. If a a better explanation, then please let me know.Yes, I just wanted the values resulting from the expression to be a field on the table, rather than run a query to come to that answer. It just seemed to me that it is a database so offshoots created from an equation of existing fields shouldn't need to be manually inputted. The ultimate goal is to create a form view, hence why seems simpler to have the needed fields pre-populated.I know Excel VBA, but not how to apply that to Access, so I'll need to get a book to translate my Excel VBA knowledge.Thanks for trying.Rob
Expert:  Steve replied 1 year ago.

Hi, Rob. Your Excel VBA knowledge is completely transferable to Access. You can code the macro I mentioned in VBA, and have it triggered when a form or report is opened to generate the results of your formulas. You just can't store them as fields in the database.

A more advanced SQL database, such as SQL Server, has something called Stored Procedures that essentially do what you're trying to do, but, especially if you already know VBA, you can code some macros and accomplish it in Access without any problem.

Expert:  Steve replied 1 year ago.

BTW, Rob, if this answered your question, can you please accept the answer and rate me approrpriately? I don't get paid anything for helping you unless you accept the answer and rate me at least 3 stars. Thanks!

Steve, Consultant
Category: Computer
Satisfied Customers: 289
Experience: Steve is a consultant in the areas of computer software and programming, information management and networking.