MS Access 2003/2007 VBA - How can I take a date field from a recordset and string the dd-MMM-yyyy format?

Question

i have some VB that uses DAO to grab some data, one field being a date field (as in date of a transaction). its not date/time, just simply date like dd/mm/yyyy.

so on an access form i know how to do this, but right now i am working on some excel/ppt automation. i use something like this simple example DAO sql string, openrecordset to get the data.

So lets say the data in question is just rs!Date.

I move it to powerpoint like so:

Set oShape = oSlide.Shapes("S1_Date")
Set oTextRange = oShape.textFrame.TextRange

     oTextRange.Text = rs!Date

now i am leaving out all kinds of stuff, but this is the part that transfers this date that I already have in the recordset, on to the ppt pres just fine, only in this format

dd/mm/yyyy

and i would really just like to know how to simply get this

"dd-MMM-yyyy"

as my desired output string.

thanks justin

Answer 1

Use the Format function.

Try this in the debug window Msgbox Format (Now(), "dd-MMM'YYYY").

http://office.microsoft.com/en-ca/access-help/format-function-HA001228839.aspx

Answer 2

Maybe:

oTextRange.Text = Format(rs![Date], "dd-MMM-yyyy")

I enclosed the field name in square brackets because Date is a reserved word. But I don't think it should make any difference in this case. Nevertheless, try to avoid reserved words for your field, table, and other object names.

Answer 3

Ok so this is the first time I am ever answering my own question, so if i am not supposed to do this....apologies.

i figure i would show what I did incase anyone else wants to know

Set oShape = oSlide.Shapes("S1_Date")
Set oTextRange = oShape.TextFrame.TextRange

     oTextRange.Text = Format(rs!Date, "dd-MMM-yyyy")

and that was it. it was easy enough for me to figure out myself.

EDIT: Oops....sorry did not have other answers when i posted this!