If I write a select case statement for a field X i.e. 6 to 7 for example will this:
1. Select any fields X where the number is greater than or equal to 6 but less than (and not including) 7
B. Select any fields X where the number is greater than or equal to 6 but includes decimels of 7 i.e. 7.5
OR
C. Select any fields X where the number is greater than or equal to 6 but includes but literally only includes cases where the the whole number 7 appears>
I only ask because in an example in a book I am looking at says:
Case 10000 to 20000
......
Case 20001 to 30000
I would have through the word "to" means until but not including but in this case the field that equals 20000 would never be selected?
Can someone simplify my feeble scratching at a Case Select statement, please?
I have two fields in a table (True/False): IsActive and IsDefault
I would like to convert some If...Else...Then statements to a Case Select statement which details what should be done when any of the possible states of the two fields exist.
Semi-Psuedo example in the (subform) Form_Current Event: With Me If !IsActive = True And !IsDefault = False Then Do Something on the Parent form ElseIf !IsActive = True And !IsDefault = False Then Do Something Else on the Parent form ElseIf !IsActive = True And !IsDefault = True Then Do Something Else Again on the Parent form Else Do Something Entirely Different End If End With
What blows my mind is the Select Case Expression bit. I can't figure out how to write this. Any help is greatly appreciated.
I have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.
There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:
Case {T,T,T,T}
Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}
Case {T,F,F,F}
Case {F,F,F,F}
What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.
using the Select statement on a form;I have it looking up 3 fields, Ordered by the first and bound by the first.the other 2 are being stored in different fields.What is weird is that I need to have the first field entered twice in order for it to show up, which means I'm looking up 4 fields.When I go to store the selection after, only 2 are available... know this confusing; here is the Select Statement;
SELECT [ITEM CODE1].[ItemCode],[ItemCode],[Customer],[Project Description] FROM [ITEM CODE1] ORDER BY [ITEM CODE1].[ItemCode] ;
If I don't type ItemCode twice, it shows Project Description in the drop down box. When I use the code above, only ItemCode and Customer are available to store in other fields;
Me.Customer = Me.ComID.Column(2) which actually stores Customer (should be column 3)
When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".
Code: Private Sub cmd_Update_Conditional_Codes_Click() Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset
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)
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,
Hi everyone, please help, i am in depserate need here. Am i right in thinking nested IF functions are limited to 7 variables, i need to choose from 12, so i need to use something different
it seems like a CASE function could do what i want it to, but i really have no clue where to start, shall i enter this into a query criteria box, or on the form itself ina new field box...??
Can you give a few hints as to what the code would look like, i have two fields, obviously. One is 'lesson_type' and one is 'cost' i want the cost to change depending on what is selected in the lesson type box.
I am trying to make a field in a query which displays a date based off of the criteria below. I tried nesting IIF statements but I couldn't get it to work so I tried a Case WHEN statement.
CASE WHEN [Appfrom] Is Null & [Fundfrom] Is Null THEN "7/7/1999" WHEN [Appfrom] Is Null & [Fundfrom] IS NOT NULL THEN [Fundfrom] WHEN [Fundfrom] Is Null & [Appfrom] IS NOT NULL THEN [Appfrom] WHEN [Fundfrom] IS NOT NULL & [Appfrom] IS NOT NULL THEN "7/7/1999" END
Here is the IIF statement
IIf(IsNull([Appfrom] And [Fundfrom]),"7/7/1999",(IIf(Not IsNull([Appfrom]) And IsNull([Fundfrom]),[Appfrom],(IIf(IsNull([Appfrom]) And Not IsNull([Fundfrom]),[Fundfrom],"7/7/1999"))))))
Neither one works and have no idea how to fix it. Any ideas would be greatly appreciated. Thanks.
Here is my CASE WHEN statement in SQL. What would the If statement be in MS access?
CASE WHEN LEN(Serial) = 9 THEN Serial WHEN LEN(CAST(Serial as Varchar(255))) = 8 THEN '0'+Serial WHEN LEN(CAST(Serial as Varchar(255))) = 7 THEN '00'+Serial WHEN LEN(CAST(Serial as Varchar(255))) = 6 THEN '000'+Serial ELSE 'Error' END AS NewSerial
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
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
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
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query which contains many nested IIF-statements. Due to that, my database is running terribly slow. I want to use a 'SELECT CASE' statement because I think this would be faster.
In order to get used to the SELECT CASE-thing I created a new database and I implemented a query with the following SQL-code:
SELECT ColorID, 'Colorvalue' = CASE WHEN Color = 'Green' THEN 100 ELSE '0' END FROM Color.Colors
(Color is a fieldname, Colors is a tablename)
This code is not working, between CASE and WHEN is something wrong according to Access.
Probably I am overlooking the obvious, does anyone has a tip for me??
I wonder if any one can help with this one, shown below are 2 examples of strings in a field called PROCOM
Occurred: EnterMeterReadings; Reason: Reading greater than upper limit; Session: 0A52BE4293.worker1
MSPSubmitMeterReadings; Reason: CANNOT BILL LATER THAN 29/02/08, DATE AMENDED FROM 13/03/08
I ususally nest IIf statements to abbreviate these in another field (PROC), as PROC: IIf ([PROCOM] like Occurred: EnterMeterReadings; Reason: Reading greater than upper limitMRE-Upper limit",[PROCOM])
The problem I have is there now 30 permutations of the error and IIF can only nest 8 statements (and it looks very messy). I thought that Select Case would do the job but I cannot use
Select Case
Case Is Like "Occurred: EnterMeterReadings; Reason: Reading greater than upper limit*"
Hiya is there an easier way of grouping together 'Select Case' statements rather than this way I have done them. They work but I still have more to add and feel this way is a little long winded! Private Sub Form_Current()
Select Case DatePart("w", Tape_Date) Case 1, 3, 5 Me.Datapulse.Visible = True Me.Label86.Visible = True Me.Label87.Visible = True Case Else Me.Datapulse.Visible = False Me.Label86.Visible = False Me.Label87.Visible = False End Select
Select Case DatePart("w", Create_U607ST50) Case 6 Me.Create_U607ST50.Visible = True Me.Label88.Visible = True Me.Label89.Visible = True Me.Label129.Visible = True Case Else Me.Create_U607ST50.Visible = False Me.Label88.Visible = False Me.Label89.Visible = False Me.Label129.Visible = False End Select
Select Case DatePart("w", U607ST94) Case 6 Me.U607ST94.Visible = True Me.Label90.Visible = True Me.Label91.Visible = True Case Else Me.U607ST94.Visible = False Me.Label90.Visible = False Me.Label91.Visible = False End Select
I am trying to select a different query based on the selection in the [Product] Field. I have 4 different products and 3 different queries to run. Each product is specific to a particular query.
What I would like is: When Product 3 is chosen "Query 1" is ran. When Product 1 is chosen "Query 1" is ran. When Product 2 is chosen "Query 2" is ran. When Product 4 is chosen "Query 3" is ran.
As it is set up now it appears that when choosing Product 1 I open Query 2 and when choosing Product 2 I open Query 1, but it is the opposite of that. I get the correct query with this code by choosing Product 1 and the Query that opens is actually Query 1, I don't know what is going on here.
When I open Products 3 & 4 it always opens Query 2, no matter what Query I specify.
Please help me sort this out. I am not very familiar with case select. Thanks
Private Sub run_query_Click() On Error GoTo Err_run_query_Click
Dim stDocName As String stDocName = "Query 1" (Should be for Product 1 & 3) stDoc1Name = "Query 2"(Should be for Product 2) stDoc2Name = "Query 3" (Should be for Product 4)Select Case stProtocolName Case [Product] = "Product 1": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 2": DoCmd.OpenQuery stDocName, acNormal, acEdit
Case [Product] = "Product 3": DoCmd.OpenQuery stDoc1Name, acNormal, acEdit
Case [Product] = "Product 4": DoCmd.OpenQuery stDoc2Name, acNormal, acEdit
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?
Code:
Private Sub cmd_Update_Conditional_Codes_Click() Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset
