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.
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")
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)
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,
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.
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
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.
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?
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 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
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.
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?
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
I'm trying to update some field value depending two string field using a function with if and case, function below:
Code: Private Function checkDATI(tipotransazione As String, tipovendita As String) As String Dim r As String r = "" If tipotransazione = "VENDITA" Then Select Case tipovendita
[code]...
And then when I call this function in a command button event as:
I would like to select a case depending on the output of a function.
This function tests the syntax of the reporting month.
If the syntax is fine nothing should be done further in the main sub else it should return to the Input window for the reporting month.
Somehow it doesn't work out.
Code: Public Function RepMonthCheck(rep_date As String) as Boolean If Len(rep_date) <> 6 Or Left(rep_date, 2) > 12 Or Left(rep_date, 2) < 1 Then MsgBox ("Reporting rep_date is not in the correct format = mmyyyy") Return False ElseIf Right(rep_date, 4) > 9998 Then MsgBox ("No forecast available for year 9998")
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.
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
Hi, hope someone can help me. I want to run a query which acts on data in a table which has been imported from excel, where it was somewhat mangled. Basically, the field shows telephone numbers, but the leading zeros on the phone numbers have mostly been discarded. I want to replace the zeros if they're missing. (Here in the UK phone numbers always start with zero). What I'm trying to use is: IIf(Left[fieldname],1)="0",[fieldname], "0" &[fieldname])) But I get errors, I think maybe because of the first comma(?). Can anyone tell me how to achieve this? Any suggestions appreciated.
I have a table of dates that store all the Sundays of a year, as that is what all of our records are based on (how many items an employee sold on the week ending on such and such a date). The table is called WeekEndDates and the column is called WeekEndDate. I want to automate adding a new week, so I have a command button that has the following VB code:
DoCmd.RunSQL ("INSERT INTO WeekEndDates (WeekEndDate) VALUES (DATEADD('D',7, SELECT Max(WeekEndDate) FROM WeekEndDates)")
Any help would be appreciated, I don't know of another way to do this. I'm not sure if this is a SQL question or a VB question, so sorry if this is in the wrong spot.
I have a report where I use numerous DCOUNT functions, and it works fine. But now I need another report which has multiple conditions, and I can't seem to find examples anywhere to get my syntax right. For this example, the field 1 ENJOY in table CONGSITEALL needs to pull out the number of entries for the answer Yes, Definitely, and Most of the Time. I have tried putting the quotes, etc into diffeent places but I just can't seem to find the right combination.
=DCount("[1 ENJOY]","CONGSITEALL","[1 ENJOY] IN ('''Yes, Definitely','Most Of The Time'")
I am trying to using an iif statement to pick up null values, but I don't know what to type in the function. I tried using "is null", but got an invalid message.
What value I should type in to get null value in the function below?
