IF-THEN-ELSE Statement

In Access, the IF-THEN-ELSE statement can only be used in VBA code.
I have a query that based upon Teacher Reg will pull up if they opted for a district, they have checked to teach in a particular area, and if they are certified in a specified area.

I have all that working. From here, I want to see if they have other certifications along with the one they opted for. So, I have a nested select statement in an OR clause to pull up theinformation in an IIF statement based on if the checkbox I made is checked or not checked.

I have this statement for my IIF Statement:

Sponsored Links:

Related Topics

Complex IIF Statement
I have a query that based upon Teacher Reg will pull up if they opted for a district, they have checked to teach in a particular area, and if they are certified in a specified area.

I have all that working. From here, I want to see if they have other certifications along with the one they opted for. So, I have a nested select statement in an OR clause to pull up theinformation in an IIF statement based on if the checkbox I made is checked or not checked.

I have this statement for my IIF Statement:


Select Case statement
I have a Select Case statement that populates a text box based on the user's selection from a list box. It works fine as long as the value selected is one listed specifically in the case statement.

But several values require exactly the same formula, and rather than listing each of them out, I thought I could use Case Else to call this for the ones that did not meet the criteria.

When I do this, however, the value does not change. Here is my statement.


Microsoft Access Select Case Statement
Microsoft Access is a very versatile data-handling tool. There are numerous control statements that turn the program flow based on the value of certain expressions. Such statements are the Select Case statement, If...Then...Else statements, the For Loop etc.
The Select Case statement is the multiway branching statement. If the probable actions based on the value of a variable are numerous, this statement is used. It is an easy way to send execution to different parts of the code depending on an expression.


How To Use An SQL Statement In A TransferSpreadsheet Command
I have some some data I would like to output to an excel ss using a SQL statement in my code. Problem is, I don't know how to refer to that sql statement in the transferspreadsheet statement.

Below I have an example. The strSQL variable in the transferspreadsheet statement is obviously incorrect, but just placed to show what I want.

I know I could do it by creating a table and then referring to that created table in the transferspreadsheet function, but would like avoid this if possible.

strSQL = "SELECT table1.SUBID from Table1 where table1.loc = somecriteria"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strSQL, filepathandname, True


Multiple iif statements
I am trying to combine two iif statements and I am trying enter the correct iif statement for it. This is what I have so far.

Original iif statement that exists
...

I am trying to add another iif statement to the existing iif statement with the following methodology.
Iif original lock does not fall within the filter below, then change [ORIGINAL_LOCK] to [new_App_DT] otherwise leave as [ORIGINAL_LOCK]


Use a SQL statement to delete data
Writing SQL to delete data from an MS Access table is just about the easiest SQL statement you can write.
This example will use the SQL statement to create an Access query, but you can use the statement in a macro or VBA as well.


Case Statement
In Access, the Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.


Nested IIf Statement
I made a database many years ago for my business. I built it just after taking a class on Access, so it was all fairly simple for me. Now, over 9 years later, I have to make a simple change to an If statement and I can't seem to get it right.

I use it to calculate my commission percentage based on my current pay plan, which was recently changed. Here is my trusty old statement:

[code]

Attempting to keep the old information, and add one more criteria, I tried nesting a second If statement in, to look like this:

[CODE]

The problem is, the results that are returned, even for deals after June 1, are still showing the old calculation. How can I fix this?


Write an UPDATE Statement in SQL
At its most basic level writing an UPDATE statement in SQL is a very straightforward task. Below I will detail the three core sections that make up an UPDATE statement.


GROUP BY sql Statement
I have the following sql statement but when I add the group by portion to the entire statement it is giving me an error and not running.


IIF statement using a date
I have a date field in a table and I want to write an IIf statement in a query wizard using that date to get another date field.

The date field I already have in one of the tables is SaleDate. I need for the IIF statement to give me First day of the month that the file goes to sale (will always be mm/dd/y format)

Lets just say the saledate is 05/09/2011 | I need the iif statement to give me 05/01/11. Its always the first day of the saledate month

I would start the iff statement with this IIf[SaleDate] ---but then I don't know the rest of it.


IIF statement using a date
I have a date field in a table and I want to write an IIf statement in a query wizard using that date to get another date field.

The date field I already have in one of the tables is SaleDate. I need for the IIF statement to give me First day of the month that the file goes to sale (will always be mm/dd/yyy format)

Lets just say the saledate is 05/09/2011 I need the iif statement to give me 05/01/11. Its always the first day of the saledate month

I would start the iff statement with this IIf[SaleDate] ---but then I dont know the rest of it


Should we put SELECT statements inside a transacti
begin trans
update items statement
inset order statement
select @@identity statement
select * from items where statement
commit trans

My first question is should we put the two SELECT statements inside or outside commit trans?

My second question is do we need a transaction for just SELECT statements like the following?

begin trans
select 1 statement
select 2 statement
select 3 statement
commit trans

I think only action queries can be a transaction.


Simple IIf statement I cant seem to get working!
I am a beginner to MS access and I am struggling with my first IIF statement. What I need to do is compare month by month sales for 2010 and 2011 and insert text to say if the sales are increasing or decreasing for that particular month. I have managed to do thatfine for the first 6 months using the below statement.

My IIF Statement
a: IIf([Diff]>0,"decrease","Increase")

My IIF statement is based on the difference column I inserted (see below) Diff: [Complete payments_Crosstab]![2010]-[Complete payments_Crosstab]![2011]


Long IIf Statement with Or statement inside
I have a long IIf statement that I am using in a text box to generate some totals at the bottom of a report. This is it below:

=Sum(IIf([Data Table]![Order Phase]="OE" And [CheckHardSale]=0 And [CheckCredit]=0 And [Afterclose Status]="Dormant",1,0))

I want to add in an Or statement inside -

OR [Conflict Phase] = "AAM","QA",Is Null

I cannot seem to get the paratheses and brackets right. Can someone straighten me out


RunSQL statement
Can anybody tell me why this SQL statement won't run in in the RunSQL macro action.

SELECT Open_Issues.* FROM Open_Issues WHERE ((Open_Issues.Account_No)=(Forms.Open_Issues_Viewe r.Text64));

It tells me that the RunSQL action must have an SQL statement for an arguement when I try to run the macro. I thought that was a perfectly valid SQL statement considering I checked it with a query


Msg Dialog Box Prompt
Request some assistance,

I created a button with the following vba action statement

Private Sub Refreshtxttrig_Click()
DoCmd.RunSQL "drop table [Table Name]"
Exit_CmdImportExcel_Click:
End Sub

(This statement deletes a specific table within access.)

It works well, except when the Table is missing or I accidently click twice, it executes the statement but brings up a "runtime error." instead.

I would like to create a statement that will bring up a MsgBox instead of a runtime error and tells the user that the Table has already been purged


if statment or case statment?
If statement or case statement? I want to write an if or case statement that opens my form depending on the criteria. I have tried both neither work.


Macro print IF statement problem
I have a program that prints out Work Orders. The original person who set all this up made a MACRO to do this. (See image)

I need this print command to access an if statement and IF TRUE I need it to print another report.

I don't know how to get an IF statement in the Original MACRO.


If and only if statement, to fix overflow error
Im trying to produce an if and only if statement where if only does the calculation if the denominator is not zero.

Where did I go wrong?

Calc: iif(
[listpriceorig]<>0,
[listprice]/