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 Replies
ADVERTISEMENT
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.
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 RelatedModules & 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.
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?
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].....
Case Statement
May 23, 2005Hi,
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,
Case Statement
Dec 19, 2007Hello, 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:
CASE Function Or IF Statement
Feb 28, 2006Hi 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.
LESSON TYPE ----------- COST
Beginner 1hour ----------- 15
Beginner 2hour ----------- 29
Beginner 3hour ----------- 42
Test Retake 1hour -------- 13
Test Retake 2hour -------- 25
Test Retake 3hour ---------36
Disqualified Retake 1hour -- 25
Disqualified Retake 2hour -- 49
Disqualified Retake 3hour -- 72
Advanced 1hour ---------- 10
Advanced 2hour ---------- 19
Advanced 3hour ---------- 27
Is it possible to do that, change the cost correpsonding to what one is entered in lesson_type...??? And where do i put it??? What do i do with it??
Thanks, dragon2309
CASE WHEN Statement Problem
Aug 8, 2007I 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.
Case Statment To If Statement
Apr 29, 2008Here 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
Thanks for your help!
Sql Case Statement In Access??!?
Jan 12, 2006hey
Is it possible to use sql case statements in access ?
I have used them in sql server all the time and they are really useful!
I mean statements like this one:
"SELECT iOrderID,dOrderDate,iTotalBeforeVat,iShipTotal,iVa tTotal,iTotal,
CASE sOrderStatus WHEN '9' THEN 'Canceled' END FROM tTmpOrderRpt"
thanks!
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
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
Syntax Error -- Case Statement In Query
Jan 16, 2008Hi,
I received syntax error for the following statement
SELECT
CASE [File Type]
WHEN 'Security' THEN 'SEC'
WHEN 'Admin' THEN 'ADM'
END AS FT
FROM [tbl_Core Non-Core]
please advise
thanks
Alice
Queries :: Update Statement For A OUTER JOIN Select Statement
Feb 12, 2014I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
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?
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
Is 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
Modules & VBA :: Trying To Get Case Statement To Recognize Multiple Conditions
Sep 8, 2014I 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
[code]...
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..
Specific 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