Posts
This week's question comes all the way from Germany! I like that I've got people coming to my blog from all around the world, that's pretty neat.
"How do I make bulleted lists on my Access reports?"
This is a very good question! You'll notice that, unlike Word, there's no easy option on the ribbon to make bulleted lists. Thankfully, there's a somewhat simple workaround for that.
Go to design view for your report. In the detail section, go to the text box of the item you'd like to appear in a bulleted list format. As an example, let's say that the text box currently contains ItemName. Now, you're going to trick Access into making bullets next to this item by concatenating a bullet character, a space, and the ItemName. Here's what you'll want to enter into the text box:
= Chr(149) & " " & [ItemName]
Don't worry! It looks like mumbo jumbo, but I promise you it'll work. Chr(149) is the code for a bullet character (like this: • ), then it's putting in a space, and finally the ItemName. If the concept of concatenation is foreign to you, I suggest taking a look at this post where I explain concatenation in Excel.
As a sidenote, if you want to just type out that bullet character (in Windows, anyway), all you need to do is type Alt + 0149. Cool, eh?
Thursday, January 28, 2010
Thursday, January 7, 2010
Word: Open Documents As Final (Without Reviewing Markup)
The last question I got, a very good one, was from a very frustrated guy who was sick of opening up his Word documents and having them appear with all of his reviewing markup showing (additions, deletions, comments, etc.). He wanted a way for it to automatically show the 'Final' version. And here is a solution for you! But fair warning, if you use this bit of VBA code, it will not immediately be apparent if changes have been tracked in a document and if there are comments and other markups in the file. Why might this be a problem? If you're planning on sending this file on to someone outside of your company, they might be able to see all of the changes you've made as you've been working on the document... which isn't so great. So please, if you use this code... make sure you accept all changes made and remember to delete your comments before sending it off.
Obviously that doesn't apply if you're printing or making a PDF of the document.
Anyway! On to the code. I'm writing this post with the assumption that you know how to add a macro to your "Normal" template in Word. If this sounds foreign to you, I suggest doing a quick Google search and learn a bit about macros and VBA code in Word. Or, if you're patient, you can wait and I'll eventually make a post explaining that stuff.
There are two codes here, as there are two instances when you might want Word to automatically switch the view to 'Final': the first being when you open an existing document, and the second being when you create a new document.
Sub AutoOpen()
'Sets the revisions view to 'Final' when opening a document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub
Sub AutoNew()
'Sets the revisions view to 'Final' when making a new document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub
Happy New Year, my friends! Here's hoping your 2010 is a safe, happy, and healthy year for you all.
Obviously that doesn't apply if you're printing or making a PDF of the document.
Anyway! On to the code. I'm writing this post with the assumption that you know how to add a macro to your "Normal" template in Word. If this sounds foreign to you, I suggest doing a quick Google search and learn a bit about macros and VBA code in Word. Or, if you're patient, you can wait and I'll eventually make a post explaining that stuff.
There are two codes here, as there are two instances when you might want Word to automatically switch the view to 'Final': the first being when you open an existing document, and the second being when you create a new document.
Sub AutoOpen()
'Sets the revisions view to 'Final' when opening a document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub
Sub AutoNew()
'Sets the revisions view to 'Final' when making a new document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub
Happy New Year, my friends! Here's hoping your 2010 is a safe, happy, and healthy year for you all.
Thursday, October 15, 2009
Excel: Join Together Strings Using Concatenate
Concatenate! It's a bizarre-looking word if you've never seen it before, but the application's actually pretty simple AND very useful.
So here's a scenario for you: You have a spreadsheet listing contact information for hundreds of people. Last names are in column A, first names are in column B. But you need the full name now, for some reason. Here's where concatenate comes in!
Concatenate lets you basically take different pieces of text (called "strings") and put them together. Yes, you can do this with numbers as well, just know that the end result will be formatted as text (there's a way to make it a number, but that'll be a topic for another post). In this case, you're wanting to take the first name, put a space in between, and then have the last name show at the end. Here's the formula to use:
=CONCATENATE(item1,item2,item3,item4,etc.)
Just write "=CONCATENATE", then put in brackets each item you'd like to join together, separated by commas. You can put in cell references, formulas, or even other pieces of text. If you want specific text to show up, make sure you put it in quotes.
So for my example, assuming there's a last name in cell A2 (DuBois), and a first name in cell B2 (Stacy), I'd enter this formula into cell C2:
=CONCATENATE(B2," ",A2)
That's saying: take whatever is in cell B2, then put in a space, then put in whatever's in cell A2. So for this example, my formula would return:
Stacy DuBois
And of course, you're free to fill this formula down through your datasheet and watch as all of the names (or whatever it is you're doing) are automatically generated.
Some people like to use this to generate usernames. You can use the LEFT function to pull the first letter of the first names, and use concatenate to join it with the last name.
The possibilites are endless!
So here's a scenario for you: You have a spreadsheet listing contact information for hundreds of people. Last names are in column A, first names are in column B. But you need the full name now, for some reason. Here's where concatenate comes in!
Concatenate lets you basically take different pieces of text (called "strings") and put them together. Yes, you can do this with numbers as well, just know that the end result will be formatted as text (there's a way to make it a number, but that'll be a topic for another post). In this case, you're wanting to take the first name, put a space in between, and then have the last name show at the end. Here's the formula to use:
=CONCATENATE(item1,item2,item3,item4,etc.)
Just write "=CONCATENATE", then put in brackets each item you'd like to join together, separated by commas. You can put in cell references, formulas, or even other pieces of text. If you want specific text to show up, make sure you put it in quotes.
So for my example, assuming there's a last name in cell A2 (DuBois), and a first name in cell B2 (Stacy), I'd enter this formula into cell C2:
=CONCATENATE(B2," ",A2)
That's saying: take whatever is in cell B2, then put in a space, then put in whatever's in cell A2. So for this example, my formula would return:
Stacy DuBois
And of course, you're free to fill this formula down through your datasheet and watch as all of the names (or whatever it is you're doing) are automatically generated.
Some people like to use this to generate usernames. You can use the LEFT function to pull the first letter of the first names, and use concatenate to join it with the last name.
The possibilites are endless!
Tuesday, September 29, 2009
Thank You
This message is coming later than I'd intended, as I've been rather busy lately!
I'm pleased to say that I placed 9th for Canada at WorldSkills Calgary 2009, and my high score earned me a Medallion of Excellence. What an amazing experience it was! Thanks so much to everyone who has helped me along the way, particularly the fantastic people at Skills/Compétences Canada, Susan Gwin, and Paul Drye from 1-on-1, who helped me prepare for competition.
The opportunities I've had along the way have been incredible. Over the past year, I've received letters and e-mails of support from numerous organizations, software and equipment from sponsors, speaking engagements at various events, and a bit of media attention (which has been pretty neat!).
Last, but certainly not least, I'd like to thank my friends and family who put up with my stressed out self for the past year - I couldn't have done this without their constant support.
Congratulations to Team Canada for bringing back 8 medals and 12 medallions of excellence! To anyone out there who's considering a career in a skilled trade or technology - go for it! It's amazing how far a pile of hard work will get you.
-Stacy
I'm pleased to say that I placed 9th for Canada at WorldSkills Calgary 2009, and my high score earned me a Medallion of Excellence. What an amazing experience it was! Thanks so much to everyone who has helped me along the way, particularly the fantastic people at Skills/Compétences Canada, Susan Gwin, and Paul Drye from 1-on-1, who helped me prepare for competition.
The opportunities I've had along the way have been incredible. Over the past year, I've received letters and e-mails of support from numerous organizations, software and equipment from sponsors, speaking engagements at various events, and a bit of media attention (which has been pretty neat!).
Last, but certainly not least, I'd like to thank my friends and family who put up with my stressed out self for the past year - I couldn't have done this without their constant support.
Congratulations to Team Canada for bringing back 8 medals and 12 medallions of excellence! To anyone out there who's considering a career in a skilled trade or technology - go for it! It's amazing how far a pile of hard work will get you.
-Stacy
Thursday, September 24, 2009
Word: Non-Breaking En Dashes and Spaces
Ever type something like a phone number in Word with hyphens, and get irritated when the phone number gets broken up on two separate lines like this?:
You can contact me any time you like! My phone number is 555-555-
5555.
Sometimes you'd prefer the number to stay together in one piece, like this:
You can contact me any time you like! My phone number is
555-555-5555.
In instances like this, most users would just press the Enter key before the phone number to make sure it's on its own line. But that's kind of sloppy, because you might add text later that makes it unnecessary for it to be the start of a line. It would be best to use what's called a "non-breaking en dash". It's a way of putting in a dash and telling Word to make the ends "sticky" (so to speak) and keep everything next to it together.
How? Instead of just pressing the '-' key like you normally would, hold down 'Control', 'Shift', and '-'. This will insert a non-breaking en dash.
You can also make a "non-breaking space" using the same technique. Just hold down 'Ctrl', 'Shift', then press the space bar. This is pretty useful for things like legal documents where an entire name needs to stay together on one line. You can put a non-breaking space in between the parts of the name to make sure they'll stay together.
You can contact me any time you like! My phone number is 555-555-
5555.
Sometimes you'd prefer the number to stay together in one piece, like this:
You can contact me any time you like! My phone number is
555-555-5555.
In instances like this, most users would just press the Enter key before the phone number to make sure it's on its own line. But that's kind of sloppy, because you might add text later that makes it unnecessary for it to be the start of a line. It would be best to use what's called a "non-breaking en dash". It's a way of putting in a dash and telling Word to make the ends "sticky" (so to speak) and keep everything next to it together.
How? Instead of just pressing the '-' key like you normally would, hold down 'Control', 'Shift', and '-'. This will insert a non-breaking en dash.
You can also make a "non-breaking space" using the same technique. Just hold down 'Ctrl', 'Shift', then press the space bar. This is pretty useful for things like legal documents where an entire name needs to stay together on one line. You can put a non-breaking space in between the parts of the name to make sure they'll stay together.
Tuesday, August 25, 2009
Gone for now, back in a couple of weeks!
I'm very, very busy getting ready to represent Canada next week at WorldSkills Calgary 2009. Unfortunately I won't be posting any new tips and tricks until everything's over - but you can expect much more interesting/advanced content later! I haven't been sharing my best tricks yet, since I don't want to give the other competitors any great ideas.
Check out the official WorldSkills Calgary 2009 web site, and cheer on Team Canada as we take on the world next week! I'll be there to show my skills in the IT/Software Applications category.
Ciao for now!
Stacy
Subscribe to:
Posts (Atom)
