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).
View Replies
ADVERTISEMENT
Oct 4, 2007
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!!!!
the new and refund fields are checkboxes.
View 3 Replies
View Related
Oct 30, 2007
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);
View 1 Replies
View Related
Feb 24, 2005
Hi folks,
I have a query which queries another query. I need to re-write the whole thing in SQL, but struggling with incorporating the nested query.
here's the main query:
SELECT tblProductSubCategory.SubCatDesc AS Products, qrySummaryStage3.[Early Cash Sales], qrySummaryStage3.[Late Cash Sales], qrySummaryStage3.[Cash Variance], qrySummaryStage3.[Cash Variance %], qrySummaryStage3.[Early Credit Sales], qrySummaryStage3.[Late Credit Sales], qrySummaryStage3.[Credit Variance], qrySummaryStage3.[Credit Variance %], qrySummaryStage3.[Early Total], qrySummaryStage3.[Late Total], qrySummaryStage3.[Variance Total], qrySummaryStage3.[Total Variance %], tblBudgetNew.Amount AS [Late Budget Amount], [Late Total]-[Late Budget Amount] AS [Budget Variance], qrySummaryStage3.[Early Cash Margin %], qrySummaryStage3.[Late Cash Margin %], qrySummaryStage3.[Early Credit Margin %], qrySummaryStage3.[Late Credit Margin %]
FROM (qrySummaryStage3 INNER JOIN tblBudgetNew ON qrySummaryStage3.SubCatID = tblBudgetNew.SubCatID) INNER JOIN tblProductSubCategory ON qrySummaryStage3.SubCatID = tblProductSubCategory.SubCatID
WHERE (((Month([date]))=1) AND ((Year([date]))=2005) AND ((tblBudgetNew.Site)=[Enter Site ID]) AND ((tblBudgetNew.Type)="Monthly Breakdown"));
and here's the nested query , called "qrySummaryStage3":
SELECT tblProductSubCategory.SubCatID, tblReportTemp1.[Early Cash Sales], tblReportTemp1.[Late Cash Sales], [Late Cash Sales]-[Early Cash Sales] AS [Cash Variance], IIf(nz([early cash sales],0)=0 Or [Early Cash Sales]=0,0,[Cash Variance]/[Early Cash Sales]) AS [Cash Variance %], tblReportTemp1.[Early Credit Sales], tblReportTemp1.[Late Credit Sales], [Late Credit Sales]-[Early Credit Sales] AS [Credit Variance], IIf(nz([Early Credit Sales],0)=0 Or [early Credit Sales]=0,0,[Credit Variance]/[Early Credit Sales]) AS [Credit Variance %], [Early Cash Sales]+[Early Credit Sales] AS [Early Total], [Late Cash Sales]+[Late Credit Sales] AS [Late Total], [Late Total]-[Early Total] AS [Variance Total], IIf([Early Total]=0,0,[Variance Total]/[Early Total]) AS [Total Variance %], IIf([Early Cash Sales]=0,0,([Early Cash Sales]-[Early Cash Cost])/[Early Cash Sales]) AS [Early Cash Margin %], IIf([Late Cash Sales]=0,0,([Late Cash Sales]-[Late Cash Cost])/[Late Cash Sales]) AS [Late Cash Margin %], IIf([Early Credit Sales]=0,0,([Early Credit Sales]-[Early Credit Cost])/[Early Credit Sales]) AS [Early Credit Margin %], IIf([Late Credit Sales]=0,0,([Late Credit Sales]-[Late Credit Cost])/[Late Credit Sales]) AS [Late Credit Margin %]
FROM tblReportTemp1 RIGHT JOIN tblProductSubCategory ON tblReportTemp1.ProductSubCatId = tblProductSubCategory.SubCatID;
Thanks in advance!
View 4 Replies
View Related
Dec 2, 2014
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.
View 4 Replies
View Related
Jan 22, 2008
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.
Example preliminary test code:
Thanks in advance for any help provided regards.
Daryl
View 12 Replies
View Related
Mar 25, 2013
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",
[Code] .....
View 1 Replies
View Related
Jul 31, 2013
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.
Code:
SQLstock1 = "UPDATE TBL_STOCK SET Stock = '" & DLookup("RemainingQty", "QRY_STOCK", "ActionEntity = '" & Me.cmb_customer1 & "'" And "StockType = '" & Me.cmb_stock_type1 & "'") & "' WHERE StockEntity = '" & Me.cmb_customer1 & "'"
View 1 Replies
View Related
May 24, 2014
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:
Code:
DoCmd.OpenForm "frmContacts", , , "[ID]=" & Me.ListCustomers.Column(0)
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
[Code] .....
View 2 Replies
View Related
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 1 Replies
View Related
Jul 8, 2015
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?
View 8 Replies
View Related
Jun 1, 2014
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 ?
View 5 Replies
View Related
Sep 19, 2013
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:
Code:
DoCmd.OpenForm "Form1", , , "incoming_module_sn = '" & Me.txt_sn & "'"
Here is what I tried and it did not work:
Code:
DoCmd.OpenForm "Form1", , , DMax("incoming_module_sn", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'")
View 11 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
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
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
Jul 22, 2013
I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.
Code:
Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database
[Code]......
View 5 Replies
View Related
Jul 7, 2015
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~
[Code].....
View 6 Replies
View Related
Aug 3, 2015
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.
View 3 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
Jun 22, 2015
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
[Code] ....
View 10 Replies
View Related
Jan 20, 2014
how to write a vba msgbox with two lines i access 2013
i try to put chr(13) and alse i used & vbCrLf& but nothing is worked
they seems to be for an earlier version ...
View 6 Replies
View Related
May 21, 2015
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")
[Code] .....
View 3 Replies
View Related