Calculate a field in Access, using a formula from a text string

Does anyone know of a way to calculate a field in an Access query using 
different formulas entered in a database table, as a text string?  I have 
gotten around this before by splitting up these equations into their 
different variables and storing the values into a table.  However, that only 
works if the equation format stays the same.  I no longer have formulas that 
closely resemble each other in format and differ only in values.

Any ideas?
0
Utf
12/3/2007 5:46:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1447 Views

Similar Articles

[PageSpeed] 48

Could you provide an example or two for clarification?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"ALF" <ALF@discussions.microsoft.com> wrote in message 
news:4C5297BA-6918-407E-A7DD-8E84E825E47F@microsoft.com...
> Does anyone know of a way to calculate a field in an Access query using
> different formulas entered in a database table, as a text string?  I have
> gotten around this before by splitting up these equations into their
> different variables and storing the values into a table.  However, that 
> only
> works if the equation format stays the same.  I no longer have formulas 
> that
> closely resemble each other in format and differ only in values.
>
> Any ideas? 


0
Jeff
12/3/2007 6:20:59 PM
You might want to take a look at the Eval( ) function.  If you pass it a 
string ("3 * 6") it will return 18.  If you have variables defined in the 
string, you will have to use the Replace function first.

If  strExpr = "intA * intB
and intA = 3 and intB = 6
Then ?eval(replace(replace(strExpr, "intA", val(intA")), "intB", val(intB)))
will print 18

The down side of this is that you would have to know which variables are in 
each equation, or if you had a standard list of variables, you could just 
run it through a function that checks strExpr for any of the variables and 
replaces them with their appropriate values.

HTH
Dale

"ALF" <ALF@discussions.microsoft.com> wrote in message 
news:4C5297BA-6918-407E-A7DD-8E84E825E47F@microsoft.com...
> Does anyone know of a way to calculate a field in an Access query using
> different formulas entered in a database table, as a text string?  I have
> gotten around this before by splitting up these equations into their
> different variables and storing the values into a table.  However, that 
> only
> works if the equation format stays the same.  I no longer have formulas 
> that
> closely resemble each other in format and differ only in values.
>
> Any ideas? 


0
Dale
12/4/2007 5:04:35 AM
Reply:

Similar Artilces:

Need a formula #4
I would like to have a formula that would calculate the following. Starting in year 2007 and ending in 2024 if each year the amount went up by 3 % each year My worksheet will have the years in column A and I would like the calculations to show up in column B Thank you to anyone willing to create this formula for me If you have the years in A2:A19 and in B2 you have the start amount, in B3 enter =B2*1.03 and copy down to B19 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "Just Me" <No@isp.com&...

Calculating Taxes and Making calculations
Hello, Kindly plz tell me how do i go about making some small calculations based on my existing data such as how to compute taxes and do other calculations based on some quantitative data. where exactly do i make modifications on my application??? Kindly help me out. Thanks , swati you can add code in javascript behind dropdowns or piclist fields. One example might be a dropdown which has shipping options ie ups, fedex etc. When you choose one it takes the weight of your package (whcih you enter) then calculates a shipping cost. search the archives of this group at groups.google.com and ...

How to Read/Write vsd uses Delphi?
I wana read/write vsd file by Borland Delphi. anyideas? Miles Guo What version of Delphi? Surely you can use the ActiveX control to access the vsd and its objects. Randall Arnold "miles guo" <milesg@china.com> wrote in message news:%23f4tOnqHEHA.3376@TK2MSFTNGP09.phx.gbl... >I wana read/write vsd file by Borland Delphi. > anyideas? > > Miles Guo > > On Sat, 10 Apr 2004 11:13:29 +0800, "miles guo" <milesg@china.com> wrote: >I wana read/write vsd file by Borland Delphi. >anyideas? http://www.diagramantics.com -- Regards,...

Insert text in Cell A1 based on keyword criteria
I want to populate cell A1 with specific text (the word "Active") whenever the word "Yes" is found in cells A2:A20. Whenever the word "Yes" is not found in cells A2:A20 I want to leave cell A1 blank. <twlove@ontuet.com> wrote in message news:1150210856.375367.146410@c74g2000cwc.googlegroups.com... >I want to populate cell A1 with specific text (the word "Active") > whenever the word "Yes" is found in cells A2:A20. Whenever the word > "Yes" is not found in cells A2:A20 I want to leave cell A1 blank. =IF(COUNTIF(A2:...

Help on Formula
Hi all, Newbie here! I think the post goes here....!?! I have a spread sheet that I cumulate rows G7:G16 (dollar amounts) wit total at G21. Now in H7 to H16 is where I put a "P" or empty t indicate if the corresponding G cell its payed for or not. I would like a formula that would only calculate the rows (G7 thru G1 that have a P in the corresponding H column cell. I'm sure this is possible but don't know how to acheive this. Ca someone help me? Thanks for your help -- Message posted from http://www.ExcelForum.com Instead of creating one simple formula, I create a new...

Procedure to calculate distance using latitude/longitude
Does anyone know of a vba procedure for access that will calculate straightline distance using latitude and logitude for 2 points? AJ wrote: > Does anyone know of a vba procedure for access that will calculate > straightline distance using latitude and logitude for 2 points? See: http://groups.google.com/group/microsoft.public.access/msg/6b934b0e580b974b James A. Fortune MPAPoster@FortuneJames.com ...

How to calculate age of a person?
Hi, I'm struggling to figure this one out. I have a date column and I'd like to be able to calculate the age without doing it in my head. I've tried to do this but am struggling. Any help will greatly appreciated. Thanks Suzanne Visit www.cpearson.com and search for DATEDIFF best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Grd" <Grd@discussions.microsoft.com> wrote in message news:6BFFD11C-AECB-4837-93B6-D84B6170B975@microsoft.com... > Hi, > > I'm struggling to figure this one out. I have...

Excel text corrupting problem
Has anyone seen anythink like what is happening in the Excel screenshot below .. http://www.jon-lamb.co.uk/tf_excel.gif The text is corrupting in cells. Its just happening on one PC and in most excel documents on that PC. It seems to happen a lot when cutting and pasting. So far, I've installed all office and windows updates, removed the anti virus software to test. Installed all graphics driver updates. PC spec is XP Pro, Celeron 3.2GHz, 2GB RAM. Office 2003 Any help much appreciated. Rgrds, Jon I believe that the display in XL is more through the Printer Driver than XL itself. T...

Can Not Access Money File
I am using Money 2004 Deluxe running on Windows XP It was working fine. But I had to re-install windows due to several reasons. After re-installation, I can not access my Money File, as it keeps asking for the password, and refuses to accept the password. I have certainly not forgotten the password as I use money on a fairly regular basis. Any help will be appreciated. Thanks As luck would have it, I had installed Money 2004 with Anti-Virus running. Although the intallation completed without any problems / messages, I thought it might be a problem. So I un-installed Money, then re-insta...

Using OWA from another Exchange Server
Hi Guys! We have 2 Exchange Servers in the same organization, EXCH1 and EXCH2,OWA is enabled on EXCH1. Both servers have mailboxes. The question is how do I configure users on EXCH2 to logon to their mailboxes using the OWA running on EXCH1.Your response will be most appreciated. We run Exchange Server 2003 SP1 on Windows Server 2003 SP1 -- Buchi "Buchi" <Buchi@discussions.microsoft.com> wrote in message news:4BDE18EE-D9DB-49F7-AB76-B13DA1C11064@microsoft.com... > Hi Guys! > We have 2 Exchange Servers in the same organization, EXCH1 and EXCH2,OWA > is > en...

IIF statement pulling from wrong field
I have a query have has the 5 variations of the following IIF statement: Bag Total: IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null) this statement says to add up if the unit of measure if it is a bag. I am attempting to add another IIF statement that says =Sum(IIf([product type] Like "*printed*", [pounds], 0)) - I am trying to add up if the product type has the word printed in it total it at each customer and at the end of the report. When I use this IIF statement on my report, it is totalling the amount from the printed IIf(...

Filtering on a formula
I have a column which has inconsistent formulae running down it. Is there any method of filtering the data dependent on the phrasing of the formula? Or can I somehow refer to the text of the formula in another formula? -- Timmy Mac1 ------------------------------------------------------------------------ Timmy Mac1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15188 View this thread: http://www.excelforum.com/showthread.php?threadid=483607 One quick and dirty way to check for formula consistency is to go into R1C1 reference style. Tools|Options|General ...

Form Fields
I have created a form for use by others. I used the Text form fields format when creating the form, my problem is the main text in the form does not stay locked in place. When we try to type to fill in the form fields it moves the main body text. I have tried using the Document Protection tab and telling it to allow formatting only in the form fields but that still does not prevent the other text from moving. I did this in word 2007. Any idea on what to do to lock the main body in to allow filling in just the Form fields. Hi Michael, The simplest way to gain control over the ...

"Program trying to access e-mail addresses" msg
Hi. I recently installed SP3 on my computer, and now I get an odd message when I open emails. A box pops up that says, "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose 'No'." My virus scan turned up nothing, and the "detect & repair" function of Outlook did not change this. Anyone have any idea what's going on? Thanks! Same here. After "yes" or "no" outlook stop responding. Curious to find an answer! Mel &...

Due Date Calculated
I need assistance on an expression that allows for the due date of recurring training to be one year from date completed. Details are as follows: I have a form named [frmTRNComplete] based on table named [tblTRNComplete] Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext (text box), Reccuring (check box) I would like the DateNext to be 1 year from DateComp if Reccuring is true and Date next to be blank if Reccuring is false. -- Aloha, Ron A. Ron I'll assume that you want to use the form to calculate the DateNext, and that you are not trying to s...

multiuser access to excel files
Is there a way to allow for multiple users to access an excel workbook simultaneously if it is stored on a network share? "Ric Deters" <ricdeters@swbell.net> skrev i en meddelelse news:058c01c35320$297e2e60$a301280a@phx.gbl... > Is there a way to allow for multiple users to access an > excel workbook simultaneously if it is stored on a network > share? Look in the Functions menu. Third item from the top (using a danish version so I don't know the english name for it) -- /\ preben nielsen \/\ prel@post.tele.dk tools/share workbook, and a check box. how nea...

EXCEL 2007 Formula to calculate INTEREST only on a 3 month bridge
I am trying to calculate monthly INTEREST ONLY payment on a short term bridge/swing loan. Assuming an interest rate of 2.75%, paid monthly, Also assuming it will be required for a 3-6 month period, amount approx $500,000. Just switched to Excel 2007 but don't seem to be able to calculate using the formula builder. Not sure if it is compounded daily or monthly. You might want to check out the IPMT function. From the XL help file: IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must...

Can Outlook access Exchange Servers over the Internet?
I have multiple Email accounts located on different Exchange Servers (different locations). I want to know if there is an add-on for Outlook, in order to access multiple Exchange Servers over the Internet instead of using the Outlook WEB page. Thanks for any help, VPN will let you do this. You need to ask the Admins at the places where you access Outlook via Exchange for the credentials and the specific client. You will also need to set up separate profiles for each Exchange account that you want to access. How VPN works with multiple profiles/exchange accounts, I do not know. --� Mil...

how to calculate outliers
Google is your friend: http://www.google.com/search?q=calculate+outliers - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ismhs" <ismhs@discussions.microsoft.com> wrote in message news:035124A0-06FA-4153-932E-6C4E0614F38C@microsoft.com... > ...

Calculated field options???
Here's my Data: Country Status --------- ---------- Canada Green Canada Blue Canada Green Canada Green Canada Blue In my pivot table, 'Country' is an item, and 'Status' is my only data field. What I really need to show in my data area is two column of data, one for 'Green' and one for 'Blue'. I tried creating two 'Calculated' fields with the following formulas, but they don't seem to be working: 'CalculatedField1' = IF((STATUS="Green"),1,0) 'CalculatedF...

Dynamics 4.0 slow to retrieve data when custom fields / lookups us
We've have this issue whereby if a custom field, say on the contact, is indexed, when you use advanced find, it seems to use the index. The response is acceptable. It appears however it you use the standard search bar and try the lookup, it appears that table scans are happening. Can anyone advise if we're reading the situation correctly and whether there is a way to speed up to normal search. Thanks -- C ...

Time Sheet Function to Calculate
I am having some trouble trying to establish a formula that will give me the number of hours worked during the day given the start time say 8.30am with an end time of 5.30pm and say 30 minutes for lunch. For this scenario, total working hours would be eight and a half hours but I need Excel to display this result as 8.30 hours (0.30 being the minutes in the half hour) and not 8.50. Can someone suggest what to do? Typically the layout would be Column A - Start Time say 8.30am Column B - Time in minutes spent at lunch say 30 minutes Column C - End Time say 5.30pm. Column D - the calculati...

Making one field bold in a concatanated string
This issue is in MS Access 2003 I have a string with the last name and first name fields concatanated. I would like to bold the last name field only. Is there anyway to do this? The standard text box in Access 2003 and earlier cannot do that. Stephen Lebans has a solution here: http://www.lebans.com/mixbold-plain.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "abnewallo" <abnewallo@discussions.microsoft.com> wrote in message news:519C584...

tenure calculations
how can I format several dates such as 04/19/2003, 04/01/2004, etc. int a yearly average for the employee tenure -- Message posted from http://www.ExcelForum.com right now say I have my yearly figures in i1-i9. The formula I though would work is =today()-average(i1:i9), but how would i format this to give me yearly read out -- Message posted from http://www.ExcelForum.com Hi djackson! One way is to use YEARFRAC with a third argument of 1 to get your tenures in years and fractions of a year and then average the result. However YEARFRAC does produce some annoying but small errors. Y...

User Access Error in MSCRM
Hi all, I hope someone can help with this problem. I had MSCRM installed and working without any problems. A colleague of mine went to assign a license to a new user by removing the license from an old user. He then disabled the old user's account. The result is that we are unable to access the entire CRM environment with the error messages below. I have uninstalled the CRM software and reinstalled but kept the same database. Any help would be appreciated and thanks in advance for your help. Regards, Pere ------------------------------------------------------------------------------...