Text Box Calculation Formula

SpreadCred
InsideSpread
LeftMain
RightMain

I have a text box (SpreadCred) that needs to be calculated/populated by a 
combination of factors.

1. SpreadCred should populate off of the text box - InsideSpread.

2. The value in SpreadCred can not be greater than text box - RightMain nor 
text box - LeftMain.  If it is bigger than either number, it will max out at 
the larger of the two.

Thanks for your time.
0
Utf
12/9/2009 6:22:01 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
816 Views

Similar Articles

[PageSpeed] 40

How this is done depends a little on whether the value will respond to user 
input or to previously-entered data.

This could be done with a nested IIf, but is really a candidate for a custom 
function call. Assuming all three values are long integer types, do this; 
replace Long by Double or other data type as required:

Make this the ControlSource of SpreadCred

=CalcValue(InsideSpread,LeftMain,RightMain)

Add this function to the module for the form or report:

Private Function CalcValue(varInsideSpread as Variant, varLeftMain as 
Variant, varRightMain as Variant) as Variant
If IsNull(varInsideSpread ) or IsNull(varLeftMain) or IsNull(varRightMain ) 
Then
  CalcValue = Null
Else
  CalcValue = lngInsideSpread
  If CalcValue > lngLeftMain Then CalcValue = lngLeftMain
 If CalcValue < lngRightMain and lngRightMain < lngInsideSpread Then 
CalcValue = lngRightMain 
End If
End Function

The If IsNull just ensures that the value will be blank until all three 
prerequisite values are populated. You will need to use NZ if you want to 
assume 0 for nulls.

"Tom Perot" wrote:

> SpreadCred
> InsideSpread
> LeftMain
> RightMain
> 
> I have a text box (SpreadCred) that needs to be calculated/populated by a 
> combination of factors.
> 
> 1. SpreadCred should populate off of the text box - InsideSpread.
> 
> 2. The value in SpreadCred can not be greater than text box - RightMain nor 
> text box - LeftMain.  If it is bigger than either number, it will max out at 
> the larger of the two.
> 
> Thanks for your time.
0
Utf
12/9/2009 10:52:02 AM
Here are the properties for the fields
Field size: DECIMAL
Format: GENERAL NUMBER
Precision: 18
Scale: 3
Decimal Places: 3

How will this change the programming?

"Brian" wrote:

> How this is done depends a little on whether the value will respond to user 
> input or to previously-entered data.
> 
> This could be done with a nested IIf, but is really a candidate for a custom 
> function call. Assuming all three values are long integer types, do this; 
> replace Long by Double or other data type as required:
> 
> Make this the ControlSource of SpreadCred
> 
> =CalcValue(InsideSpread,LeftMain,RightMain)
> 
> Add this function to the module for the form or report:
> 
> Private Function CalcValue(varInsideSpread as Variant, varLeftMain as 
> Variant, varRightMain as Variant) as Variant
> If IsNull(varInsideSpread ) or IsNull(varLeftMain) or IsNull(varRightMain ) 
> Then
>   CalcValue = Null
> Else
>   CalcValue = lngInsideSpread
>   If CalcValue > lngLeftMain Then CalcValue = lngLeftMain
>  If CalcValue < lngRightMain and lngRightMain < lngInsideSpread Then 
> CalcValue = lngRightMain 
> End If
> End Function
> 
> The If IsNull just ensures that the value will be blank until all three 
> prerequisite values are populated. You will need to use NZ if you want to 
> assume 0 for nulls.
> 
> "Tom Perot" wrote:
> 
> > SpreadCred
> > InsideSpread
> > LeftMain
> > RightMain
> > 
> > I have a text box (SpreadCred) that needs to be calculated/populated by a 
> > combination of factors.
> > 
> > 1. SpreadCred should populate off of the text box - InsideSpread.
> > 
> > 2. The value in SpreadCred can not be greater than text box - RightMain nor 
> > text box - LeftMain.  If it is bigger than either number, it will max out at 
> > the larger of the two.
> > 
> > Thanks for your time.
0
Utf
12/10/2009 3:23:58 AM
Reply:

Similar Artilces:

userform txt box contents
i have a userform. on the form is a text box called txtid. i need it to show content of a formula as follows: =counta(A1:A1000)+1. can anyone help me? i can email the workbook if needed. regards, nigel Hi Nigel Txtid.Text = Range("A1").Formula HTH. best wishes Harald "Nigel" <Nigel@discussions.microsoft.com> skrev i melding news:4422B8DD-78CA-443A-B589-5F4AE59AA089@microsoft.com... > i have a userform. on the form is a text box called txtid. i need it to show > content of a formula as follows: =counta(A1:A1000)+1. can anyone help me? > i can email the ...

Last item in Combo Box
I have a combo box where you can select the employee assigned to a project. In the combo box there is also a "Not Assigned to Company" optin that can be selected. Is there a way to sort by employee, but also have this "Not Assigned to Company" as the last item listed on the drop down list even though it not last if put in Ascending order? I've done this by adding a numeric Sort column to the combox's source table and using that in the order by portion of the combox row source. Supe wrote: >I have a combo box where you can select the employee assigned to a...

Adding Formatting Instruction to a Formula
Excel 2000 This question is being asked more out of curiosity than out of any real need. I have the following formula in a cell. The text is oriented vertically and the column widths are such that I need to put it all in one cell in order to keep the information together on the page. =CONCATENATE(A16 & ": " & TEXT(B16,"mmmm") & CHAR(10) & A17 & ": " & TEXT(B17,"mmmm,yyyy") & CHAR(10) & A18 & ": " & TEXT(B18,"mmmm d, yyyy") & CHAR(10) & A19 & ": " & TEXT(B19,"mm...

Calculating a grand total for an unbound control in a report
I have a report for sales grouped by customer as follows customerA itemA lbs sold $ sales itemB lbs sold $ sales customerB itemC lbs sold $ sales etc..... The report automatically subtotals per customer for lbs and $ then grand totals in the report footer. We have some customers with rebates, returns, allowances etc... that we have an adjustment to the $ sales # that I pull in on the query that is the source for this report and it shows up under the customer subtotal then I have a control box that calcul...

Text box
I want to add a text box for a email address at the begining of my all my text boxes. The Email address must be inserted into the 1st text box before anyother text box can be completed. I am using MS FrontPage 2003. If you are using the standard FrontPage Form Field validation, the best you can do is to require that they enter something in the field before the form is submitted. Right-click the email one-line text box and select 'Form Field Validation'. Check the 'Data Length' 'Required' checkbox. -- ~ Kathleen Anderson Microsoft MVP - Expression...

csv-files with '+' in field -> treated as formula
When I make up a CSV-file (from our oracle-database) we have to fill in a line like this: 9603144;01;TEST;+BE;01/01/1000;31/12/9999 When opening the file in Excel, the 4th field (+BE) is treated as a formula, but that is not an option. Anyone have an idea how to write it in the CSV-file so that it is treated as a text-field but without showing any quotes (so I would like to see +BE and not '+BE) Tnx already in advance! Dave found something myself: just add a space before the + and it's ok if anyone has another idea: post it >-----Original Message----- >When I make up ...

Excel - Array Formulas - Freeze
Hi all I have a big problem. It takes about 30 minutes to save my excel fil because I have many array formula: about 32 000! So do you have a ti to accelerate the computing process? Is a VBA function would do i faster? Here is an example of what I use: Col A contain unique numbers Sheet 1: Col A Col B Col C Row1 001 123 9i9 Row2 005 456 8u8 Row3 003 406 8ur etc... to +8000 In sheet 2, I have to check if the data exist in sheet 1. Col D contai Yes (data is in the sheet1) or No... Examples: Col D check if the data in Col B is the same in sheet 1 Col...

will formulas be lost when moving data in a worksheet?
I have a P & L that contains 3 years of info. I have to delete the oldest year (2007) and put in our actual numbers for year ending 2010. Will I lose all of the formulas when I delete all of the 2007 numbers? Is there an easier way to do this? Edit/ Goto/ Special/ Constants Delete those. -- David Biddulph "Renae" <Renae@discussions.microsoft.com> wrote in message news:456322CE-EB1E-4616-8716-881CF545DC33@microsoft.com... >I have a P & L that contains 3 years of info. I have to delete the oldest > year (2007) and put in our actual numbers for y...

Why is it defaulting to plain text for replies and forwards?
All of a sudden, my Outlook is set whereby all replies and forwards open in plain text, even though when I compose a new e-mail it opens in HTML. ...

Converting variable text strings to numeric
I've imported some word tables into excel, the 'numbers' have spaces instead of commas and consequently are read as text. The 'numbers' vary from single integer to millions For example 1 101 335 293 207 23 113 101 19 While I can use the left, mid and right functions for the millions that formula is unworkable for other numbers. Any ideas, or do I have to get a VB programmer to write a function for me? Hi! Try using Find/Replace Select the range Goto Edit>Replace Find What: enter a space by hitting the space bar Replace With: nothing...

"Thickening" text
I am using Access 2003. I created a form for my database to edit/enter new data. Within the form, I inserted a tab control box with three pages, and within each tab page, there are various fields and labels. My problem happens when I open the form in Form View and scroll through the records. As I scroll, the text in the labels gradually "thickens", to the point where it appears to be in bold and is difficult to read. What is strange about this problem is that 1) it only occurs on the second and third tab pages (not the first), 2) it only happens with True Type fonts in ...

unable to sort in Select Names box
I just migrated from 98se/OL 2000 to XP proff/OL 2002. Now when I open a new email and click the "To..." button, and it opens the "Select Names" box, and I go to select a recipients email the fields won't let me sort like I used to. Plus there are additional entries with fax numbers, how do get it to sort and NOT display the fax number entries? Also, is there any way I can make this "Select Names" box bigger so I don't have to scroll right to see the if it is the correct email address if they have multiple email address's? Thanks Ted No...

Combo Box Issue
I have made one of my fields in a subform a combo box. When I open the form in form view, everything looks ok, but when I open the drop-down arrow and make a selection, an error window immediately opens. This is the message: You can't assign a value to this object. *The object may be a control on a read-only form. *The object may be on a form that is open in Design view. *The value may be too large for this field. I can hit the Return key and move on to the next field and the selected data stays in the field. Can anyone help in resolving this error? Thanks in advance! ...

Combo Box problem 02-15-10
Hi I have 3 tables and 3 associated forms in an 2007 access database. On each form is a combo box "Name Search" so that one can input 2 or 3 letters of a surname and get a drop down list of names matching the input. All three Combo Boxes were set up in an identical manner using the wizard. The Main Menu has three buttons where you choose which table (or dbase) you wish to query. I now have a situation where 2 of the 3 combo boxes will not accept input (i.e. do not work) If I copy the Row Source from the one combo box that always works to one of the other two...

Help with formula #5
I am working with a income statement. In column A are the account classifications, column B identifies th account as fixed or variable expense, column C is the value. Wha formula can I use that I can total the Fixed and Variable costs in tw different cells based on what is in column B so that if I change th letter in column B to either V or F the total Fixed or Variable amoun will change accordingly A B C Sales 10,000 Sales Returns V 500 Cost of Goods V ...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

Multiple combined formulas ?
I am not sure what combinations of formula’s are needed to lookup an item, evaluate multiple results and list the result. Here is a sample scenario: ID Item Result 1234 Degree 30 1234 Degree 15 1234 Method Hand 456 Style Green 789 Degree 30 1023 Degree 15 1023 Degree 10 1023 Method Foot I need to be able to lookup the ID number e.g. “1234” in column A, evaluate all the item responses for 1234 that match, e.g. “Degree” in column B and then evaluate the results for all the items called “Degree” for ID 1234 and then if the Result is e.g. 30, record 30 in the cell. Hope I have explained it...

Dialogue box too wide
I use the macro below to open a dialogue box for this instruction. "The file will be saved with the new data Click OK to continue. To stop, click CANCEL". But the box is too wide. Can someone provide me a macro to correct this. Thank you. Regards, Salza ------------------------------------------------- Option Explicit Sub HU_simpan() If MsgBox("The file will be saved with the new data. " _ & " Click OK to continue. To stop, click CANCEL. ", _ vbOKCancel) = vbCancel Then End Application.Dialogs(xlDialogSaveAs)....

can I have a multiselect option for a list box in excel 2003
I am working with Excel 2003, I created different list boxes for different fields but I wanted to know if I could select more then one answer by using CTRL command? Is that only available for later versions? thanks Yes, you can. Depending on what type of listbox it is and where it's located, the instructions to modify the settings would be different. If it's a listbox from the Forms toobar placed on the sheet, then rightclick on it and choose Format Control, then Control tab and choose Multi. If it's a listbox from the Control toolbox toolbar placed on a workshee...

formula required
hi I don't know if this can be done but here goes, I have the ages of certain members of a club and I want to label them in the next column as either :- under 12, 12-18, oap or adult. I then need to allocate a payments due colunm depending on there status ie under 12s �4, 12-18 �6. I have tried in vain and cannot do but it would make it so much easier for me if it could be done. Many thanks Jim one way to try =if(a2>=18,"adult",if(a2>=12,"12-18","under 12")) -- Don Guillett SalesAid Software donaldb@281.com "jim" <jcreask@nospamblueyo...

[vba-newbie] testing cell text for specific characters?
I would like to test the text value of a cell to determine if i includes a certain string of characters and set a variable to true whe appicable. In other words, something like this (unknown method in human syntax an italics) : -If Range["A1"] text string includes 'thisText' Then- boolVariableName = True End If Is there a built -in way to do this with VBA? I'm sure there is, but haven't been able to locate the appropriate funtion in the help files this forum, or via google. Can anyone assist -- Message posted from http://www.ExcelForum.com One way: boolVar...

How to calculate numerous clicked cells?
I have a spreadsheet where say column A(i.e. apples) is subject matte and column B is the price of subject matter; columns of subject an price extend down to cell 30. I, also have column C(another subjec matter) and column D(the price of C subject matter) and thes subjects/figures also extend down to cell 30. I want to be able to click on the price of B1 and D1, have exce highlight(keep prices highlighted) each clicked price and the calculate it. B1 and D1 are of course just examples, as it could b any of the prices that needed to be calculated; i.e. B1 + B29 + D1 +B10 + D3, etc, etc.... ...

military time calculations
I am trying to write a program that tracks time entries in "Military time" and then average multiple entries, that cover a 24 hour cylcle for three shifts. I have 90% of the formulas in place but have run into a snag. I need to figure per hour, the number of calls and the average time each took from call in to completion. There will be some hours without any calls and some with several. I need to examine each entry to see when the call came in and when completed, then take the elapsed time and store in a separate cell to be averaged together with other entires fr...

How do I set up a text insert?
Hi We are fairly new outlook users running acting agency. We want to know how you set something up so that when emailing appointments to people we can just insert the following text TIME: PLACE: DATE: Good luck! I was shown how to do this but have since forgotton. Can anyone help?? Thanks You can use templates for this such as Quick Parts or AutoText. For details see; http://www.howto-outlook.com/howto/messagetemplates.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, A...

Copy and paste data from Auto Filter / Subtotal (or apply a formula to filtered data)
I have Excel 2003 at work and Excel 2000 on one of my computers at home. I am not sure how much this feature differs between the two programs, but I am defintitely experiencing this problem on my work computer: When I apply autofilter and then copy the data and try to paste it onto another worksheet, it pastes all the data, not just the visible data (i.e the data that is still visible when autofilter is applied). I have just tried this on my home computer (2000) and the results are a little more unpredictable. Sometimes it will paste only the visible data, but sometimes it pastes all the dat...