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
Hi all, I need to extract in a query a recent range of records, these records should be all before a certain date and all the following future records example:
if today date is 12/12/2007 I need to extract all records that are dated from the 1st of november and all the records that come after the 12/12/2007.
In the criteria I have set "Date()" what else should I be adding to get the range mentioned above?
Thanks
I have a table that stores price records with a field for the effective date but there is no field for the inactive date. How can I write a query to return the price for a date that falls between recorded effective dates?
Example: A record exists for 12/03/2005 and the next is 01/04/2005. How can I find the effective price on 18/04/2005?
Thanks
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].....
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
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
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
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
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
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
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 RelatedI 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
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
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
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 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..
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 RelatedMy 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.
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.
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.
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] ....
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]
I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.
View 2 Replies View RelatedI 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] .....
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?
so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd
FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo
WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]))
ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!