Conditional Calculation Formula Using "Case"
Feb 15, 2005
Howdy,
I'm missing something because my calculations are off on a form. I have a form with a textbox labeled "txtStartupTemperature". The value I insert here will affect another value in a textbox labeled "txtStartupTCF". If my temperature value is <= 25, I will use one formula for my calculation. If my temperature value is >25 then I will use an alternate formula. Both formulas involve exponentials. Here's the code I developed for my public function:
Public Function TCF(ByVal sngValue As Single) As Single
Select Case sngValue
Case Is <= 25
TCF = Exp(3480 * (1 / 298 - 1 / (273 + txtStartupTemperature)))
Case Is > 25
TCF = Exp(2640 * (1 / 298 - 1 / (273 + txtStartupTemperature)))
Case Else
End Select
End Function
In my form's code I've inserted the following:
Private Sub txtStartupTemperature_AfterUpdate()
Me.txtStartupTCF = TCF(Me.txtStartupTemperature)
End Sub
I tested this code with a temperature value of 15. This should calculate out to be 0.67. Instead I'm getting 0.34. Are my formulas written correctly? If so, what else could be going on?
View Replies
ADVERTISEMENT
Aug 12, 2004
Please advise how I can incorporate a time variable (in elapsed minutes) in a formula and have it update automatically every minute:
example: count the number of minutes elapsed from a point in time multiplied by a fixed rate (such as Gallons per minute) to produce a value of gpm over a period of time.
Any suggestion are welcome.
Stinhop
View 4 Replies
View Related
Jul 8, 2013
I would like to know how to have conditional formula (code) for my monitoring. I have the following fields for form [1]:
Date today
Date Encountered
Date Closed
No. of Days
I need an automatic computation of the No. of days until it is closed. Below is the computation I want to have:
If it is not yet closed: [Date today] minus [Date Encountered]
If already closed: [Date Closed] minus [Date Encountered]
I tried using a code builder:
If Forms![1]![Date Closed].Value<>""Then
Forms![1]![No. of Days]="[Date today]-[Date Encountered]"
Else
Forms![1]![No. of Days]="[Date Closed]-[Date Encountered]"
End if
but there is error.
View 9 Replies
View Related
Oct 23, 2007
I currently have two queries. One calculates emissionsperhour based on a results field and the other calcualtes emissionsperperiod based on the emissionsperhour query and the inspection date. I need to go a little deeper and seperate the emissionsperperiod into two categories. Category 1 will sum the emissions for the year up to 5 (Results). Category 2 will sum the emissions for the year from 5 and over.
My theory is this...in the emissionsperhour query create 2 more expressions which are Cat1lbperhour and Cat2lbperhour. Cat1lbperhour needs to calculate using a maximum of 5. Cat2lbperhour needs to first look for any results >5, subtract 5, then calculate lbperhour based on the difference.
Any ideas as to what this looks like in SQL?
Thanks
View 8 Replies
View Related
Oct 21, 2015
So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
Would it require a different report per year?
View 1 Replies
View Related
Mar 16, 2013
Is there any way of making data that is inputted in lower case to automatically change to the first letter of each word being a capital ...
View 4 Replies
View Related
Aug 24, 2004
hi
help is needed yet again :-)
I know when creating a text field in the format option
you can use the > or < sign so that when text
is entered it automatically changes it to uppercase or
lowercase - but i need it to be Title Case, any one know
how I can do this....
View 11 Replies
View Related
Apr 6, 2007
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 Replies
View Related
Nov 20, 2006
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
View 2 Replies
View Related
Apr 7, 2006
I have an Option Box that contains three Report Choices, Report1, Report2 and Report3. I want to run the Reports. I coded using If-Then and it worked out fine. See code below:
Private Sub Frame0_Click()
If Me.Frame0 = 1 Then
DoCmd.OpenReport "Report1", acViewPreview
ElseIf Me.Frame0 = 2 Then
DoCmd.OpenReport "Report2", acViewPreview
Else
Me.Frame0 = 3
DoCmd.OpenReport "Report3", acViewPreview
End If
End Sub
I would like to get the same result using "Case." However, when I use the Case format, it does not show all the reports as it does in the IF -Then format. See Case format below:
Private Sub Frame1_Click()
Dim Test As Integer
Select Case Test
Case Me.Frame1 = 1
DoCmd.OpenReport "Report1", acViewPreview
Case Me.Frame1 = 2
DoCmd.OpenReport "Report2", acViewPreview
Case Me.Frame1 = 3
DoCmd.OpenReport "Report3", acViewPreview
End Select
End Sub
Something simple is missing at the top. Please advise as to how to correct this?
View 3 Replies
View Related
Sep 28, 2004
Someone was helping me out and sent me the attached code to connect to a SQL server from Access2000 in a query. Access doesn't use the CASE statement, but the IIF statement instead. Can any of you help in deciphering the below CASE statement into an IIF statement?....
View 2 Replies
View Related
Jul 24, 2005
Can I use a [system number] value stored in a table in a select case?
Select Case [system number] "Tble system numbers"
Case 42144
do this
Case 88754
do this
???
Jon
View 6 Replies
View Related
Jul 26, 2005
I have this code in an event property on a report and it is working great. However, I now need to do a second case based on which modules are down. Is it possible to have a select case within a select case? I have the select case for the systems now with in each system I need a select case based on modules?
Dim dbs As DAO.Database
Dim rst AS DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From YourTable")
While Not rst.EOF And Not rst.BOF
Select Case rst![System Number]
Case 42144
' do this
Case 88754
' do this
Case Else
' do this
End Select
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
Set dbs = Nothing
View 4 Replies
View Related
Jan 1, 2007
How can I set up a table so that as we assign a case it generates a new number with the year in front of it. I would like for the case number to be generated by the program and when someone enter a new case the program generates the next case number and assignes it to that case. The format we use for our file numbers are year-XXXX (ex: 07-0001). With the new year I'm trying to set this up so that as we start the year the computer will generate the new number.
View 1 Replies
View Related
May 2, 2007
Any ideas how to Capitalise data in a table.
View 14 Replies
View Related
May 23, 2005
Hi,
I am trying to run the following query and getting an error. It looks like its because of the Case Statement.
SELECT Role.RoleID, Objects.ObjectID,
CASE MID(Objects.ObjectName, 4, 2)
CASE 'SR'
0
CASE ELSE
2
END AS AccessType
FROM Role, Objects
WHERE (Role.RoleID > 2115)
Can you give me suggestions plzzz
Thanks,
View 2 Replies
View Related
Jun 28, 2005
Hi everybody,
I'm having quiete a problem with the case instruction. I have the folowing query:
Select station, count(case poc_1 when 1 then 1 end), count(case poc_1 when 2 then 1 end)
From poc
group by station
Well Acces tells me there is a syntax error in the case instruction???Can't find!!!Can anybody help? Tanx
View 4 Replies
View Related
Jun 29, 2005
Hi,
I'd like to know if the keyword CASE, or IF... is recognized in Access (sql) queries.
If not, wat should I do to implement a condition, in a query or in the source of a textbox in a report ?
The problem : I have a report that use this query in a calculated field, and I want to avoid results that are equal to zero (i.e.divising by zero).
Thanks :)
Twinpath
View 3 Replies
View Related
Oct 10, 2006
Hi here is the original code
MAX(IIf([Book]='C',[Score]," ")) AS CScore,
Max(IIf([Book]='C',[PercentileScore]," ")) AS CPercent,
Max(IIf([Book]='C',[PassFail]," ")) AS C,
Max(IIf([Book]='D',[Score]," ")) AS DScore,
Max(IIf([Book]='D',[PercentileScore]," ")) AS DPercent,
Here is what I thought may work, but it seems like there should be an easier way
Case when Max ([Book]) = 'C' then [Score] else null as CScore,
Case when Max([Book]) = 'C' then [PercentileScore] else Null as CPercent,
Case when Max ([Book]) = 'C' then [Passfail] else Null as C,
Case when Max([Book]) = 'D' then [Score] else Null as Dscore,
Case when Max ([book]) = 'D' then [PercentileScore] eslse Null as DPercent,
Thanks
K
View 3 Replies
View Related
Oct 24, 2007
I have two tables that have fields set to a text so that the ClientID is their name.
When I query, my queries don't take into account the case. So "K Smith" is the same as "K SMITH" as "k sMIth"
I am trying to write an unmatched query between two tables based on this ClientID but it will turn up no unmatched because it is not taking into account the case.
Any suggestions on how to match the cases, othere then changing the table?
Thanks.
View 1 Replies
View Related
Dec 19, 2007
Hello, I'm attempting to use a case statement in order to select fields from a column that have names and addresses mixed. I'm wanting to select only the fields that contain names. For example some fields start with an address of "1998 Sky Rd" or PO BOX, or Suite at the beginning of the field. I would like to first select all fields with names in the field and then use an update statement to move/switch fields to another column. The only problem is that I can't seem to get this query to work. Please help!!!
Thank you!!!!!!!!!
SELECT address1, name3=address1 as expr1
Case [address1]
When mid([address1],1,1) Like [A-Z] then [address1]
When [address] is null then ""
When [address] = "" then ""
Else 'Null"
End,
FROM Exercise1;
:confused:
View 9 Replies
View Related
Mar 21, 2005
i have a form, and i would like that on one of the fields, as soon as i type something in it when i leave the field, i want it to automatically convert it to title case. can this be done please?
Cheers
View 1 Replies
View Related
Oct 27, 2004
New at this and need help!
I am trying to calculate a date in the future based on an entered date and the sample falling into one or more categories.
Fields: Start Date, manually entered
Interval, combo box, choice of monthly or weekly
Pull Interval, combo box, choice of Long term, Short Term, or Stressed
Conditions, combo box, choice of 25°C, 40°C, or 60°C
1 Week, calculated field
3 Month, calculated field
What I would like to happen is that when the Interval box is the choice of monthly that the 3 Month field calculates the date 3 months from the start date. Also, if the Interval field is weekly the 3 Month field is to be left blank.
I have managed the Dateadd function to calculate the 3 month date, but do not know how to incorporate the condition of the weekly statement to leave the 3 month field blank.
Also where should I put this, right now I have this calculation in a subform. Would it be best to generate this in a query? and if so how do I set this up?
Also, I need to select that the Pull Interval is Long term that I can only choose the Conditions field, 25°C, Interval field as Monthly, and only calculate the 3 month time point. Same thing with the Short term and stressed Pull Intervals, I want when they are selected to only allow me to look at fields relative to those conditions.
Any help would be extremely appreciated. I have several books and they are not spelling out exactly what I need, and with my limited programming experience I am stuck.
Thanks
ChrisB37
ChrisB37View Public ProfileSend a private message to ChrisB37Find all posts by ChrisB37Add ChrisB37 to Your Buddy List
View 1 Replies
View Related
Nov 1, 2005
I have a linked table that updates regularly. It stores all words in Upper Case. I would like to mail merge out of a query but I would like to format the words so that the first letter is Upper Case and the rest of the letters are Lower Case. I have tried formating the field in the query but this doen't work.
Any way to do this?
View 7 Replies
View Related
Feb 20, 2006
Can someone put a newby straight please?
Why do I get a 'NAME' error from this?
Many thanks in advance
Don
Select Case SortOrd
Case "a": Me.SortOrd.DefaultValue = "B"
Case "b": Me.SortOrd.DefaultValue = "C"
Case "c": Me.SortOrd.DefaultValue = "D"
Case "d": Me.SortOrd.DefaultValue = "E"
Case "e": Me.SortOrd.DefaultValue = "F"
Case "f": Me.SortOrd.DefaultValue = "G"
Case "g": Me.SortOrd.DefaultValue = "H"
Case "A": Me.SortOrd.DefaultValue = "B"
Case "B": Me.SortOrd.DefaultValue = "C"
Case "C": Me.SortOrd.DefaultValue = "D"
Case "D": Me.SortOrd.DefaultValue = "E"
Case "E": Me.SortOrd.DefaultValue = "F"
Case "F": Me.SortOrd.DefaultValue = "G"
Case "G": Me.SortOrd.DefaultValue = "H"
'Case Else
'Me.SortOrd.DefaultValue = "Oops"
End Select
View 2 Replies
View Related
Apr 28, 2005
i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?
thanks in advance
View 6 Replies
View Related
