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.
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.
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.
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.
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.
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:
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.
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"
Below are a few of the shortcut keys I find useful, with a few extras added from a colleague.
If you spot any mistakes, or have any to add let us know through the comments.
