1

If-Then Statement

EXCEL

In the results cell, type:
=If(C4=3,2,4)

2

If-Then Statement

ACCESS

In a created query field, type:
IIf([FieldName]=3,2,4)

Note the two "I"s in "IIF".

3

If-Then Statement

BRIO

Formula Definition Box:
if (FieldName == "One" ) {1 } else {0 }

If-Then Statement

Formula Definition Box:
=if([Model]="Mustang" ; "Ford Car" ; "Non-Ford" )

5

If-Then Statement (Nulls)

BRIO

Formula Definition Box:
if (MCO ==Null ) {'Null' } else {'Not Null'}

6

If-Then Statement
(With Wildcard)

BRIO

Formula Definition Box:
=If ( Match ( [Lienholder Field] ; " *LEASE* " ) ; "Leased Car" ; "Not Leased Car" )

7

Multiple If-Then Statements

ACCESS

In a created query field, type:
IIf([FieldName]="Y","A Is Y",IIf([FieldName]="N","A Is N",IIf([FieldName]="X","A Is X","None")))

8

If-Then
(Multiple Conditions)

BRIO

Formula Definition Box:
=If ( [Model]="Mustang" ; "Ford Car" ; If ( [Model]= "Camaro" ; "Chevy Car" ; "Non-Ford/Chevy" ) )

9

If-Then Statement
(Multiple conditions & Choices)

BRIO

Formula Definition Box:
=If ( [Make] InList("Ford" ; "Chevy" ; "Dodge" ) ; "American Car" ; "Foreign Car" )

10

Multiple If-Then Statements

BRIO

Formula Definition Box:
if(TIN==111111111) {'A'} else {if(TIN==222222222) {'B'} else {if(TIN==333333333) {'C'} else {'Z'}}}

11

Reference The Tab Name In A Cell

EXCEL

Simply type this in:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Paste this in, exactly as it is. Don't do anything with the "filename". This is cool!

12

M For Million Dollar Groupings

EXCEL

Custom Format: $0,, "M" will display $2 M for $2,123,456
Custom Format: $0.0,, "M" will display $2.1 M for $2,123,456
Custom Format: $0.00,, "M" will display $2.12 M for $2,123,456

13

K For Thousand Dollar Groupings

EXCEL

Custom Format: $###.0, "K" will display $6.5 K for $6,462.21
Custom Format: $###.00, "K" will display $6.46 K for $6,462.21

14

Logic for either K or M (Option 1)

EXCEL

Custom Format: [>=1000000]$0,, "M";$0, "K" will display either K or M.
Or
Custom Format: [>=1000000]$0.0,, "M";$0.0, "K" will display either K or M.

Two options here, one for decimals, and one without.

15

M For Million Dollar Groupings

POWERPOINT

Custom Format: $#,###" M" will display $12 M for $12,251,111.23

16

Crosstabs With Null Values

ACCESS

Many times in a crosstab query, you count the number of records, and have a null when there is no record. To get rid of the null, and display a zero, simply go into the design view, and under the "Value" column, use the following formula:
CLng(Nz(Count([field to count]),0))

Not using the CLng function will display values that are in text, instead of numeric.

17

Subtotal A Column

EXCEL

In this example, we want to subtotal the values in column-C, from rows 6 through 200. The "9" designates a sum. Other values include: 1=Avg, 2=Count, 4=Max, 5=Min

Outlook Email

This can be used in an Access form to send an Outlook email. Here is the syntax and structure for building a typical procedure. You will need to make sure your Access dataabse has the Microsoft Outlook refrence set in the module.

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strData As String
Dim strmail As String
Dim strsubject As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = "abc@abc.com"
.CC = ""
.BCC = ""
.Subject = strsubject
.Body = "Hello world!"
.Attachments.Add ("C:\MyMainFolder\NameOfFile.xls")
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

Notes: In the above, if you want to send an email, and maintain HTML formatting (or use HTML tags), you can switch ".Body" to "HTMLBody."

19

Transferring Data

ACCESS / EXCEL

There are three methods of transferring data in Access, with each having its own benefits and disadvantages. They are:

- DoCmd.SendObject
- DoCmd.TransferSpreadsheet
- DoCmd.OutputTo

The SendObject is nice because you can take an Access table or query, and send it as an Excel attachement via Outlook. If you have hyperlinks in your Access table, these will be maintained. The downside is that when you wish to automate a process of sending multiple emails with the SendObject method, Outlook will prompt you with a Security pop-up, asking you to approve the action. That means that if you are sending the same email to ten recipients, you will need to click the pop-up ten different times. Here is the general syntax for SendObject:

DoCmd.SendObject acSendTable, "tblNameOfAccessTable", acFormatXLS, _
"xyz@xyz.com;abc@abc.com","abc@abc.com", , _
"This is my Subject Line", , False

The TransferSpreadsheet method was used by me to take an Access table, and place it in a folder, in the form of an Excel. I did not continue using this because while it did convert an Access table to an Excel, the formatting was lost, including the hyperlinks. Here is the general syntax for TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, _
"tblNameOfAccessTable", "C:\MyMainFolder\DesiredNameOfExcel.xls"

The OutputTo method was eventually used by me in one of my projects. With the Output method, I was able to take my Access query (or table), and save it to a folder in my computer in the form of an Excel. The format for columns and hyperlinks were also maintained. In my project, i then used one of the Outlook methods (See Outlook Email topic) to attach this file to an Outlook email. Here is the general syntax for Output method:

DoCmd.OutputTo acOutputQuery, "TheNameOfAccessQuery", _
acFormatXLS, "C:\MyMainFolder\DesiredNameOfExcel.xls", False

20

21

22

23

24