Hello,
I'm having trouble with my code as I'm trying to automate a file.
What I'm trying to do is bring a portion of the file name in as a date. It doesn't appear to work.
My file is named Email by Client - Feb.xls
My logic seems sound. I am taking the FileBaseName, searching the string for only the 3 characters from the right that I’ve named to abbreviate the month, turning that month into a numeric value, and then trying to turn it into a date.
If I use just (right(FileBaseName(),3) the word “Feb” comes in correctly. It’s when I add num and date that don’t seem to transform it into a numeric date. The reason I want to turn it into a numeric date is so that I can drop a bunch of files in this folder and add "Mar, May, June, etc" to their file names, essentially having a file for each month.
LOAD Department,
Total,
[Under 24],
Percentage,
date(num(right(FileBaseName(),3))) as Month
FROM
(biff, embedded labels, table is [Worksheet$]);
