Is Select Case The Correct Way To Do This

Mar 14, 2008

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*"

Hope someone can help with this one

Thanks in advance

View Replies


ADVERTISEMENT

Modules & VBA :: Getting Error End Select Without Select Case?

Sep 5, 2014

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

[Code].....

View 3 Replies View Related

Using Select Case

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

Need A Little More Help With A Select Case

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

Help With IF/Then/Else Or Case Select...I Think

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

Select Case

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

Select Case Question

Oct 2, 2005

Greetings 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

View 4 Replies View Related

'Select Case' Problem

May 22, 2006

Hi,

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

View 14 Replies View Related

CASE Statement In SELECT

Mar 21, 2007

Does 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 Related

Multiple Select Case

Mar 30, 2006

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

End Sub

View 8 Replies View Related

Case Select To Run Queries

Oct 27, 2005

Please 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

View 2 Replies View Related

Combo Box To Select Correct Record On FORM

Dec 1, 2007

I have database of roughly 15000 records. The records have a unique number and the majority of searches etc work OK. The problem is with a form used to display record details with various sub forms embedded on the form.
Scrolling through displays the correct details.
Trying to use a combo box to determine the record selected does not work correctly.
The combo box is made up of 4 columns of "NAME", "COUNTRY", "ROLE" and "UNIQUE NUMBER"
Typing in the combo box auto fills in the NAME until the correct one is found. If the name is unique it opens on the form correctly. But if the name is not unique it does not always open the correct record.
Is there any way of being able to use the NAME to search through the combo box entries but use their unique number to determine the record selected.
A further problem arises as there are apostrophes in the NAME as well

View 3 Replies View Related

Not The Best At Case Select Statement Writing...

May 14, 2007

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.

View 6 Replies View Related

Order Of Events For Select Case

Oct 2, 2005

I 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..

View 3 Replies View Related

Select Case Statement - General Q

Feb 6, 2008

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) 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 Related

Specific Date For Select Case

Apr 3, 2006

OK 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

View 6 Replies View Related

Modules & VBA :: Select Case Function

Aug 17, 2014

My 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.

View 8 Replies View Related

Modules & VBA :: Dynamic Select Case

Jul 31, 2013

I 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.

View 6 Replies View Related

Modules & VBA :: Using Select Case Statement With Array?

Jun 26, 2014

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.

View 7 Replies View Related

Modules & VBA :: Case Select Public Function

Jun 4, 2014

I 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] ....

View 11 Replies View Related

Modules & VBA :: Select Case On First Two Digits Of A Field

Feb 4, 2014

I'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]

View 10 Replies View Related

Queries :: Select Query - Count How Many Records Not In Correct Format

May 7, 2013

So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format

The format is 10 digits and it must start with a 0

Using Access 2010

View 2 Replies View Related

Modules & VBA :: Adding Various Values Based On Select Case To Value Of Field

Oct 1, 2014

I 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] .....

View 5 Replies View Related

Queries :: SELECT CASE Statement - Populate Each Unit With Assigned Category?

Apr 9, 2013

I 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?

View 1 Replies View Related

Subform Correct Answer Adds To Number Correct In Main Form

Mar 2, 2012

The code I have is.

Code:
Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
DoCmd.FindNext
End Sub

Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.

View 4 Replies View Related

General :: Input Data In Lower Case And Automatically Change First Letter Of Word To Upper Case

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







Copyrights 2005-15 www.BigResource.com, All rights reserved