Using Select Case
Jul 24, 2005Can 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
ADVERTISEMENT
Modules & VBA :: Getting Error End Select Without Select Case?
Sep 5, 2014When 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
[Code].....
Need A Little More Help With A Select Case
Jul 26, 2005I 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
Help With IF/Then/Else Or Case Select...I Think
Oct 27, 2004New 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
Select Case
Feb 20, 2006Can 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
Select Case Question
Oct 2, 2005Greetings all,
done a quick search and came up empty handed
what is the best way to use 'or' in a Case statement. I am using
Case "14" or "15"
however it only seems to recognise the first number not the second. Sometimes it does not recognise either number.
~rbinswe
'Select Case' Problem
May 22, 2006Hi,
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??
Cheers,
Willem
CASE Statement In SELECT
Mar 21, 2007Does anyone know if its possible to perform a CASE Statement in a SELECT statement in Access and if so what the syntax is? Thanks in advance.
View 9 Replies View RelatedIs Select Case The Correct Way To Do This
Mar 14, 2008I 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*"
Hope someone can help with this one
Thanks in advance
Multiple Select Case
Mar 30, 2006Hiya 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
End Sub
Case Select To Run Queries
Oct 27, 2005Please help with the code below.
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
Exit_run_query_Click:
End Select
Exit Sub
Err_run_query_Click:
MsgBox Err.Description
Not The Best At Case Select Statement Writing...
May 14, 2007Can 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.
Order Of Events For Select Case
Oct 2, 2005I re-arranged things in the code to try to make it clear as to whats going on. But I'm at a loss to why the qry will only run once. When I open the db and add a new record it works, when I try to add the next one, it doesnt....
Is this an issue with Access or is it me? I stripped it down in case someone could look at it....
The a mousehook module in the db to prevent scrolling..
Select Case Statement - General Q
Feb 6, 2008If 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) 7B. Select any fields X where the number is greater than or equal to 6 but includes decimels of 7 i.e. 7.5 ORC. 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 30000I would have through the word "to" means until but not including but in this case the field that equals 20000 would never be selected?
View 14 Replies View RelatedSpecific Date For Select Case
Apr 3, 2006OK I have done a select case as follows
Private Sub Form_Current()
Select Case DatePart("w", Tape_Date)
Case 1, 3, 5
Clearly this runs/works on a Sunday, Tuesday and Thursday.
I have tried to adjust this to work on a specific date but I'm struggling.
Would I use 'datePart' or just 'Date' and what format in the case would I use.
Thanks in advance
Modules & VBA :: Select Case Function
Aug 17, 2014My problem now is about evaluating the result of that query in order to calculate quarterly and annual performances. The query returns correctly:
1) NULL - when I don't have values in the given month
2) 0 - when one of the components of the performance formula is 0
3) value (positive or negative) which can include any positive or negative value
In order to calculate the quarterly values I need the performance of the last month in the quarter and the first month in the quarter. My monthly values are stored in columns so say for Quarter 1, I would need something like this:
Code:
[mar]/[jan]-1
As you might have thought, the problems come when I have NULL or 0 values. Let me give you a couple of examples.
[jan] is NULL
[feb] is a value
[mar] is a value
Than my formula would have been
Code:
[mar]/[feb]-1
Second case:
[jan] is 0
[feb] is 0
[mar] is 0
In this case I would like the formula return '-' because I want to be able to spot the case in which the quarterly performance is 0 because the initial values were 0 from the case in which the performance was actually 0 (i.e., say [jan] = 101 and [mar] = 101 then performance is 0 which is different from having [jan] and [mar] = 0 thus performance '-').You can combine the three states (NULL, 0, value) with the three months and find many combinations (I have found 27 relevant ones).
I initially thought to use SWITCH in SQL to evaluate the 27 combinations but I found out that SWITCH would evaluate non more than 15 conditions. So I guess the solution should be use a function in VBA which does what I was about to do with SWITCH in SQL.
I have built a test function which evaluates the numerator of my formula using only two cases (CASE 1: all variables have a value <>0, and CASE 2: the first month is null, the second is 0 and the third a value). Here the code:
Code:
Function evaluate_s(Var1, Var2, Var3) As Double
Select Case Var1
Case Is <> 0
[code]....
My main question here is what is wrong in the syntax of my function. Why is not correctly evaluating at least the two cases in my function? I always get 0 for all records.
Modules & VBA :: Dynamic Select Case
Jul 31, 2013I want to know if it's possible to make a dynamic select case. In my form I have 3 separate combo boxes. What I want to happen is depending on what was selected in one the options in the other two change and if you select something in the second the option in the third narrows down again. The user can select these in any order. They can use one by itself or all three independently. In my code I can do this with many select statements, e.g
Select Case Me.combobox1.Value
Case "choice1"
Forms!CurrentForm!combobox2.RowSourceType = "Table/Query"
Forms!CurrentForm!combobox2.RowSource = "SELECT fieldname FROM tablename WHERE fieldname = '" & Me.combobox1.Value & "'"
End Select
In the place I have "choice" is it possible to write something along the lines of,Me.combobox1.value = "SQL Code"..The idea that this code would still work if the user adds more data to the tables which these combobox choices come from.
Modules & VBA :: Using Select Case Statement With Array?
Jun 26, 2014I 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.
Modules & VBA :: Case Select Public Function
Jun 4, 2014I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
'Daylight 6:00am - 4:00pm
'Afternoon 4:00pm - 2:00am
'Midnight 2:00am - 6:00am
Code:
Public Function getShiftForRecord(DelayStart As Variant)
On Error Resume Next
Select Case DatePart("h", DelayStart)
Case 6 To 16 'Daylight 6:00am - 4:00pm
[Code] ....
Modules & VBA :: Select Case On First Two Digits Of A Field
Feb 4, 2014I'm trying to use select case on the 1st two digits of a field. I f it equals 1- then the case is meet but its not working. Will this work or no?
[CODE Case (Left(Me.Step42, 2) = "1-")][/CODE]
Modules & VBA :: Adding Various Values Based On Select Case To Value Of Field
Oct 1, 2014I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.
Code:
Private Sub ProductID_AfterUpdate()
Dim qflPrice As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlQry As String
Dim instID As Integer
[Code] .....
Queries :: SELECT CASE Statement - Populate Each Unit With Assigned Category?
Apr 9, 2013I have a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.
In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".
how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?
General :: Input Data In Lower Case And Automatically Change First Letter Of Word To Upper Case
Mar 16, 2013Is 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 RelatedChange Case To Title Case
Aug 24, 2004hi
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....
Modules & VBA :: Programmatically Find And Select Item In Multi Select List Box
Apr 23, 2015I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies View RelatedForms :: Select Record On Subform To Select Default Value In Combo Box?
May 6, 2014I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?