using wildcards in IIF statements

IIf([PlanType] Like "*Open*","Yes","No")

will be true for BN Open. BE Open-J

Adding a blank column into a query results

Fred:""
will add a blank column titled Fred

You may have chosen fields from record sources which the wizard can't connect

To correct this error, I simply created the query manually rather than using the wizard (wizard is not that smart sometimes). Of course make sure all your relationships are correct first.
 To create a query manually:

Create > Query Design

To see all relationships

I was recently working on a project and I had defined several relationships, between tables and between queries. I decided to rename the queries and when I went back to the relationships window to see if the relationships had been updated with the new query names, I could no longer see the query relationships - I could only see the table relationships. The queries were working fine but I was worried because I couldnt see the query relationships. Googling, I found the answer I was looking for:
go to database tools > relationships > all relationships

Leszynski_Reddick Naming Convention

Create an AutoExec macro that runs when the DB is opened

From:

https://support.office.com/en-us/article/Create-a-macro-that-runs-when-you-open-a-database-98ba1508-dcc6-4e0f-9698-a4755e548124

Create an AutoExec macro:

If you have already created a macro that contains the actions that you want to occur when the database starts, just rename the macro AutoExec, and it will run the next time that you open the database. Otherwise, follow these steps to create a macro:

1. On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
2. In the Macro Builder, in the first empty Action cell, select the action that you want to perform. If applicable, under Action Arguments, type the appropriate values in the argument boxes.
   If you cannot find the action you want, on the Design tab, in the Show/Hide group, make sure Show All Actions is selected. This expands the list of actions that you can use, but the list will include some actions that will only run if the database is granted trusted status. For more information, see the articles Decide whether to trust a database or How database objects behave when trusted and untrusted.
3. Repeat step 2 for each additional action you want to occur.
4. Click Save, and in the Save As dialog box, type AutoExec.
5. Click OK and then close the Macro Builder. The new macro will run the next time that you open the database.

For more information about how to create macros, see the article Create a macro.

NOTE: 

• To bypass the AutoExec macro and other startup options, hold down the SHIFT key while you start the database. For more information, see the article Bypass startup options when you open a database.
• Another way to perform actions when a database starts is to specify a startup form, and then attach macros or VBA code to the OnOpen or OnLoad events of the form. For more information, see the article Set the default form that appears when you open a database.

Set the default form that appears when you open a database

From https://support.office.com/en-us/article/Set-the-default-form-that-appears-when-you-open-a-database-94961011-392F-4C3B-8DBC-E5D5ADBFF1DF

This procedure assumes that you have already created the form that you want to display.

1. Click the File, and then click Access Options.
2. Click Current Database, and then in the Display Form list, select the form that you want to display when the database starts.
3. Click OK, and then close and reopen the database to display the startup form.

NOTE: 

• To bypass this option and other startup options, hold down the SHIFT key while you start the database. For more information, see the article Bypass startup options when you open a database.
• By attaching macros or VBA code to the startup form's OnOpen or OnLoad event, you can specify more actions that occur every time that the database starts. For more information about how to create macros, see the article Create a macro.

How to compare two tables and find records without matches

From https://support.office.com/en-us/article/Compare-two-tables-and-find-records-without-matches-cb20ad48-4eba-402a-b20d-eaf10a5d1cb4

Use the unmatched query wizard to compare two tables:

On the Create tab, in the Other group, click Query Wizard.


In the New Query dialog box, double-click Find Unmatched Query Wizard.


On the first page of the wizard, select the table that has unmatched records, and then click Next. For example, if you want to see a list of Northwind 2007 products that have never been sold, select the Products table.


On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.


On the third page, select the fields that relate the tables, click < = >, and then click Next. You can choose only one field from each table. To follow the example, select ID from the Products table and Product ID from the Order Details table. Verify that the correct fields are matched by reviewing the text in the Matching fields box.


Note that the ID and the Product ID fields may already be selected because of existing relationships built in to the template.


On the fourth page, double-click the fields that you want to see from the first table, and then click Next. To follow the example, select the ID and Product Name fields.


On the fifth page, you can choose to view the results or to modify the design of your query. In this example, click View the results. Accept the suggested name for the query, and then click Finish.


You may want to modify your query's design to add other criteria, to change the sort order, or to add or to remove fields. For information about modifying a Find Unmatched query, read the following section; or, for more general information about creating and modifying queries, see the links in the See Also section.

Create and modify a Find Unmatched Query to compare by more than one field

1. On the Create tab, in the Other group, click Query Wizard.
2. In the New Query dialog box, double-click Find Unmatched Query Wizard.
3. On the first page of the wizard, select the table that has unmatched records, and then click Next. For example, if you want to see a list of Northwind 2007 products that have never been sold, select the Products table.
4. On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.
5. On the third page, select the fields that relate the tables, click < = >, and then click Next. You can choose only one field from each table. To follow the example, select ID from the Products table and Product ID from the Order Details table. Verify that the correct fields are matched by reviewing the text in the Matching fields box. You can join the remaining fields after you have completed the wizard.
   Note that the ID and the Product ID fields may already be selected because of existing relationships built in to the template.
6. On the fourth page, double-click the fields that you want to see from the first table, and then click Next. To follow the example, select the ID and Product Name fields.
7. On the fifth page, click Modify the design, and then click Finish.
   The query opens in Design view.
8. In the query design grid, note that the two tables are joined on the fields (in this example, ID and Product ID) that you specified on the third page of the wizard. Create a join for each remaining pair of related fields by dragging them from the first table (the table that has unmatched records) to the second table. For this example, drag the List Price field from the Products table to the Unit Price field from the Order Details table.
9. Double-click a join (the line connecting the fields) to display the Join Properties dialog box. For each join, choose the option that includes all the records from the Products table, and then click OK.
   In the query design grid, note that each join now has an arrow at one end.
   
   1. When you create the join between the List Price and Unit Price fields, it restricts output from both tables. Only those records with matching data in the fields in both tables are included in the query results.
   2. After you edit the join properties, the join only restricts the table that the arrow points to. All records in the table that the arrow points from are included in the query results.
   NOTE: Make sure that all of the arrows on the joins are pointing in the same direction.
10. For the table that has related records (in this example, the Order Details table), double-click each field that is joined to the first table, except for the field that you chose on the third page of the wizard (in this case, the Product ID field). For each of these fields, clear the check box in the Show row, and type Is Null in the Criteria row.
11. Optionally, add criteria to the other query fields, or create calculated fields that are based on values from the first table.
12. On the Design tab, in the Results group, click Run.
    The query returns the names of products that are not part of any existing orders.

Create your own query to find unmatched records

1. On the Create tab, in the Other group, click Query Design.
2. In the Show Table dialog box, double-click the table that has unmatched records, and then double-click the table that has related records.
3. Close the Show Table dialog box.
4. In the query design grid, the two tables should have lines, called joins, connecting them by their related fields. If the joins are not there, create them by dragging each related field from the first table (the table that has unmtached records) to the second table (the table that has related records).
5. Double-click a join to open the Join Properties dialog box. For each join, choose option 2, and then click OK.
   In the query design grid, the joins change so that they have arrows at one end.
   NOTE: Make sure that all of the joins are pointing in the same direction. The query will not run if the joins point in different directions, and may not run if any join is not an arrow. The joins should point away from the table that has unmtached records.
6. In the table that has unmatched records, double-click the fields that you want the query to return.
   Optionally, enter criteria for any of these fields, or create calculated fields.
7. For the table that has related records, double-click each field that is joined to the first table. For each of these fields, clear the box in the Show row, and type Is Null in the Criteria row.
8. On the Design tab, in the Results group, click Run.

How to Import an Excel Spreadsheet Into Access With a Macro

From http://smallbusiness.chron.com/import-excel-spreadsheet-access-macro-42538.html
by Kevin Lee, studioD
Step 1

Launch Access and open one of your databases.

Step 2

Move to the Tables pane and double-click the table you wish to load. The table’s fields appear in columns running across the top of the update pane. You’ll probably find it easier to work with your data if the column headings in your Excel spreadsheet match the names of the fields in your Access database.

Step 3

Launch Excel and open the spreadsheet you wish to load into Access. Verify that the spreadsheet’s column names match the Access table’s field names. If the spreadsheet has no column headings, right-click the spreadsheet’s first row and select “Insert.” Excel adds a new empty row to the top of the spreadsheet. Type a column heading for each column in the spreadsheet. For example, if the field names in your Access table are Name and Age, use those names as column headings in the spreadsheet.

Create Macro

Step 1

Return to Access and click “Create.” Double-click the “Macro” button on the Ribbon to view the Macro Builder.

Step 2

Click “Show All Actions” and then click “Action Catalog” to display the Action Catalog pane if it is not visible. This pane displays a list of macro categories.

Step 3

Move to the Action Catalog Pane and double-click “Data Import/Export” to view the macros within that category. The ImportExportSpreadsheet form appears. This form allows you to set up the parameters Access needs to import your spreadsheet.

if you dont see this action, go to Macro Tools > Design > Show All Actions

Show All Actions

Step 4

Type the name of your table in the “Table name” text box. Type the path name of your spreadsheet in the “File name” text box. For example, if it is a file named “Sales.xlsx” residing in a folder named “Spreadsheets” on your “C” drive, type the following into the “File Name” text box: .. C:\Spreadsheets\Sales.xslx

Step 5

Type “Yes” in the Has Field Names text box and press “Ctrl-S.” Type a name for your macro in the “Macro Name” text box and click “OK.”

Run Macro

Step 1

Click the tool bar’s “Database Tools” button and then click “Run Macros” to view the Run Macro popup window.

Step 2

Click the “Macro Name” drop-down menu and select the macro you created.

Step 3

Click “OK” to run the macro. Excel imports the spreadsheet data into the table.

Tips

• If you don’t see the imported spreadsheet data immediately, move to the Tables pane, right-click your table and select “Open” to refresh its view.
• or move to the Tables pane, then switch to Design view and then back to Datasheet view.
• Double-check your spreadsheet and Access table to verify that the number of fields in the table match the number of columns in the spreadsheet.