<

Wrapper-1, Query.  Show result of Excel Worksheet Function in Access Query

Posted on
6,012 Points
12 Views
Last Modified:
Experience Level: Beginner
7:06
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function.

See how to call a wrapper function from a query, and send the information it needs. "Wrapper Function" is a term to mean a user-defined function (UDF) that calls another program. In this case, Excel is called and one of its worksheet functions is used. This example shows  payment calculations for a number of offers from data that is stored in a table (loan amount, annual interest rate, number of years, and number of payments per year) using Excel's PMT function.

Also see how query columns can display in different colors using the format code, and learn about some of the placeholders.

Part 2 shows how the VBA code works:
Wrapper-2-VBA-Show-result-of-Excel-Worksheet-Function-in-Access-Query.html

Even though topics may be technical, I do my best to consider that a beginner may be watching, so extra detail is added. Hopefully not so much that more advanced users turn away ... maybe you will comment and let me know your thoughts about this? Thanks!

have an awesome day,
crystal

Video Steps

1. Create a query with the fields you want

2. Define calculated fields with intermediate calculations if desired

3. In the calculated column with results from a VBA wrapper function, specify the function name


Send fields, calculated fields, and literal values as parameters inside parentheses

4. To color data in the column, use [ColorName] in the Format property


The color name in square brackets can be Black, Blue, Green, Cyan, Red, Magenta, Yellow, or White

5. Format codes have 4 parts separated with semi-colon (;)


1. Positive, 2. Negative, 3. Zero, 4. Null (vs Text in Excel)
0
Comment
0 Comments

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

State of play: Databases ensure data integrity and relationship through both primary and foreign keys, and MS Access is no exception. Well, that's not completely true ... A primary key should not be null.  - No problem, the NOT NULL constrain…
You can use IMAP or POP3 protocol to configure your Gmail account in MS Outlook. However, after using IMAP account in Outlook for some time, many users want to change their mail settings from IMAP to POP. Today we will discuss how quickly you can c…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month