Articles with VBA and/or Steps ... for Access ... and more

Create a chart with Microsoft Access. To create a query that compares sales for each month for 2 different years, combine the results from two queries that each filter the data by the specified year. The resulting query lines up data for the chart.

Add a chart control to the design view of a form using the wizard. Then modify the chart control properties to get what you want.

Running sums are easy in Excel, but how do you do them in Access? Create a running sum using the built-in DSum domain aggregate function. For better performance, VBA can be used instead. Both methods are shown.

Charts are a great way to visually show data, but what do you do when the scales of the values are different? Use a secondary axis, and format charts for a better look.

A stacked column can be used to show how values break down by category. See how to use a crosstab query for the data source of a chart.

Use a crosstab query for the source of a grouped report Display up to 10 levels of sorting and grouping on a grouped report, and show subtotals and other calculations on each level. Use the report wizard for the basics then see how to add additional calculations to sum columns of the crosstab.

Add calculated controls to a grouped report to show percentages related to totals and subtotals. See a logical way to name so that the formulas in control sources are more clear.

See how to create an unbound form as a menu to navigate and open objects in your database. Add title and buttons. Use the Command Button Wizard to open reports and forms.

Organize controls on a menu form using a tab control. See how to collect start and end dates and use criteria specified by the user to filter reports. Set a form to automatically open when the database opens.

See how to change macros to VBA code. Construct criteria and modify the SQL statement for a crosstab query so that records are limited before the crosstab occurs. Then use VBA to open the report reflecting the specified criteria.

Often times, you will use a query to line up data from more than once source. You can then use a grouped report. Easiest may be to start with the wizard, but the formatting isn't pretty, so change it. Best-fit controls. Hide controls with key fields. Move controls from the report header to the page header. Delete unnecessary label controls and use properties to lines things up.

Modify report design to make the layout more efficient and easier to read. Concatenate text and sums. Use a formula with IIF to see if an 's' needs to be added to the end of the record count description. Match indents by setting the LEFT property. Make sure there is at least one record after a group heading when pages are rendered. Set colors for sections. Add a command button to the menu form to open a report, and consider criteria the user may have specified.

One of the best things you can do to work more efficiently is to customize the Quick Access Toolbar (QAT). Learn useful command to add and how to organize them.

Designing a successful database requires planning. Think about the data you have to put in, and structure it well so that what gets built on top is stable and flexible. How to decide how to organize fields into tables, and then build the tables you need.

The building blocks of a database are the fields in each table. An important characteristic of a field is its data type. Learn about the different data types and when to use them.

There are many types of numbers - whole numbers, numbers with decimal places, numbers with currency symbols or percentage signs, and data that looks like a number but isn’t to data that looks like text but is a number. Take a deep dive into the number data types you can choose. See ranges and limitations. Consider accuracy and magnitude when you choose data type. Modify basics number formats for your country using the Windows Region Settings.

Formulas are commonly referred to as expressions in Access. See the different parts of an expression ... functions, operators, references (identifiers), and/or constants. Formula examples with number, date, and string results.

The Expression Builder helps you construct formulas in Access. It can be launched from many places, such in queries and tables for calculated fields, on forms and reports in control sources, default values, validation rules, and more. The Builder Button (...) opens the Expression Builder when it applies. Make text larger and smaller, build expressions, use built-in functions and see what parameters they need, and lookup information.

When you are tracking and analyzing accounts receivable, see how to dump amounts into categories like 1-30, 31-60, 61-90, 91-120, and 120+ days past due using DateDiff and IIF. Modify format code to show the due amount how you wish or the words "Not Overdue". See how to use the Totals Row that is available for all datasheets if you simply turn it on!

Even though queries show text, did you realize you can also show bar charts? Use block characters in Unicode to visually display data!
▀▀▀
▀▀▀▀▀▀▀▀▀
▀▀▀▀▀▀

Continuing on with using Unicode characters to help you visualize your data in a query, which normally can't have graphics, see how to use up and down triangles, and circles, to indicate if a value has gone up, down, or stayed the same. Use a query to compare a value to the value in a previous record for the same field. Enhance performance using a subquery instead of DMax in the SQL statement for the query to get the record to compare to.
▼ ● ▲

Do you have data involving dates? See how easy it is to show your data on a calendar with a free tool you can download. Use a query to line up your data, and then presto! Calendar!

Combo boxes are a great way to let your users pick something from a list. They can also store one thing, such as a number, and show users friendly text. Learn important combo box properties to know!

Learn about using a Mainform + Subforms for Payments in Microsoft Access to find and enter data for customer payments on orders. Synchronize subforms with LinkMasterFields and LinkChildFields. Calculate sum and total number of orders and payments, and the balance due. Display the company logo. Learn how to undo creating a new record, shortcuts for entering data, set the Enter key behavior setting in the Access Options, and anchor controls so they stretch and shrink as the user resizes the window.

Use a subform to show the balance for a customer on a form for entering customer payments. Construct the underlying query to do calculations. Add a subform to a main form. Use properties of the subform control to specify how the subform is linked to the main form with Link Master Fields and Link Child Fields). Name is what it’s called. Width and Height specify size. Top and Left specify location. Appearance is controlled by properies such as Border Color, Border Style, Special Effect, and Visible. Locked, Enabled affect whether user can modify and select values.

SQL. Get comfortable with SQL -- what it is, and how to write/read it. SQL is Structured Query Language and defines the rules you need to use to get data such as: SELECT myField1, myField2 FROM myTable

Row Source is a property of a combo box or list box that specifies where the rows of data for the list will come from. It is often an SQL statement or a query, and can also be a table.

VBA. Dynamically modify the contents of list boxes on a form by changing SQL for Row Sources using VBA so they change each time a customer changes. If you've never written code before, read this article and get started.

UDF. User-Defined functions are a great way to make things easier to understand, even with complex logic. If you can create a formula, then you can also write a function using VBA. The functions you write can be used in queries, forms, reports, and called by other code. Learn how.

Excel has a lot more built-in functions than Access ... and what Excel has, Access can use! Look at the VBA code behind a form that collects data for Excel's PMT function to calculate the monthly payment for a loan. Enhance performance and readability using With. Download the PMT Calculator tool.

Create a wrapper function in VBA to call Excel so that Excel Worksheet functions can be used in calculated fields of a query in Access. Format columns in a query to show values in red, blue, and other colors.

Dates and Times. Why isn't the base date for Access the same as Excel? See how date and time values really are numbers, and how doing math with them is easy. Data entry tips and shortcuts. Calculated field that creates a date.

Date (+Time) Formatting can be used to display what you want for a date. Even though dates are stored internally in Access as numbers, format symbols (placeholders) can be used to display dates (and times) the way you want them to look. Along with standard information—such as year, month, day, hour, minute, and second, learn about other information such as quarter and day of year that you can also show.

Windows Region Settings can affect how data is displayed. Note differences between short dates, long dates, currency, and long time values for a few regions. ACCDB download has VBA to read international settings for Windows using Excel since Access doesn't have a direct built-in way.

How to get local values of international settings such as currency symbol, whether 12 or 24 hour clock is used, am/pm indicator, date order, measurement system, and various separators such as for date, time, decimal, and thousands. Automates with Excel to get some of the values. ACCDB download with VBA.

How to Highlight the Current Record in an Access form that shows multiple records. Uses a control to keep track of the primary key, some conditional formatting, and a little VBA. ACCDB download with VBA.

Holidays remind us to keep tack of Contact information like names, addresses, phone numbers, email address, and websites; lists and categories ... for friends, family, companies, organizations, clubs ... Make lists for who you want to send a card or gift to, or keep in touch with for another reason.
MyContacts is a fully functional contact management system, with source code, that is more within the reach of non-specialists than the complex one you can also get on MsAccessGurus

Difference between using & (ampersand) and + (plus sign) in expressions. Combine all the name parts like "Joe" and "Smith" to make a full name. IsNull function. By storing date of birth in year, month, and day fields, you can fill what you know. If DateSerial can make a actual date, it does, and then you can also find out the day of the week someone was born, such as Tuesday or Saturday, which is nice.

Assign each contact to as many Lists (that you define) as you like. Organize contacts into groups by specifying a main category such as friend, family, colleague, service, supplier, manufacturer, club, or other interest. Relate contacts to each other by specifying a "Head Contact" such as a company, club, or head-of-household.

Use a cross-reference table to create a many-many relationship so one contact can be on many lists, and each list can have many contacts.

See how a self-join works, where one record in a table relates to another record in the same table. VBA to swap subforms to show more records or more detail. Assign hotkeys for quick keyboard jumping by putting & (ampersand) before the character to use with ALT.

... continuing with the 'series' started in November for managing contact information...

Instead of a main form with a lot of subforms, maybe you want to popup forms to add and edit related information -- this also reduces screen space that is needed. The main form can just then have links to other forms, and can be continuous too. See how to write VBA to popup related forms that are synchronized to the record displayed on the main form.

Partition is a highly useful but little-known function in Access. Group numbers into ranges and determine statistics. A great example of a practical use is to age overdue balances into 30-day buckets, and report the sum of each; which would otherwise require several expressions.

Use the Rnd() function to return a random number. In this example, we pick 3 winners for a monthly contest. Store the generated random number in a field, and use an SQL statement to update it. Next, make a query to sort by the random number, and limit the results to the top 3.

Steps to import data from Excel using the import wizard. You may want to change some things. For instance, Access chooses double-precision for numbers even if they don't need a decimal point. What can you do about that? Look at each column of data that is imported ... what is it? and what does it relate to?

Import data from Excel and Append to an existing table. After each sheet is added, run an update query to do a calculation.

Shuffle data to other tables after it is imported from Excel. Separate information when data is put into Access. More append queries.

Often, data coming from Excel is flat, or not structured. "Normalize" so that data is structured the way that it really is in the real world. 'Visualize' it ... see it. Append queries to add import records. Create foreign key values from primary keys in tables that aren't the one you are adding records from.

Lots of times when you import data, it needs to be parsed into different fields. VBA has a wonderful function called Split that can separate data when there is a known delimiter for the parts. Learn how to make a user-defined function (UDF), and a little foundation on VBA code. Modify the table design to add more fields. Use your UDF in an Update query.

When you change data using an Update Query, do it in steps that can be undone, and verify calculations before results are used. Always select what you think new values should be before you actually make changes. Select query to see calculated values and make sure logic is good! Then change to an Update query and run it. If you want to modify the logic for something you missed before, run a reset query that clears the new values. That won't, of course, bring back values that might have been overwritten. For that, you'd need to link to a backup table. Once logic is adjusted, run the first Update query again.

You can't use a totals query in an update query. You can use domain aggregate functions such as DSum and DCount. All the domain aggregate functions use the same syntax: DSum( "Expression", "Domain" [,"Criteria"] ) where DSum could be something else like DMax, DMin, or DCount. Expression is a field or formula. Domain is the name of a table or query. Criteria is optional and limits the records.

Once you start building a database in Access, you’ll find yourself wanting to add more and more functionality. The best data structure is one that’s flexible and reflects how the objects that the data is based on exist in real life. This will make it easier to expand the database and add more to it as needs arise.

When designing an Access database application, pattern the structure of tables, fields, and relationships after the real world. Change structure as you learn more.

When you’re building a report, it often makes sense for a main report to show a subreport multiple times. For instance, when the main report if for a student and the subreport is their classses and grades for each year. This article exposes one of the powers of Access to relate similar data for different criteria.

It's nice to be able to show whatever characters you want, whether they're on the keyboard or not. For instance, if you're writing trignopmetric formulas, good to show the symbol for π (pi). It's also nice to use a numbers table to create data. This article shows how to make a handout for students showing values for Sine, Cosine, Tangent for various angles of a circle. I love math! Since this monthly article is about Access, a query is created that can be printed and passed out to a class to learn about the major angles of a unit circle.

Happy New Year! With each new year, we think about resolutions and planning for a successful future. Learn about a tool I created to make calendars that I share freely with the public, the CalendarMaker. I've spent a lot of time on it, but want to add more in the future. Print a calendar on any report in any position and size, in the language you have installed for Windows, and show information from your database on each day. Start weeks with any day you want.

Know what folder your Access database is in, and what the file is named. Default file names. When an Access database is being used, there will be an LACCDB (or LDB) file in the same folder as the database. The Lock ACCess DataBase file keeps track of the users who have the database open, and prevents multiple concurrent users from simultaneously changing the same data. Display file extensions in File Explorer, and use Details view so you can sort and display the most recent files at the top of the list. Add date code to file names since Access updates the file date/time even if you don't change anything.

Because the purpose of a database is to logically organize information, it makes good sense to organize the information related to the database too. Learn tips from world-class Access developers such as Arvin Meyer and Dale Fye, see examples of file folders to create, and discover what is most important. Learn how you can keep your data secure when you're working with sensitive information.

Create combo boxes in your form header sections to quickly find records. Use information from the table with the record you're looking for, or display information from a related table. For instance, if you have a related table for contact phone numbers, you can lookup a contact by phone number. This gives you great flexibility and saves time.

Learn how to customize the Quick Access Toolbar, or QAT, which gives you a handy way to access the commands you use the most, saving time and mouse clicks. Each of the Microsoft Office products has a QAT. Some of the commands, like SaveAs, aren't specific to Microsoft Access -- I add them to all my Office QATs ;)

The Input Mask property in Access can be used to validate the type of data with each character typed. General ledger (GL) codes, used to describe the nature of each transaction, are typically designed with groups of letters or numbers separated by dashes, spaces, or other characters. In Access, you can use an input mask to make it easier to enter information with a pattern, such as a GL code or phone number.

Write a letter in Microsoft Word and personalize copies with data from Access in a mail merge. Data Souce can be a table (always on list) or a query.

How to add new values to another table when what the user wants isn't on the list of choices. VBA code for the NotInList event that calls a generic procedure to add the record using an SQL statement. The NotInList event is a quick way to add values to whatever table provides the list for a combo box.

Anytime two items, objects, or ideas of the same type have a relationship, you have hierarchical data. For instance, people have parents, accounts have a chart of accounts, and parts are organized into assemblies. These kinds of relationships appear often in Access databases. The simplest way to store a hierarchy is where each item has a single parent, and each parent can have zero or more children. A simple hierarchical relationship can be defined by creating a foreign key field in a table that relates to its own primary key.