Topics Search

IF-THEN-ELSE Statement

IF-THEN-ELSE Statement
Views: 244
This MSAccess tutorial explains how to use the Access IF-THEN-ELSE statement with syntax and examples. The Microsoft Access IF-THEN-ELSE statement can only be used in VBA code.
Sponsored Links:

More topics


Case Statement

This MSAccess tutorial explains how to use the Access Case statement with syntax and examples. The Microsoft Access Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.

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.

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:


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


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.