30 June, 2009

Excel displays formula instead of calculating

Here's a new one I just ran into. I entered a new formula into a cell, and it just displayed the formula, instead of calculating the results. It turns out the problem was due to the field being formatted as a Text field. Unfortunately, just by changing the field to a Number does not solve the problem. You must change the field to a Number and then re-enter the formula.

26 June, 2009

Concatenate and Combine Access Rows

I was working with a dataset in Access, where I had a single field that was different to an otherwise exact data row. I wanted to merge these rows, so I had a unique record; I wanted the single field of different data to be concatenated together, as to not lose the data.

I was able to create a VBA function that I then called from a query to perform the merging of rows. It does have one shortcoming -- if you have duplicated data in the field you are concatenating, it may miss it and add it again. Here's what I mean:
  • Removed Duplicates: Data order a, a, a, b, c -- output = a, b, c
  • Creates Duplicates: Data order a, b, a, c, a -- output = a, b, a, c, a
Note that given the time/effort, you could improve my function to remove all duplicates.
Here's the function. Press Alt-F11 to get to the VBA Editor.
Public Function Concat(email As String) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Concat = ""
strSQL = "SELECT [Some ID] FROM MyTable WHERE [Email Address] = '" & email & "';"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rs.EOF

If Concat <> "" Then
' check for duplicate
If Left(Concat, Len(Concat) - 2) <> rs("[Some ID]") Then
Concat = Concat & rs("[Some ID]") & ", "
End If
Else
Concat = Concat & rs("[Some ID]") & ", "
End If

rs.MoveNext
Loop

'remove last ", "
Concat = Left(Concat, Len(Concat) - 2)
Set rs = Nothing
End Function
In your query call the function and pass the lookup variable -- in my case, it's an email address. See "Public Function Concat(email As String) As String" -- Concat is the function name and email is that variable that you are passing. The variable strSQL uses the email address passed to the function. Here's the expression in my query:
  • Expr1: Concat([MyTable]![Email Address])
I found this to be a valuable solution that can be re-applied to several situations in working with data in Salesforce.com.

24 June, 2009

My Experience Using Blogger with My Own Domain

UPDATE June 25, 09: I heard back from GoDaddy Support this morning, we clear and accurate instructions. I think by the time you see this, you will be able to once again get to my blog at www.CHRISdotTODD.com -- now I just need to get CHRISdotTODD.com to work.

I thought I knew my way around the web, how to develop and post content and apps, etc. One area I've not spent time with is domain registration and management. When I started CHRISdotTODD.com, it took about 3 clicks and $10 to get setup on Blogger (part of the Google family). I was led to believe that Blogger would make sure I had a chance to renew, and never lose my domain -- very similar to how they remind me annually to pay an extra $10 for extra space I use. This seemed like a great way to have my own domain, and not have to learn any more about the real guts that keep the Internet working for us.

Well, turns out on May 1st my domain registration had expired and Blogger never bothered to notify me. Since I've been busy with a project at cascadeblues.org and suffering from the flare-up of chronic pain in my neck, I wasn't paying too close to what was happening on CHRISdotTODD.com. When I finally discovered what was going on on May 25th, GoDaddy had already auction and sold my domain. You see Blogger outsources its domain management to GoDaddy.

When I notified Blogger, they said it was all my fault. Apparently I could have managed this through my "control panel." Well if you're a Blogger user, you know there is no Control Panel that provides this function. To make a long story short, the Control Panel is part of Google Apps, something I was not familiar with -- something I was apparently notified of -- a part of the domain hosting through Blogger. In fact, if you use the URL they gave me, http://mail.chrisdottodd.com/, it did (and still does) go to a site called Bay Area Style Files. I did eventually figure out how to get to the Control Panel, but by this time it was too late.

By this time I figured all was lost. I re-enabled my site with my older Blogger name, Skimming the Cream Off the Top, and figured I'd give myself some time to decide what I wanted to do. In the mean time, Blogger came back and offered me a new, free domain for 1-year and admitted to an error on their side. Since I wasn't sure what I was going to do, I declined. On June 15th Blogger reached out again and offered me $250 and a new domain for a year. Again since I hadn't resolved to what I wanted to do; the principle behind it -- now it wasn't just me thinking they made a mistake, they actually admitted it; and now with some renewed hope, I declined the $250 offer too.

On June 18th Blogger contacted me and said they got my domain name back. Just provide them with a GoDaddy account name, and they'd assign it to me. Naturally I was thrilled. When the transfer was complete on June 19, I logged into GoDaddy and into Blogger, trying to figure out how to reconnect the two. I followed a documentation trail on Blogger, which gave me instructions to set a CNAME through GoDaddy. Going to GoDaddy, I couldn't make heads-or-tails of the instructions. Apparently my DNS is not hosted by GoDaddy, so I cannot set the CNAME.

This is where the area of Internet mystery starts to sink in. Remember, I took the easy path through Blogger to begin with to avoid having to learn some of these gory details. So I emailed the same support rep at Blogger:
I looked at Blogger and GoDaddy, and I don't understand how to reconnect my domain with my Blogger account. The Blogger information talks of a CNAME and the instructions it lists for GoDaddy do not seem to align with my options on GoDaddy. Can you help me on this last step, too?
He sends me the same links that I just reviewed. Now I would think after all this, the least they could do was finish the configuration, so I would be functioning again on the Blogger service. Isn't the idea behind Blogger to make it easy for the non-technical to have their own personal blog? There are countless people less technical than me doing this every day.

Well I've sent another email to Blogger and also one to GoDaddy support. We'll see if we can get this last mile fixed by July 1st -- that would mean I only lost my domain for 2 months.

Who am I to expect anything more for $10 a year? In fact I didn't choose to continue with Blogger, using my own domain, for the low price. I chose it because I was familiar with the interface; generally happy with other Google products (had one Picasa incident); and saw no need to move to a different system (since I'd been there for 2.5 yrs already). Perhaps most important, it was easy to integrate with Googe AdSense. But it turns out that Google made much for than $10 from me, as AdSense payout is real poor. Now that's another story.

Common Reasons Why You Can't View a Video

One thing I've had to deal with in my last two (day) jobs is helping solve why some customers are unable to view our support and/or training videos. A recent problem for one customer led me to document what I know about the problems and likely solutions. Your feedback is welcome!
  1. Missing or out-dated codec. This applies to video files such as WMV, AVI, and MPG. These formats are all containers that can use various different codecs depending on the tool that was used to create. For example, GoToMeeting/GotToWebinar, WebEx, and TechSmith's Camtasia could all put out WMVs and AVIs, which have the same extension, but would not play unless you had their specific codec.
  2. Out-dated or missing player. This applies to MOV and RM. Since MOV is QuickTime, controlled by Apple, I don't often see problems because Apple pushes updates with iTunes. RM from Real Media is just not found much any more. When it is, most likely their is no player at all, so the download gets you the latest version.
  3. Out-dated or corrupt Flash Player. This applies to SWF and FLV files. I don't see this too often, but occasionally you will have someone who has not updated their Flash Player when the video we produce is from a new release software. I occasionally hear about a corrupt player that usually occurs after a browser update, such as the recent IE8. Given the choice, this is my preferred video format. As you likely know, it is also the format for YouTube and other video sharing sites.
  4. Browser Security Config. I think I've only seen this with Internet Explorer, and it can be very difficult to troubleshoot. The most common solution is adding your domain as a Trusted Site (e.g. http://www.blogger.com). The Compatibility Mode is something new with IE8 -- who knows what that'll bring. It is supposed to make IE much more WC3 compliant than prior versions (IE6 was bad and IE7 was worse).
    • This could also happen with a plug-in such as No Script for Firefox.
  5. Firewall and/or Proxy Security. The most common I've seen is the blocking of EXEs, so it doesn't apply to videos. I've seen file size restrictions, video file restrictions, and filtering that's too smart for its own good. IF you can ZIP something for someone and/or use an FTP site, that's typically the best solution. The best test for confirming this being the cause is to have the user try it from home.
  6. Other Misc. Local firewalls and virus scanners are the top of the misc list. Followed closely by pop-up blockers, which used to be very problematic, but have improved tremendously in the last few years.

22 June, 2009

Unable to Edit GoToWebinar Screen Captures

I recently for the first time recorded a GoToWebinar presentation. GoToWebinar, from Citrix, provides the option for their own proprietary output or WMV. I took the WMV option, to make it easier for my viewers, and give me the option to clean up any errors in the presentation.

Upon completion of the recording, I brought the WMV file into Windows Movie Maker. The source was 80MB (1 hr), and Windows Movie Maker split it into 8 pieces of various lengths. I performed normal edits, chopping out some dead space at two points, increasing the overall audio level, and adding intro and exit info. So far, so good.

Upon trying to save my project is where I ran into troubles. Trying several different settings, closing other programs to free memory, and rebooting -- nothing worked; it would not output. I then decided to try another computer, and realized I needed GoToWebinars own codec -- this was my first clue that I may not be able to solve this problem.

I tried two different encoders and importing into Camtasia -- still no better. Back on my original PC, I found some additional ideas from http://sam.charrington.com - somuchtolearn... so little time! I tried the recommended transcoding application that comes with GoToWebinar, which failed; I tried Windows Media Encoder, which looked promising, but it too failed.

I've come to the conclusion that GoToWebinar is not a good solution for recording presentations. I've had previous experience with WebEx, and have successfully been able to perform the same functions that I am unable to do with GoToWebinar. (Monthly fees for WebEx are known to be higher.)

If presenting with GoToWebinar is your only option, I recommend a screen capture of the presentation with a 3rd party tool such as TechSmith's Camtasia or Adobe Captivate. This usually requires a capture on a different PC than the one that is hosting the presentation. There are also many free and open source solutions such as Jing (from TechSmith), Windows Media Encoder (I discover in this process that it can be used for screen capture, though I have not tried myself), or CamStudio.

19 June, 2009

Enhanced Copy and Paste

First, sorry for the large gap in posts. In addition to having problems with losing my domain -- which I should have back soon -- I've been busy on another project.

I found a slick little utility to enhances copy and paste on Windows. As you know, once you copy new text, the previous is lost. With CopyPasteTool, if you continue to press V as you hold down the Ctrl key, it will scroll through prior copies.

I've used utilities in the past where you could view prior copies in a little window, manage, and save them. This is much simpler. Just keep pressing V as you hold Ctrl, and release Ctrl when you find the text you want.