Modules & VBA :: Using If And Case Statements
Dec 16, 2013
Basically I want to check if the Reporting Month already exists in the Months Table. If it exists it should ask the User wheter to overwrite or not. If the User clicks on OK it should delete one record from Months Table if the User clicks no, nothing should happen.However it doesn't delete the record, when I click on OK.
Code:
Public Sub IMPORT_Click()
Dim filelocation As Variant
Dim f As Object
Dim Message As String, Title As String, Default As String
Dim sql As String
Dim MsgReply As Integer
[code]....
View Replies
ADVERTISEMENT
Jan 24, 2008
I have a field in my main form that shows values of BMI which is calculated from the users height and weight which they entered on a form attached to the same table. I have a query called BMICalculation which is attached to this field using =DLookup([BMI Calculation, "BMI Calculation"] etc
However for my other variables which I have applied case statements to so I can automatically populate other text field boxes, I cant do it for this can I since its not a value that is stored anywhere? How can I automatically populate a text box in a report if the field is based on a query?
View 1 Replies
View Related
Feb 27, 2015
The following SQL statements run with no errors, but I can't see the output of the SQL statements. What is wrong with my "Debug.Print" or "rs" statement ? I would like to see the results. The SQL statements works when I save them to a query.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Rem Dim intResult As Integer
Dim strSQL As String
Set db = CurrentDb
[Code] ....
View 4 Replies
View Related
Oct 15, 2013
I've seen a common solution is to double up the apostrophe, replacing ' with " to avoid the special character however i also hear of another solution using "parameters".I tried have a look around on the internet and got confused so I thought maybe it would be better to ask here.
I just want to change my INSERT INTO and UPDATE commands to be able to use this (to prevent a potential sql injection) also i think its important to know im using DOA to code? (not sure if its important) either way.
View 3 Replies
View Related
Jun 26, 2013
I'm trying to put multiple IF statements into one procedure like the below:
Dim Answer As Integer
Answer = MsgBox("Have You Selected The Correct Outcome?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then 'Works fine here
'The bit I can't get right
[Code] .....
View 2 Replies
View Related
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
Aug 31, 2013
I have a table of marks of student, a query that calculates totals and a form that displays results. on the query, i have an if statement.
Code:
comment:IIf([Marks]>=80,"good",IIf([Marks]>=50,"fair",IIf([Marks]>=40,"Work harder")))
So i want to be able to make the whole syntax available for user to edit without actually doing it in vba. is this possible in vba? i have attached an image
View 8 Replies
View Related
Aug 20, 2013
How do I combined multiple OR statements in conjunction with an AND statement as follows:
If [Len(Field1 & "")=0 OR Len(Field2 & "")=0] AND Field 3 = "Something" AND Field 4 = "Something" Then...
I thought there I had to use a | symbol but I am not exactly sure?
View 2 Replies
View Related
Apr 12, 2015
How do you write complex nested IF THEN ELSE END IF statements..How do you know what IF statement to put first?Then how would you know where to put ELSE, or END IF?Before you start with the VBA, what are the foundations for making it work successfully, know where you are, know where to put END IF etc?
I've seen many times when using VBA people set up an initial foundation for writing code. For example when needing to put something in quotes, they do "" then enter the text in between, so they don't get errors on running (A very simple, crude example I know, but is there a similar basis for nested IFs).
View 6 Replies
View Related
May 8, 2015
I have coded a select statement that uses multiple tables for information used to populate a report. It returns the correct information, sort of, but it doesn't recognize the already existing table relationships so I get dupicate records. Is there a way to run the code so that it recognizes the table relationships?
View 1 Replies
View Related
Jan 22, 2014
I have a check for lots of different data in a string and wondered if i can use CASE or similar.Sample code reads...
If InStr(1, Me.txt_sp, "Give & Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "give and take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give and", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority working", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority boards", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp", 1) Then TM = "2 Way"
View 12 Replies
View Related
Dec 4, 2014
I'm having problems with quotation marks in a sql statement. The string is an array separated by a semicolon.
120/80;70;5'6";125
this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.
I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:
original string: 120/80;70;5'6";125
strPreOpVits = "120/80;70;5''6'"';125"
mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _
"WHERE nID = " & myRecID
docmd.runsql mysql
I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error.
Here is the code that I use to convert the original string
Public Function FixQuotesInSql(strToFix As String)
Dim lgth, y As Long
Dim strTemp, char2Add As Variant
'This routine fixes the use of apostrophe and quotation marks in an SQL sequence
'If the apostrophe is at the beginning or end of the string it replaces with 3 x "'" or "'''"
'If in the middle of the string then replaces with 2 x "'" or "''"
[Code] ....
View 7 Replies
View Related
Jul 12, 2013
I want to make this bit of code below case sensitive :
Code:
SQL_Entire = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "*';"
SQL_Beginning = "WHERE [" & ReplaceStringSelectedField & "] LIKE '" & SearchedString & "*';"
SQL_End = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "';"
View 14 Replies
View Related
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
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
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
Feb 2, 2014
I have two queries called "Query_match_AND" and "Query_match_OR". I want to show the total number of records for either of these queries in a form called "form_candidates_result" depending upon the combo box value (either "AND" or "OR") in a form called "form_match".If I put
Code:
=DCount("*","Query_match_AND")
in the control source of [candidatecount] in "form_candidates_result", the value is shown in the form and it works fine.If I use the following code in either of the "on load" or "on open" events in "form_candidates_result"...
Code:
Select Case [Forms]![form_match]![ANDOR]
Case "AND"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_AND")
Case "OR"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_OR")
End Select
it doesn't work and I get
Code:
#NAME?
View 7 Replies
View Related
Jan 20, 2014
I'm new to access vba and I'm trying to create a login form. I have already my code to login but i want to validate the password in 'case sensitive' basis. Below is only what I've got so far.
Code:
Private Sub cmdLogin_Click() Dim login_validation As Variant login_validation = DLookup("Password", "tblLogin", "Username='" & Nz(txtUsername.Value, "") & "'") If Nz(login_validation, "") <> Nz([txtPassword].Value, "") Then MsgBox "Incorrect Password. Please try again." txtUsername.Value = "" txtPassword.Value = "" txtUsername.SetFocus Else MsgBox "Hi " & txtUsername.Value & "," & vbNewLine & vbNewLine & "you have successfully login!" DoCmd.OpenForm "frmMain" End If End Sub
View 5 Replies
View Related
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
Jun 10, 2015
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:
Code:
MsgBox (checkDATI(Me.CausaleMov, Me.txt_tipomov))
It's not update those field.
View 6 Replies
View Related
Jan 3, 2014
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")
[Code] ....
View 3 Replies
View Related
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
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 1 Replies
View Related
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
Jul 7, 2015
I need to make a query that will show the oldest cases and then open this one in the main form. It will be possible that there could be several cases with the same date. For example the query runs and the results are 7, how could I get one of those cases in the main form.
The idea is that a person will click on a button called "next case", the query will run and then it will open the case in the main form.
How to get this started?
View 7 Replies
View Related
Aug 24, 2004
hi
help is needed yet again :-)
I know when creating a text field in the format option
you can use the > or < sign so that when text
is entered it automatically changes it to uppercase or
lowercase - but i need it to be Title Case, any one know
how I can do this....
View 11 Replies
View Related
