I'm pulling data into Excel using a SQL statement in VBA. I'd like to
pull an [updatedate] field and have it formatted in the format yyyy -
mm. I'm not sure how to do this however. I basically have this set
up right now.

Statement = "SELECT Insertby, Campaign, Resolution1, " & _
"Resolution2, Resolution3, Stage, MonthlyCost, " & _
"ProspectId " & _

I'd like to add into this something along the lines of

Format(UpdateDate,"yyyy - mm") AS UpdateMnthShrt

I'm not sure how to set the UpdateDate portion of the statement. Any
help would be GREATLY appreciated.

Thanks

Paul

Pepe,

Try the "To_Char" method.

TO_CHAR(UpdateDate, 'YYYY - MM')

HTH

Pepe,

Here's an idea which runs in Enterprise manager in SQL. Not sure how it will
work on a query from Excel, but worth a shot:

"SELECT Insertby, Campaign, Resolution1, Resolution2, Resolution3, Stage,
MonthlyCost, ProspectId, CONVERT(char(4), YEAR(UpdateDate)) + '-' +
CONVERT(char(2), MONTH(UpdateDate)) AS UpdateMnthShrt"

Robin Hammond
www.enhanceddatasystems.com


"Pepe" wrote in message
m...
I'm pulling data into Excel using a SQL statement in VBA. I'd like to
pull an [updatedate] field and have it formatted in the format yyyy -
mm. I'm not sure how to do this however. I basically have this set
up right now.

Statement = "SELECT Insertby, Campaign, Resolution1, " & _
"Resolution2, Resolution3, Stage, MonthlyCost, " & _
"ProspectId " & _

I'd like to add into this something along the lines of

Format(UpdateDate,"yyyy - mm") AS UpdateMnthShrt

I'm not sure how to set the UpdateDate portion of the statement. Any
help would be GREATLY appreciated.

Thanks

Paul

Robin, Thank you for the advice. It worked nicely for me. I have an
additional challenge now for you or anyone willing to help out. I
want to do something similar to what I did below, but this time
convert the UpdateDate into a Week Range (Sun - Sat). The format
would be

1/18/2004 - 1/24/2004

I had done something similar in Access with this formula:

Int((CDate(UpdateDate))-Weekday((CDate(UpdateDate)))+1) & " - " &
(Int((CDate(UpdateDate))-Weekday((CDate(UpdateDate)))+7)) AS
UpdateWeekRange

Thanks in advance for any help on this one.

Paul



"Robin Hammond" wrote in message ...
Pepe,

Here's an idea which runs in Enterprise manager in SQL. Not sure how it will
work on a query from Excel, but worth a shot:

"SELECT Insertby, Campaign, Resolution1, Resolution2, Resolution3, Stage,
MonthlyCost, ProspectId, CONVERT(char(4), YEAR(UpdateDate)) + '-' +
CONVERT(char(2), MONTH(UpdateDate)) AS UpdateMnthShrt"

Robin Hammond
www.enhanceddatasystems.com


"Pepe" wrote in message
m...
I'm pulling data into Excel using a SQL statement in VBA. I'd like to
pull an [updatedate] field and have it formatted in the format yyyy -
mm. I'm not sure how to do this however. I basically have this set
up right now.

Statement = "SELECT Insertby, Campaign, Resolution1, " & _
"Resolution2, Resolution3, Stage, MonthlyCost, " & _
"ProspectId " & _

I'd like to add into this something along the lines of

Format(UpdateDate,"yyyy - mm") AS UpdateMnthShrt

I'm not sure how to set the UpdateDate portion of the statement. Any
help would be GREATLY appreciated.

Thanks

Paul