Windows 8: Ribbon for Windows Explorer

Remember how happy you were when the ribbon was introduced in Microsoft Office 2007? Well you will be able to relive the joy with the next release of Windows, according to their developers' blog on MSDN. They've been putting a lot of thought into Windows Explorer and have decided to revamp the interface and to include the ribbon.

Despite my sarcasm in that last paragraph, I have actually become a fan of the ribbon. I am convinced that, like the ribbon in Microsoft Office 2007, this will simply be something that I will hate at first but will eventually get used to and find more intuitive. It's just a matter of breaking old habits and making new ones, which is never easy to do.

In any case, I found the blog post to be very interesting. It's nearly as interesting as the inevitable debate that has erupted in the comments area. Check it out for yourself:

http://blogs.msdn.com/b/b8/archive/2011/08/29/improvements-in-windows-explorer.aspx

Read More

Access: SQL Server 2008 Date/Time Data Type Compatibility

I came across something annoying this week, and have decided to make a post here about it with the hopes that it can help someone else who might be experiencing the same thing!

Here's what happened. I have a Microsoft Access database that has a number of linked tables from a SQL Server 2008 database. I started to create an input form in Design view for one of these tables in Access. I added a date field that I'd chosen directly from the table, and then switched to Form view to try it out. I was annoyed to see that a date picker wasn't showing up for the date field. I switched back to Design view and checked all of the properties to make sure that it had been set up correctly (which it had), and then I double-checked the data type in SQL Server. I'd chosen 'DATE'. I checked in Access to see if it recognized this field as being a date, and it seemed to recognize it as such. The control on the form, though? It refused to act like a date.

The solution that I eventually found was to change the field in SQL Server to a DATETIME data type. I relinked the table in Access, and had no problem with the date picker on my form. I was curious to know why, though, and to make sure that this was actually the issue and not just something that I'd done wrong. I did a bit of research online and can confirm based on Microsoft's help content that four of the new date/time data types in SQL Server 2008 are only partially supported in Access 2010. These data types are as follows:

• TIME
• DATE
• DATETIME2
• DATETIMEOFFSET

What you'll find is that in Access, while the tables themselves recognize these data types and identify them as Date/Time fields, you will run into trouble when you try to use them on forms and reports. They will appear as text fields, meaning that you won't be able to apply date formats (without additional programming) or use the date picker.

Read More

Access: Show Checkbox Controls for Bit Fields from SQL Server Linked Tables

I've been having all sorts of fun adventures working with a Microsoft Access database linked to SQL Server 2008 tables. And by "fun adventures", I really mean to say "irritating issues". Thankfully, I don't let them stop me... I just dig around until I can figure out how to get my way!

A bit field in one of my linked tables was showing up as a text box in forms. It was also showing up as text while viewing the table in Datasheet view. My first thought was, "Well this is completely useless. I don't want to see -1 and 0. I want a nice little checkbox!"

Generally, when you want to change a bit field (or in Access terms, a 'Yes/No' field) to display a checkbox control, it's easy enough to just go into the table design and make that change there. Yes, this is still the case for linked tables... but be forewarned that the UI in Access, for whatever reason, lies to you and attempts to trick you into thinking that you can't. Don't be fooled!

Right-click on the linked table that contains the bit field, and select 'Design View'. You're going to see the following message: "Table 'dbo_YourTable' is a linked table whose design can't be modified. If you want to add or remove fields or change their properties or data types, you must do so in the source database. Do you want to open it anyway?"

While this is mostly true, it fails to tell you that there are still some properties that you can alter that only really affect your Access file and that yes, you are free to make and save those changes here. Do yourself a favour and ignore this warning and proceed by clicking on the 'Yes' button.

Now, select your bit (Yes/No) field. At the bottom of the window in the 'Field Properties' area, click on the 'Lookup' tab. You're going to see a property called 'Display Control'. Change this to 'Check Box'. Now close the table, and click 'Yes' to save your changes.

It really is that simple. Unfortunately for me, this initially caused me grief because the warning was giving me the impression that I shouldn't even bother to try changing the table properties. Sheesh! Here's hoping that this post can help someone else (or several other people) save time.

Read More

Excel: Use VBA to Get Around Nested IF Statement Limitation

If you’ve ever used nested IF statements in Microsoft Excel, you might be aware of the fact that it is impossible to nest more the 7 functions in 2007 (or 64 in 2010 - thank you to 'danonuke' for enlightening me on this). What if you really need more than 7, though? Using VBA to create your own function to get around this limitation is actually fairly simple, even if you aren’t very familiar with programming in Excel.

If you are comfortable with VBA, then you’re going to want to scroll down and take a look at the code that I’ve posted. For those of you that are newer to the world of VBA, I’m going to explain how to do this in a series of steps.

For this example, pretend that you are a teacher who has to assign letter grades to a worksheet that contains all of the final marks of your students. Marks can be one of 11 different possibilities: A+, A, A-, B+, B, B-, C+, C, C-, D, or F.

With 11 different grades, using a nested IF statement might not work. In Excel 2007, you’d either have to simplify your marking scheme or completely leave out some ranges of marks. What we’re going to do is create what is called a user-defined function.

Not entirely sure what that means? Well, assuming you’ve used Excel to some extent, you probably already use some functions that Excel has already made for you. When you add up values using SUM, that’s a function. When you calculate an average using AVERAGE, that’s also a function. Using VBA, you can create your own functions which allow you to get the results you need.

First, you will need to save your workbook as a macro-enabled workbook. This is a special file format that specifically lets you use the functionality that we’re about to add using VBA. On the ribbon, click on ‘File’ and then choose ‘Save As’. In the ‘Save as type’ drop-down, choose ‘Excel Macro-Enabled Workbook (*.xlsm)’, and then click the ‘Save’ button.

Now, you need to open up the VBA editor window. On your keyboard, press Alt+F11. Go to ‘Insert’, and then click on ‘Module’. It is in this module that you are going to place your code.

Copy and paste the following code into the module (or type it out yourself, if you really feel like it):

Function GradePoint(Grade As Double)
Select Case Grade
Case 95 To 100
GradePoint = "A+"
Case 90 To 95
GradePoint = "A"
Case 85 To 90
GradePoint = "A-"
Case 80 To 85
GradePoint = "B+"
Case 75 To 80
GradePoint = "B"
Case 70 To 75
GradePoint = "B-"
Case 65 To 70
GradePoint = "C+"
Case 60 To 65
GradePoint = "C"
Case 55 To 60
GradePoint = "C-"
Case 50 To 55
GradePoint = "D"
Case 0 To 50
GradePoint = "F"
Case Else
GradePoint = "N/A"
End Select
End Function

When you’re done, close the VBA editor window (just click the x in the upper right-hand corner like any other window). Now you’re back on the spreadsheet.

At this point, you’re ready to use the function that you just created. Suppose that cell A3 has a student's final mark, which is 92.3. In cell A4, type in the following:

=GradePoint(A3)

You will now see the result “A” in cell A3.

Now, if this happens to be the exact scenario that you find yourself in, then you’re all set. However, it is likely that you are going to want to tweak this to suit your own needs. I’m going to explain a few things about the code that will hopefully make this task easier for you.

First of all, you can change the name of the function in the very first line. I have called it ‘GradePoint’. Go ahead and change this to something else if you like. This is the text that you are going to enter when using the function, so if you would rather enter something like ‘=EmployeeRating(A3)’, then change ‘GradePoint’ to ‘EmployeeRating’. Don’t just make this change in the first line, make sure you change it for every other instance of the term ‘GradePoint’ in the entire function.

You can go ahead and add or remove cases as you need to, and make changes to the numbers used in the different ranges. Think of the cases as different possible scenarios, and the item in quotation marks are the results that you would like to see in each scenario. In plain English, the first case is like saying “If the grade point is between 95 and 100, then the result is A+”.

The very last case you see, ‘Case Else’, is the result that is returned in the event that none of the other cases apply. It’s similar to the FALSE part of an IF statement. You’re going to want to leave this case to deal with anything unexpected. In my example, I’m showing ‘N/A’ for anything that isn’t between 0 and 100. That way, if I accidentally enter a mark such as 540 instead of 54, I will see ‘N/A’.

I hope that this post helps you out as you build your own function to get around the limitations of nested IF statements!

Read More

Word: Get Statistics on Page, Word, Character, Paragraph, and Line Count

With only one essay left before finishing yet another university course I'm working on, I've found myself focusing quite intently on the statistics of my document. The word count seems to be just as important as getting the content together, as my deadline looms on the horizon.

You might already be aware that Microsoft Word displays your word count in the bottom left-hand corner of your screen (in the status bar). This total gets updated as you write, making it easy to continuously keep track of your progress. Did you know that you can access much more statistical information beyond the word count, though?

For those of you with other statistical needs, you should try going to the 'Review' tab of the ribbon, and clicking on the 'Word Count' button. I personally find this name to be a bit misleading, as you will find that this option brings up a window that displays a number of other statistics besides the word count: page count, character count (with and without spaces), paragraph count, and line count.

A checkbox right at the bottom allows you to specify whether or not to include the text found in textboxes, footnotes and endnotes. How awesome is that? The only complaint I have about this feature is that it doesn't allow you to keep the window open while you work... but it is just a click away, so it's not too much trouble to find when you need a status update.

Happy writing, and good luck to any fellow students that are in the process of pumping out papers!

Read More