Friday 15 February 2013

SQL–Overloading !

I came across this recently while considering the best way to create a function to deal with a SQL statement that could accept a varying number of parameters.

SELECT * FROM YourTable WHERE TableId = 2 AND Param1 = '%' AND Param2 = '%';

It means you don't need to use an overload function. Just swap out blank parameters for wildcard characters, meaning you don't need to do anything with the query as well. You could quite easily do this with any database engine, from Access to SQL Server.

Tuesday 12 February 2013

Converting an Excel date into PHP timestamp

I was recently asked by a customer to upload a large dataset extracted from their old Microsoft Office based database system into their shiny new web-based application (Built by the guys at Zypher).

The data included a lot of date fields which needed to be converted into UNIX timestamp to be used by the PHP based front-end.

We used the formula below to convert the dates, it assumes the date is in cell ‘A2’.

=(A2-DATE(1970,1,1))*86400

I hope it proves useful.

Saturday 19 January 2013

Calculate percentages with decimals in SQL?

Working with SQL Server recently I needed to calculate a percentage using values in other fields.  I went with the obvious field1 / field2 * 100 but got only a result of zero ?

After talking to everyone’s friend Google I came across the following stackover question and answer. It had the following answer:

CONVERT(
    DECIMAL(5,2),
      ( 100 * CONVERT(DECIMAL(5,2),[field1]) / CONVERT(DECIMAL(5,2),[field2]) )
) AS MyPercentAnswer

I hope it proves useful to you.

With thanks to those who answered the questions.

Sunday 9 December 2012

Pasting formulas between workbooks without workbook link

Have you ever needed to copy and paste a range of cells containing formula, each referencing cells in other worksheets, from one workbook to another ?  When you copy the formula across workbooks, Excel, by default, copies a link from the original file.  So instead of copying “=Sheet2!D6+Sheet2!E6” you end up with =[YourOriginalFile.xls]Sheet2!D6+[YourOriginalFile.xls]Sheet2!E6.

This has always frustrated me, I can see why it would be useful, but wouldn’t it be nice to have a simple way of copying the formula without the original file name.

For a while now I’ve been using a fairly simple work-around, using Edit / Replace.

  • Select the range you want to copy
  • Edit->Replace
  • What:= “=” (the equal sign)  /  With:=”^^” (or some unique string)
  • Replace All
Now the formula are text strings, these can be copied and pasted without Excel doing anything to “help” you.
  • Select the new range
  • Edit->Replace
  • What:= “^^” (or your unique string) / With:=”=” (equal sign)
  • Replace All

Now the text strings are formulas again.  You will need to ensure you have matching worksheets in that new workbook, otherwise you’ll be dismissing lots of dialogs.

Saturday 20 October 2012

Clear DNS cache

The ipconfig /flushdns command allow you to flush and reset the contents of the DNS client resolver cache. During DNS troubleshooting, if necessary, you can use this procedure to discard negative cache entries from the cache, as well as, any other dynamically added entries.
Resetting the cache does not eliminate entries that are preloaded from the local Hosts file. To eliminate these entries from the cache, edit the hosts file using a text editor.

To clear DNS Cache in client, do the following:

  • Start
  • Run
  • Type "cmd" and press enter
  • In the command window type "ipconfige /flushdns"

If everything has been run correctly you will received the following message "Successfully flushed the DNS Resolver Cache."  If you receive an error, "Could not flush the DNS Resolver Cache: Function failed during execution.", follow the Microsoft KB Article 919746 to enable the cache.

Monday 2 July 2012

MemoryUsed Property

I'd never come across this before and it doesn't appear in the IntelliSense drop-down, but it is quite a neat way of monitoring the current workbook size.

Per the MSDN page here:

This example displays a message box showing the number of bytes that Microsoft Excel is currently using.

MsgBox "Microsoft Excel is currently using " _
                  Application.MemoryUsed & " bytes"

Sunday 10 June 2012

Outlook 2010 Keyboard Shortcut Keys

Below are a few of the shortcut keys I find useful, with a few extras added from a colleague.

  • CTRL+1: Switch to Mail.
  • CTRL+2: Switch to Calendar.
  • CTRL+3: Switch to Contacts.
  • CTRL+4: Switch to Tasks.
  • CTRL+5: Switch to Notes.
  • CTRL+6: Switch to Folder List in Navigation Pane.
  • CTRL+7: Switch to Shortcuts.
  • CTRL+PERIOD: Switch to next message (with message open).
  • CTRL+COMMA: Switch to previous message (with message open).
  • CTRL+SHIFT+TAB or SHIFT+TAB: Move between the Navigation Pane, the main Outlook window, the Reading Pane, and the To-Do Bar.
  • CTRL+TAB: Move around message header lines in the Navigation Pane or an open message.
  • Arrow keys: Move around within the Navigation Pane.
  • ALT+B or ALT+LEFT ARROW: Go back to previous view in main Outlook window.
  • CTRL+Y: Go to a different folder.
  • F3 or CTRL+E: Go to the Search box.
  • ALT+UP ARROW or CTRL+COMMA or ALT+PAGE UP: In the Reading Pane, go to the previous message.
  • SPACEBAR: In the Reading Pane, page down through text.
  • SHIFT+SPACEBAR: In the Reading Pane, page up through text.
  • ALT+RIGHT ARROW: Go forward to next view in main Outlook window.
  • CTRL+SHIFT+I: Switch to Inbox.
  • CTRL+SHIFT+O: Switch to Outbox.
  • CTRL+K: Check names.
  • ALT+S: Send.
  • CTRL+R: Reply to a message.
  • CTRL+SHIFT+R: Reply all to a message.
  • CTRL+ALT+R: Reply with meeting request.
  • CTRL+F: Forward a message.
  • CTRL+ ALT+J: Mark a message as not junk.
  • CTRL+SHIFT+I: Display blocked external content (in a message).
  • CTRL+ SHIFT+S: Post to a folder.
  • CTRL+SHIFT+N: Apply Normal style.
  • CTRL+M or F9: Check for new messages.
  • UP ARROW: Go to the previous message.
  • DOWN ARROW: Go to the next message.
  • CTRL+N: Create a message (when in Mail).
  • CTRL+SHIFT+M: Create a message (from any Outlook view).
  • CTRL+O: Open a received message.
  • CTRL+SHIFT+D: Delete and Ignore a Conversation.
  • CTRL+SHIFT+B: Open the Address Book.
  • INSERT: Add a Quick Flag to an unopened message.
  • CTRL+SHIFT+G: Display the Flag for Follow Up dialog box.
  • CTRL+Q: Mark as read.
  • CTRL+U: Mark as unread.
  • CTRL+SHIFT+W: Open the Mail Tip in the selected message.
  • ALT+ENTER: Show the properties for the selected item.
  • CTRL+SHIFT+U: Create a multimedia message.
  • CTRL+SHIFT+T: Create a text message.
  • CTRL+ALT+M: Mark for Download.
  • CTRL+ALT+U: Clear Mark for Download.
  • F9: Send and Receive.
  • CTRL+B (when a Send/Receive is in progress): Display Send/Receive progress.
  • CTRL+SHIFT+D: Dial a new call.
  • F3 or CTRL+E: Find a contact or other item (Search).
  • F11: Enter a name in the Search Address Books box.
  • SHIFT+letter: In Table or List view of contacts, go to first contact that starts with a specific letter.
  • F5: Update a list of distribution list members.
  • CTRL+Y: Go to a different folder.
  • CTRL+SHIFT+B: Open the Address Book.
  • CTRL+SHIFT+F: Use Advanced Find.
  • CTRL+SHIFT+PERIOD: In an open contact, open the next contact listed.
  • F11: Find a contact.
  • ESC: Close a contact.
  • CTRL+SHIFT+X: Send a fax to the selected contact.
  • CTRL+N: Create a new appointment (when in Calendar).
  • CTRL+SHIFT+A: Create a new appointment (in any Outlook view).
  • CTRL+SHIFT+Q: Create a new meeting request.
  • CTRL+F: Forward an appointment or meeting.
  • CTRL+R: Reply to a meeting request with a message.
  • CTRL+SHIFT+R: Reply All to a meeting request with a message.
  • ALT+0: Show 10 days in the calendar.
  • ALT+1: Show 1 day in the calendar.
  • ALT+2: Show 2 days in the calendar.
  • ALT+3: Show 3 days in the calendar.
  • ALT+4: Show 4 days in the calendar.
  • ALT+5: Show 5 days in the calendar.
  • ALT+6: Show 6 days in the calendar.
  • ALT+7: Show 7 days in the calendar.
  • ALT+8: Show 8 days in the calendar.
  • ALT+9: Show 9 days in the calendar.
  • CTRL+G: Go to a date.
  • ALT+= or CTRL+ALT+4: Switch to Month view.
  • CTRL+RIGHT ARROW: Go to the next day.
  • ALT+DOWN ARROW: Go to the next week.
  • ALT+PAGE DOWN: Go to the next month.
  • CTRL+LEFT ARROW: Go to the previous day.
  • ALT+UP ARROW: Go to the previous week.
  • ALT+PAGE UP: Go to the previous month.
  • ALT+HOME: Go to the start of the week.
  • ALT+END: Go to the end of the week.
  • ALT+MINUS SIGN or CTRL+ALT+3: Switch to Full Week view.
  • CTRL+A: Select all contacts.
  • CTRL+F: Create a message with selected contact as subject.
  • CTRL+J: Create a Journal entry for the selected contact.
  • CTRL+N: Create a new contact (when in Contacts).
  • CTRL+SHIFT+C: Create a new contact (from any Outlook view).
  • CTRL+O: Open a contact form for the selected contact.
  • CTRL+SHIFT+L: Create a distribution list.
  • CTRL+P: Print.
  • CTRL+ALT+2: Switch to Work Week view.
  • CTRL+COMMA or CTRL+SHIFT+COMMA: Go to previous appointment.
  • CTRL+PERIOD or CTRL+SHIFT+PERIOD: Go to next appointment.
  • CTRL+E: Find a message or other item.
  • ESC: Clear the search results.
  • CTRL+ALT+A: Expand the search to include All Mail Items, All Calendar Items, or All Contact Items, depending on the module you are in.
  • CTRL+SHIFT+F: Use Advanced Find.
  • CTRL+SHIFT+P: Create a new Search Folder.
  • F4: Search for text within an open item.
  • CTRL+H: Find and replace text, symbols, or some formatting commands. Works in the Reading Pane on an open item.
  • CTRL+ALT+K: Expand search to include items from the current folder.
  • CTRL+ALT+Z: Expand search to include subfolders.

If you spot any mistakes, or have any to add let us know through the comments.