How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Steve Your Own Question

Steve
Steve, Consultant
Category: Computer
Satisfied Customers: 263
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Computer Question Here...
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: 10 months ago.
Category: Computer
Expert:  Pete replied 10 months 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 10 months 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 10 months 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 10 months 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 10 months 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 10 months ago.

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

Customer: replied 10 months 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 10 months 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.

Thank you for your patience.

- Jason

- Jason

Expert:  Steve replied 9 months 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 9 months 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 9 months 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 9 months 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: 263
Experience: Steve is a consultant in the areas of computer software and programming, information management and networking.
Steve and 3 other Computer Specialists are ready to help you

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • Kamil Anwar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    156
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
< Previous | Next >
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg Kamil Anwar's Avatar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    156
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    93
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/JO/JonTech/2011-10-25_111835_A2c.64x64.jpg Jon-Tech's Avatar

    Jon-Tech

    IS Manager

    Satisfied Customers:

    61
    20 yrs: Computers, Networking
  • http://ww2.justanswer.com/uploads/JA/jamieratliff/2012-2-8_194551_jamie.64x64.jpg jamieratliff's Avatar

    jamieratliff

    Information Systems Manager

    Satisfied Customers:

    21
    Jamie has worked in the Information Technology field for over a decade.
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERleg4q7o8/2012-3-6_203036_meja.64x64.png TheDoctor's Avatar

    TheDoctor

    Software Engineer

    Satisfied Customers:

    16
    Experienced Software Engineer
  • http://ww2.justanswer.com/uploads/FI/FisherEngineering/2012-1-18_194422_Fisher375Headshotcompressed239K.64x64.jpg Bill Fisher's Avatar

    Bill Fisher

    Computer Engineer

    Satisfied Customers:

    1
    Computer system design, networks, integration, general support.
  • http://ww2.justanswer.com/uploads/ZE/zeyank/2014-12-9_221254_zeyank.64x64.png Ryan H.'s Avatar

    Ryan H.

    Computer Support Specialist

    Satisfied Customers:

    1739
    A+ Certified Technician - 10 Years experience working with all types of computer systems.