Modules & VBA :: Easy Way To Write Nested IF THEN ELSE IF Statements
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).
SELECT tblNewTrans.Buyer, Count(tblNewTrans.Buyer) AS ["Total New Leads"], (select count(tblnewtrans.buyer) from tblnewtrans where tblnewtrans.new=true) AS Expr1, (select count(tblnewtrans.buyer) from tblnewtrans where tblnewtrans.refund=true) AS Expr2 FROM tblNewTrans GROUP BY tblNewTrans.Buyer;
can anyone tell me why statement is working correctley. i have a table with a company field, paid field, refund field.
i need to run a query that gives me an amount to records for each company(that bit works), but also tell me how many payments have been recieve(new field) and how many payments have been refunded. all grouped under the correct company name. please help!!!!
I am trying to combine the below queries into one using nested Select statements, but I am not having any luck. Any examples on how to do this would be appreciated. Thanks
qry1000_006_01-Base4 syntax:
SELECT RIVMTRM0_STDY_GRP_TST_REMARK.IVMSGRP, RIVMTRM0_STDY_GRP_TST_REMARK.IVMTESTSN FROM RIVMTRM0_STDY_GRP_TST_REMARK GROUP BY RIVMTRM0_STDY_GRP_TST_REMARK.IVMSGRP, RIVMTRM0_STDY_GRP_TST_REMARK.IVMTESTSN HAVING (((Count([IVMSGRP])<>Max([IVMRMKSN]))=-1));
SELECT [qry1000_006_01-Base4].IVMSGRP, [qry1000_006_01-Base4].IVMTESTSN, RIVMTRM0 _STDY_GRP_TST_REMARK.IVMRMKSN, RIVMTRM0_STDY_GRP_TST_REMARK.IVMRMK FROM [qry1000_006_01-Base4] INNER JOIN RIVMTRM0_STDY_GRP_TST_REMARK ON ([qry1000_006_01-Base4].IVMSGRP = RIVMTRM0_STDY_GRP_TST_REMARK.IVMSGRP) AND ([qry1000_006_01-Base4].IVMTESTSN = RIVMTRM0 _STDY_GRP_TST_REMARK.IVMTESTSN);
I have a report based on a query and in that report I added a text box to be yes or no depending on 1) the number of days to complete and 2) the type of urgency. There's 3 different urgency levels so I was trying to create iif statements based on each one with the corresponding days using the OR operator between them like below:
=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)","Yes","No")_ Or IIf([t_Days]<=7 And [Urgency Level]="Normal (5-7 days)","Yes","No")_ Or IIf([t_Days]<=30 And [Urgency Level]="Low (14-30 days)","Yes","No")
Any 1 of those by themselves works fine but I need it based on all of that. So with that said, is there a way to do this? I tried using VBA code, but I think with the type of report I'm running that that wont work. It has a repetitive one line detail section with the data I'm trying to view.
Hello all I was wondering if there is limitation to the number of IIf statements that can be nested in a calculated field. Basically I need to assign a particular ‘label code’ to a record that will be derived from data with in several fields within the query. The data from the table (that the query is derived from) will be completely deleted and recreated on occasions, meaning I can not write data back to the table as it would be lost when the re-creation happens. There is a possibility of many derivations for the ‘label code’ (maybe up to 100) so the nested IIf code will be substantial. If there is a better way to do what I want I am all ears however I am not a VB programmer so would rather stick to doing within the query design grid, or SQL at a stretch. Anyway the main focus to the question is there a limitation to the number of nested IIF statements.
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** ************* SELECT TABLE1.ID, IIf([TABLE1]![STAGE]="D", IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1", IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table.
Ok I'm building a Text Search box that "Live Filters" the results in a ListBox control on the same form.
After typing in a value (Say, for Customer First Name like "Steven") I want the code to do the following:
1. If there is no value in the listbox that matches when the user presses the enter key....open the New Customer form - THIS WORKS
2. If there is a single value in the list, then open this record in the customer form when the user presses the Enter key Receiving a Syntax Missing Operator error on this line:
However, I use this exact code on a button elsewhere in my project (NOT within an IF Function) and it works perfectly fine!
Code: Private Sub txtSearch_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyReturn Then If Me.ListCustomers.ListCount = 0 Then CmdNewCustomer_Click
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
I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items.
My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. How to force the combobox to requery through code in a module?
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen FROM voorwerp (inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp) inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau where rubriek in (select rubrieknummer from rubriek where rubriek = variable name ) group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
I need to use DMax to open the form on the last record based on the serial number. This code is what I have to open the form currently, but I want only the latest record related to the serial number:
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
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.
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
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
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?
I have header table and a distribution table that both need to be written to a txt file. They need to be written in a certain order Header. Distribution. Header. Distribution. etc. There can be multiple distribution lines for the header line.
So far this is what I'm trying and its not working right. ~Legend~ hrst = header record set drst = distribution record set tblHeaderExp = header table exporting from tblDistExp = distribution table exporting from ~End of Legend~
I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.
If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.
The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.
Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.
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 "''"
I have the requirement to write EACH record from a table to its own CSV file with name of the file being combination of 2 fields
So let's say I have Table1 with 3 columns (Field1, Field2, Field3) with following content :
Field1 Field2 Field3 AA 1 ABC AA 2 DEF AA 3 GHI
I should get 3 files with names AA1.CSV, AA2.CSV and AA3.CSV and each file contains its respective row from the table.
I tried to do it with DAO Recordset, but I do not find a way to write only the current record from recordset while looping.
See below the code I was using, but issue is that code does succesfull creates the 3 CSV files as per above example, but in each file it writes ALL 3 ROWS instead only the respective ROW.
Code: Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strFilename As String
I have a function to write some data to a text file (as an activity log, separate to the live data contained in the DB)
It uses the FileSystemObject.OpenTextFile method and works fine
However, I'm conscious that, although perhaps unlikely, there is the possibility (which is enough for me to worry about it) that multiple users could trigger the function simultaneously, which could cause a conflict when multiple FE's try to write to the same log file at the same time.
So I'm trying to add some code to prevent this from happening, by checking if write-access is available before proceeding.
Below is the piece of code I've added. Was considering, as an alternative, looping until the objFile is no longer Nothing, rather than depending on the Err.Number?
Code: Dim objFSO As Object Dim objFile As Object On Error GoTo ErrorHandler ... Set objFSO = CreateObject("Scripting.FileSystemObject")
