Posts for 06/2009

28 June 2009 - 07:00

Using dates in stead of date-references in Excel formulas

If you're calculating with dates in Excel and you want to refer to a hard-coded date in stead of using references to your dates, you'll be in for a surprise.
In Access you can refer to a literal date using the hashes, e.g. #30/06/2009#, but Excel does not understand that.

So following formulas aren't going to do what you intend it for or will throw out errors:

=IF(A2<=31/3/2009;"Trim1";IF(A2<=30/6/2009;"Trim2";IF(A2<=30/9/2009;"Trim3";"Trim4")))
=IF(A2<="31/3/2009";"Trim1";IF(A2<="30/6/2009";"Trim2";IF(A2<="30/9/2009";"Trim3";"Trim4")))
=IF(A2<=#31/3/2009#;"Trim1";IF(A2<=#30/6/2009#;"Trim2";IF(A2<=#30/9/2009#;"Trim3";"Trim4")))
You need to use the Datevalue () function which transforms a date-string into a real date Excel can understand and handle. So don't forget to put the hard-coded dates between quotes " " :-)
=IF(A2<=DATEVALUE("31/3/2009");"Trim1";IF(A2<=DATEVALUE("30/6/2009");"Trim2";IF(A2<=DATEVALUE("30/9/2009");"Trim3";"Trim4")))
(0) Comments ·
22 June 2009 - 13:10

Glass of Milk

In the margin of mail, hoaxes and truth

Every regular internet and/or mail user knows the phenomenon: the multitude of email messages which circle around and are not really spam (well... depends on how you see if of course, and let alone also junk, obscene etc.), which are not strictly necessary and/or professional too, nor really personal, but do land in our inbox because you're email address has landed on one of your colleague or friends email lists.
Because he or she enjoys or finds it great or useful to share that great powerpoint presentation, essential advice, not to be missed happening, or inspirational message...

It's always good to check though. As a friend of mine experienced recently with the each year coming back message about the extra-ordinary Mars position.

So ok, it's summer time after all, here a nice story which landed recently in my mailbox, and at least seems to be half true, or 'inspired on real facts' following this source: http://www.truthorfiction.com/rumors/o/oneglassofmilk.htm. A story which can stand well as introduction on an exchange or discussion on the topic of the universal law of action and reaction, the law of karma :-)

[Read more on Glass of Milk...]
(0) Comments ·
21 June 2009 - 01:58

Automising updates for items in Outlook

attribute categories quickly

Here are two pathways to increase productivity in Outlook.

Suppose you wanted to apply the same category to multiple Outlook items eg, or you wanted to set a whole range of tasks as completed. Or change the due date for a whole bunch of tasks.
U see the point?

One interesting approach is that in Outlook you can always drag and drop many items onto one grouped header.
Make sure you have one item attributed to the category you envisage, use the group by view, then simply select all the items you wanted to categorize and drag and drop them on top of the category header.
Or in the tasks folder, group by Due Date. Then open and change the Due date of 1 Task so that you get a destination group. Then drag the tasks for which you want to change the Due Date on top of the group header.

You can of course also go the VBA path.
[Read more on Automising updates for items in Outlook...]

(0) Comments ·
17 June 2009 - 11:56

New webfonts on the firmament?

If you're in for some experimenting with web fonts and/or you want a change from the sIFR approach, then you could consider using the Typekit service.

Kevin Yank from Sitepoint recently addressed an article on this topic under the title: Get Real with Typekit.

Typekit introduced Typekit recently and that immediately triggered of an ocean of comments.
So that might be an inspiration.
If you prefer to evaluate a practical implementation: nicewebtype.com is your place to be.

(0) Comments ·

Change the default protection message in an Excel sheet

Is there a way to change the message that is displayed when the user tries to modify a locked cell on a protected sheet?
That's exactly the question I was asked during a course.

The answer apparently is : not really - following the discussion on this thread: http://www.xtremevbtalk.com/showthread.php?t=224139, the conclusion probably is: prevent the use of being able to go into the cell at the first place.
Just make sure the option 'select locked cells' is DE-activated when you activate the protection on the worksheet

Then also you could add this VBA code to your sheet tab

Private Sub Worksheet_Activate()
If ActiveSheet.ProtectContents Then
  MsgBox "Watch out, you're going to have some funny messages" _
  & "if you try to change some protected cells in this sheet"
Else
  MsgBox "Sheet is not protected"
End If

End Sub
(0) Comments ·
16 June 2009 - 16:11

To CSS or not to CSS: Object Oriented CSS

I knew it, but couldn't really put the exact wordings on it.
There must be another way to CSS.

Because looking at the talk Nicole Sullivan did for the Yahoo Developer Network Theatre, I have to admit : I did it too - I am co-responsible too in having contributed to following observations regarding CSS:

  • CSS requires quite a bit of expertise : yes I've been writing good semantic stuff, but web developers over there had hard time to understand with basic CSS knowledge...
  • File size just keeps getting bigger: yes to solve issues, match design requirement and so on, I've seen the sizes of my CSS files growing...
  • Code reuse is just not happening: yes it's true, I haven't made that extra effort to ensure that code for returning parts would be completely and easily reusable...
  • Code is too fragile : well indeed I was already happy if I could get it all together in a semantic way for all browsers...

And some other good practices

So her talk was on 'Object Oriented CSS' for high performance web applications and site and summarizes 2 of the main proposed principles nicely as follows -:

  1. Separate structure from skin - separate the CSS to layout, position, structure including browser compatibilities etc. from how it should look like
  2. Separate container and content - separate the CSS for the object, the placeholder, the container from what should go in, so that anything can go in
Teaching Object Oriented CSS [Read more on To CSS or not to CSS: Object Oriented CSS...]
(0) Comments ·
15 June 2009 - 14:17

Corrupted files in MS Project (2007)

During a MS Project coaching at a client (with Project 2007), we encountered something I had never had before : a corrupted and completely unusuable project file. It concerned a master project file and so one of the inserted plans became completely corrupt. No way of opening, inserting it again, exporting it to something else...
The best you could get out of it was a cryptic

"The operation could not be completed because the source file contains invalid project data or the total number of rows exceeds the limit of 1,048,000 rows in a project."

What happened ?

After some research (and as usual having tried multiple pathways, one being the fact that the files were converted from Project 2003) it seemed to be linked to a password set on one of the files and the fact of having copied the files... yes indeed: just copied.

Well IT never gets as weird as it gets (?!), but the closest explanation from Microsoft I could find, was in their knowledgebase regarding a bug in version 2002, which then got solved.

Perhaps it re-incarnated?

The solution of course still stands in its simplicity: don't use a pasword, or remove the password, then copy/move the file, and eventually put the password after the copying.
And if ever you bump onto file corruption in MS Project, you could try out the Microsoft Project MVP page's solution.

(5) Comments ·
14 June 2009 - 09:02

Check for duplicate values in Access tables

How to check doubles or duplicate values in Access tables?
Voilà another question which comes up regularly.

To start I'd like to point out to Microsoft Office Online which actually published a good article on this topic : http://office.microsoft.com/en-us/access/HA010345581033.aspx. Not only it explains about the use of Access' inbuild Find Duplicates Query Wizard, but it also shows SQL, illustrates different scenarios and clarifies the Unique Records and Unique Values properties.

The famous Allen Brown with his many tips for different users also has a strategy: http://allenbrowne.com/xbase-04.html

The VBA road then.

Here is a good approach to avoid duplicates being entered in a form: http://www.databasedev.co.uk/duplicates.html.

The book ‘Building Access Applications’ by J. Viescas proposes a whole module on the topic of duplicates. In one chapter there's the description of a support database, Membership.mdb with a form ”frmCustomers” haing a vba module to check doubles for multiple fields.
Not that easy perhaps if you're not fluent with VBA programming, but really worth a visit and consideration. John Viescas developed a Public Function fctCheckSaved() called through an event onFormUnload – the function calls for another function soundex() which even checks for names with similar sound or pronunciation, but which can be omitted. The essence of this modules follows here as an example but you can download the database with the functions from his site.or see more code on MSDN

[Read more on Check for duplicate values in Access tables...]
(0) Comments ·
10 June 2009 - 12:09

Focus on global warming or climate changes

Two links with video materials on the topic of climate changes landed in my inbox around the same time.

This 'promotion' or 'commercial' is a beautiful illustration how you can use creativity to shift the focus of consciousness and provoke insight:
http://www.coloribus.com/adsarchive/tv-commercials/global-warming-when-you-feel-it-its-already-too-late-543922/

Extract Home Film - Rajasthani well

On another note, you've probably heard of or seen the 'Home' film already.
'Home', the film by Yann Arthus-Bertrand, actually does the same thing. With great sense for beauty and creative composition, he mixes a good hour of stunning views on our planet, most airy, to conclude with a less airily invitation to reconsider the way we deal with our planet and set small practical steps in the good direction.
Just in case you didn't: www.home-2009.com and www.youtube.com/homeproject

(0) Comments ·
03 June 2009 - 11:04

Queen Elisabeth Competition

Queen Elisabeth Competition

One of my favorite music happenings each year is the Queen Elisabeth Competition in Brussels, Belgium.
It's one of those rare happenings which manages to make a broader public open up to the wonders of classical music. This contest cycles in 3 or 4 years periods, dedicated to singing, violin and piano, whereby each year there's a parallel contest for compositions; the winning compositions then become imposed work for the young artists (as there's an age limit now up to 25 y).

This year was dedicated to violin, and although I'm not such a fan of this instrument, the concerto's of the finalists were absolutely marvelous. You can hear/see them here, for as long as those links will be operational:

http://video.cmireb.be/vod#

http://kew.klara.be/audio-video-foto/video/herbekijk-de-concertos

Next year is my favorite one: piano !

(1) Comments ·
Page 1 of 1 pages