The TRIM function is a great tool to help clean up your data. The function looks at your data and removes all excess spaces at the beginning, middle or end of your data.

 

Did you know that we regularly update our free video content on Log on to Learn? In addition to no-fee live webinars we offer several on-demand videos that are completely free.  We hope that you’ll find great value in our free offerings. This week’s update included content for Microsoft Excel 2010!

Exploring Excel can be a daunting task. It’s a flexible application that can do so many different things. If your intimidated looking for things in Excel, let us provide some tips. This week’s selection of videos will unlock some functions and features of Excel 2010. Our full Log On To Learn video library has a complete set of videos around all these features and much more.

Available Videos Include:

Excel 2010:
Data Analysis Revealed! Forecasting, Analyzing and Creating Informative Reports
Getting External Data from Access, The Web, Text and Other Sources
Calculate Specific Results Under Two Different Conditions with the IF Function
Wrapping Text

These videos are completely free. You can view them here: http://logontolearn.com/free_videos.php

Don’t for get to register for our live events on the 23rd. Topics for our live events include:

Access 2010:
Establishing a Primary Key and Indexing Fields
Creating Tables and Building Relationships

Excel 2010
Exploring Charts
Headers, Footers and Printing Options

Register Online for these free events: http://logontolearn.com/free_webinars.php

Watch as our Excel expert takes you into the function library and reveals the usefulness of the IF function.  Need specific results under two different conditions?  Learn how to apply true or false statements to your formula and generate results baised on your criteria.

 

Fridays just feel like a day for rewards.  It’s the end of the week, we’ve put in a good effort and we’re looking ahead to the weekend.  Well, we’d like to give you something else on Fridays.  That’s why we’re introducing, Function Fridays.  Every Friday we’re going to post a new video tip on an Excel function… for free.  These short tutorials get right at the heart of how to use the functions that can make your Excel life easier.  Now, that sounds perfect for a Friday.

Our first tutorial is a quick tip on how to put the most recent revision date and time into a spreadsheet.  Enjoy!

 

This month’s lunch bite sessions include two events for Microsoft Access and are being held on Wednesday, May 23, 2012.  Simply click the register link and regiser online. Both events are completly free!

Access- Establishing a primary key and indexing fields

Description: Understanding relational databases is crucial before we dive into creating any new database. The foundation of relationships is understanding and establishing a primary key. In addition, indexing fields on some criteria makes sorts and queries faster down the road. This tutorial provides a quick look at how to establish a field as a primary key in a table and how to create single and multiple indexes on non-primary key fields.

Date: May 23, 2012
Start Time: 12:00 PM EDT
Duration: 15 Minutes

Register Online by Clicking Here

Access- Creating tables and building relationships

Description: Once you have your primary and foreign keys established, it’s time to build relationships between fields in multiple tables. These relationships become the connections that link data from disparate tables. In this session we start at the beginning of the table creation process and go right up through building relationships between separate tables.

Date: May 23, 2012
Start Time: 1:00 PM EDT
Duration: 15 Minutes

Register Online by Clicking Here

Date ranges are a very common filter that database users  want to implement in their queries. For example, let’s say your company sells office supplies and your databases tracks customer orders. You want to run a monthly query that summarizes total orders.

 

 

 

 

You can save your database users time and frustration by creating a friendly, interactive form that allows them to enter a beginning order date and an ending order date. Then, with a click of a button, they can run a query that displays the order information for their specified date range.

Follow these steps (in Access 2007 or Access 2010):  Or click the PDF Icon to Download these steps in PDF format.

 

 

 

1. On the Create tab, click Form Design in the Forms group.

 

 

 

2. In the Form Design window, the Form Design Tools Group is now active.

3. In the Controls group, click the Text Box to add a text box to the form.

 

 

4.  Add a second text box to the form.

5. Change the caption in the label to be more descriptive (for example, Begin Order Date). Modify the second label (for example, End Order Date).

 

 

 

 

 

 

6. Click the “Unbound” portion of the first text box.

7. Make sure the Property Sheet pane is displayed on the right side of screen. Click Property Sheet, if the Property Sheet pane is not displayed.

 

 

 

 

 

 

 

 

8. On the Property Sheet, click the All tab.

9. Change the Name property to be descriptive (for example, txtBeginOrderDate).

10. Change the Format property to Short Date.

11. Click on the second “unbound” text box and change its Name and Format properties.

12. Save the parameter form. You can keep the parameter form open, since we’ll add a command button to it as our last step.

Modify the Query to Receive the Date Range Values from the Parameter Form

1. Open your query in Design View, or create the query, then switch to Design View.

2. In the Criteria row of the appropriate date field, right-click and select Build…

 

 

 

 

 

 

 

3. The Expression Builder is now displayed. In the upper area, we’ll build an expression that selects the orders between a begin date and end date that’s entered in the parameter form.

4. Type the word between (followed by a space).

5. In the lower half of the Expression Builder, click on the (+) sign to the left of the Database name to expand the list.

6. Click on the (+) sign to the left of Forms.

7. If your interactive form is still open, you can expand the Loaded Forms list. If you closed the interactive form, expand All Forms. Click on the parameter form name.

8. In the middle area (under Expression Categories), you’ll see both text box names that you created on the interactive form

9. Double-click on the Begin Date text box. In the top-half of the Expression Builder, your expression may look something like:

between Forms![ParameterForm]![txtBeginOrderDate]

10. After the Begin Date text box name, type the word and, then double-click on the End Date text box name.

11. Your final formula will look something like this:

between Forms![ParameterForm]![txtBeginOrderDate] and Forms![ParameterForm]![txtEndOrderDate]

12. Click OK to save and close the Expression Builder.

13. The expression you just created will now display in the Criteria row of your query.

14. Save your query and close it.

Final Step: Create a Command Button to Run the Query

1. Open your parameter form and switch to Design View, if needed.

2. On the Form Design Tools group, click the Design tab.

3. Click Button.

 

 

 

4. Click on the form to start the Command Button Wizard.

5. In the Categories list, click on Miscellaneous.

6. In the Actions list, click on Run Query.

7. Click Next.

 

 

 

 

 

 

 

 

 

8. Select your query and click Next.

9. Choose whether you want text or a picture for your button. Click Next.

10. Type a descriptive name (without spaces). For example, cmdRunOrdersDateQuery.

11. Click Finish.

12. Save your parameter form and test it out.

 

Click the PDF Icon to Download these steps in PDF format.

 

 

 

 

KnowledgeWave provides Access and SQL consulting services both on site and remotely. We’ve helped hundreds of clients turn their assortment of spreadsheets, external mailing lists and other documents into a streamlined, organized database. The result is more time to work on the things their businesses need from them and less time creating reports. Let us know how we can help you! Call us at 1-800-831-8449.

Calculating the number of workdays between two distinct dates can be a pain to do, manually.  However, Excel has a great function that will help you accomplish this.  It involves using the NETWORKDAYS function.

Follow the steps below:

1. Click inside the cell where the formula will reside.
2. Type =networkdays(
3. Then type in the first date of the range encased in “”.  For example “4/6/2012″.
4. Then type a comma and the end date of the range encased in quotes.
5. Close your parenthis and hit enter.

Your formula should look like this.

If you’d like to add in holidays add them as a new condition in {}.  For example =NETWORKDAYS(“4/6/2012″,”12/31/2012″, {“7/4/2012″, “12/25/2012″).  Should you have a long list of holidays type them in a list somewhere in your spreadsheet and refer to that cell range in the formula. For example =NETWORKDAYS(“4/6/2012″,”12/31/2012″, J1:J12).

Categories got a complete makeover.  They work throughout Outlook in many helpful ways.  Any Outlook item you create can be categorized by right clicking on the item and choosing categorize.  The first time you use each category you will be asked to name the category.  There are six categories by default (all named for colors) but you can add many more.

Once you begin using categories, they will be available throughout Outlook.  For example you can create a label for a project then categorize all emails, contacts, meetings and tasks associated with that project for easy distinction.

This is a great event for any new Windows 7 PC user, or anyone that has been using Windows 7, but hasn’t learn the new great features that it has to offer. Join us in March for this great free event. Our events are valued at $99, but this event is free when using code MADNESS at check out.

Product(s): Microsoft
Windows 7
Audience(s): Business Professional
Duration: 60 Minutes
Start Date: March 28th, 2012
Start Time: 3:00 PM EDT (US)
Register Online (a $99.00 Value!) Use Code:
MADNESS at check out to attend for free!

Event Overview: Windows 7 is new from the ground up. It was created so that users could put the personalization back into the PC. Whether you’re already using Windows 7 or planning on making the switch, come and be part of this 60 minute webinar that will uncover many of the ways Windows 7 is different from earlier versions. In this tips-packed webinar, you will learn techniques in Microsoft Windows 7 to customize and organize your work, and streamline your business.

Watch are short video trailer for this event:

Who needs all these decimal places? Rather have it display results to the nearest whole integer? Nearest tenth? Nearest Hundreth? Here’s how to apply the ROUND function for clearer numbers according to your specifications.

This video tutorial tip is brought to you by Log on to Learn. Log on to Learn has over a 1000 videos ranging from short tutorial clips to longer in-depth series. This video is part of larger series called Excel 2010 Functions. Other videos highlight, the CountIF, SumIF, NestedIf, PMT funtions to name only a few.