MS Query - Case Statements, Excel Data file

**excelhurts** · 04-29-2009, 09:29 AM

I know that for MS Access, Case statements won't work. Does anyone know if they work when using an Excel file as the data source?

Or maybe it's just my formatting is wrong. I've attached a sample of the data file, and the code I'm using is below:

SELECT `Sheet1$`.Author,
CASE
WHEN `Sheet1$`.Author='me'
THEN 'OK'
ELSE 'nope'
END
FROM `sample.xls`.`Sheet1$` `Sheet1$`

Basically, if the Author field indicates me, then I know I commented. Otherwise, I know I need to comment.

**broro183** · 05-01-2009, 06:19 PM

hi,

Yes, Case statements can work in Excel's VBA code but the syntax differs - check out the examples in http://www.ozgrid.com/VBA/select-case.htm
However, I think you could achieve the same thing without resorting to code... highlight the cells in the author column & apply Conditional Formatting [alt + O + D], select "cell value is" & "not equal to" & type "me" in the dropdown, then click on Format & choose a colour.

hmmm...
The more I look at your post alongside its title, the more I think I've misunderstood & given a poor answer above, so I'll try again...

You may find something of use at the below links but I hope with this "bump" that someone more knowledgeable will be able to give you a definitive answer.
http://help.lockergnome.com/office/M...ict754381.html (see pbbellri's last post)

http://www.advanced-excel.com/import_data.html

hth
Rob