Modules & VBA :: Trying To Get Case Statement To Recognize Multiple Conditions
Sep 8, 2014
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
[code]...
View Replies
ADVERTISEMENT
Sep 18, 2013
I have used the code below to send an email automatically when a new record has been created. However I recently added the If Then statement to the code as you can see. The problem is that the email wont send because it says "There must be at least one name or distribution list in the To Cc or Bcc box". It is most likely saying this because it doesn't recognize "IT" in the txtDeparment combobox. How can I get it to recognize IT in the department combobox? The reason I am using an If Then is because I will make it send to different email addresses depending on what is in txtDepartment by using ElseIf
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
If Me.txtDepartment = "IT" Then
.To = "my email"
End If
.Subject = "hi"
.HTMLBody = "hi"
.send
End With
View 2 Replies
View Related
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
Nov 6, 2013
how to do multiple conditions, Nested....I have 6 conditions that i like to check,
Code:
if (condition-1) And
(condition-2) And
(condition-3) And
'[code]....
View 8 Replies
View Related
Sep 10, 2014
I'm trying to create an If/Then statement that needs to match to multiple values. My initial thoughts were to create a list but that doesn't seem to work the way I thought it would.
For example:
If Me.Field = 1,2,5, or 8 Then
Do 'X'
Else IF Me.Field = 3,4,6 or 7 THen
Do 'Y'
Else Do 'Z'
I haven't been able to get the syntax to work right. I'd rather not have to do 'Me.Field = 1 or Me.Field = 2, etc.' but if that's the only way to have it work right, then I guess it'll have to do.
View 6 Replies
View Related
Sep 22, 2014
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 Related
Oct 11, 2013
I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.
All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.
What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.
For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.
Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.
Here is some code that I have on the Report button:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.
If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.
I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.
Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
or
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
View 3 Replies
View Related
Jul 31, 2015
Details:
I have a Profile form that tracks the expiration date for each client's various certifications. These dates are set up in the Short Date format in the table design of Access.
Problem:
When a user accidentally presses an alphabetic key while updating an expiration date, an Access error message is triggered. This is confusing to my users as these messages are written in Access lingo. I would rather that nothing occurs at all. I wrote a case statement to disable each letter of the alphabet and applied it to the On Key Down Event for each expiration date control on my Profile form to solve this problem, but this must be applied to 28 separate controls. I would rather call a function that disables alphabetic keys for each date control in my form when called.
Questions:
How do I transform my Disable Alphabetic Keys Case Statement into a function that I can call for each expiration date control? I know that when writing a function certain variables have to be declared and/or initialized.
Also, will I need to create a function to re-enable alphabetic keys or is this unnecessary because the disable alpha keys function will only be called for specific controls, not the entire form?
What I Have Tried:
I have tried copying and pasting my Disable Alphabetic Keys Case Statement into a module to attempt to create a function, but it needs work.
Below I have included 2 types of code:
(1) The original On Key Down code applied to each date control on my form
(2) The same code written as an attempt at a function
Original Profile Form Code to Disable Alphabetic Keys in the On Key Down event for each date control
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
'All message box text is for me to test the code, not for the user to see
Case vbKeyA
MsgBox ("you pressed the A key")
[Code] .....
View 14 Replies
View Related
Aug 19, 2013
I cannot get the correct syntax for this. The punctuation for the second where condition is incorrect.
Code:
Me.cboDose.RowSource = "SELECT Label FROM tblLookup WHERE tblLookup.[Med]='" & Me.cboMed & "'" AND tblLookup.[Notes] = "Swallowed"
View 3 Replies
View Related
May 23, 2005
Hi,
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,
View 2 Replies
View Related
Dec 19, 2007
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,
FROM Exercise1;
:confused:
View 9 Replies
View Related
Feb 28, 2006
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.
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
View 4 Replies
View Related
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
Aug 8, 2007
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.
View 4 Replies
View Related
Apr 29, 2008
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
Thanks for your help!
View 5 Replies
View Related
Jan 12, 2006
hey
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!
View 4 Replies
View Related
Sep 24, 2013
Why can't I get this simple piece of code to work?
Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = Null Then txtLoaned = 1
End Sub
Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = "" Then txtLoaned = 1
End Sub
View 4 Replies
View Related
Dec 1, 2014
I'm designing a form which will display a lot of employee information in a tabular layout. Users need the option to update a lot of these controls, but I want them to do that on a separate form. I'm wondering if VBA can determine the position of a clicked button within the tabular layout.
So if I have a layout like this:
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
Can I have some code which determines that, for example, the second button across on the 3rd row is clicked and read the corresponding label?
I was planning to have each button just call a function passing an argument, but since all these buttons will be labelled "update" it could get a bit confusing if I need to re-arrange the controls. So I wondered if what I describe above is possible, and which VBA functions I would need to look into if so?
View 7 Replies
View Related
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
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
Oct 1, 2013
I have the following code on an After Update event:
If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then
Me.ProgPriceTxt.Value = "85"
End If
This works fine. When I add another "And" to the statement, however, it no longer functions:If
Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then
Me.ProgPriceTxt.Value = "85"
End If
Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).
I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement.
View 11 Replies
View Related
Jan 16, 2008
Hi,
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
View 3 Replies
View Related
Apr 14, 2015
I'm trying to create a report that does the following:
If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.
If Not IsNull(Me.Time1) Then
If Me.Time1 = "Other" Then
Me.Time1.Visible = False
Else
Me.Time1.Visible = True
[Code] ......
View 6 Replies
View Related
Feb 25, 2015
I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:
Code:
strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3
You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.
Is there a simple way to build this string based on the number of search strings that are not null?
View 2 Replies
View Related
Apr 10, 2013
Is it possible to set multiple conditions across multiple tables using OR in a where clause? For instance, can you run where......
(table1 = 'test') or (table2 = pass) or (table2 = "fail) AND (class = 'SQL');
View 1 Replies
View Related
Dec 10, 2014
I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.
[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.
Dim maxFlag As String
Dim flagCriteriaWarranty As String
Dim flagCriteriaEstimate As String
[Code] .....
View 13 Replies
View Related
