Formatting Date

[Gretchen]

Ok, this is an easy one for you *but obviously not for me*

I have a field called ClassDate. This is the first day of the class. On the certificate I want the last day of the class to show. I've used =DateAdd ("d", 4, [ClassDate]) and it's perfect. However, instead of formatting mmmm dd, yyyy I need it to read as an example:

This 24th day of April, 2002

How do I format this?

Thanks so much for your help.

 

[bri822]

I just lookded at this breifly, but I think what you can do is use the DAY, MONTH, and YEAR functions and some simple coding. Maybe something like
"This " & DAY(your date) & "th day of " & functionMonth(MONTH(your date)) & ", " & YEAR(your date)
The only hard part is writing functionMonth, but you just need to write a function that takes one integer 1-12, and returns the corresponding month, ie. 1 -> Janurary, etc.

I have not programmed too much with Access, so I can't really tell you how to write it, but if you look through some of the postings in the VBA section of this board I imagine you can get a good idea. And since I have not been working with Access that much, I also am not sure if this will work. This is what I would try, but I am far from being a professional. Good luck!

Brian

 

[Paul_Bricker]

Brian is very close
Try this

"This " & Format(dteholder, "d") & "th day of " & Format(dteholder, "mmmm") & ", " & Format(dteholder, "yyyy")

dteholder is the name of your date field. You'll have to make that change in the expression.
The issue is going to be the "th" after the day. The th isn't correct for may of the days in the month. If you classes always start early in the month then you might get away with it. I didn't use the Day and Year function like Brian did simply for consistency throughout the expression. The only one you need to use the Format function for is the month, so you get the full name of the month and not the integer representation of the month.
HTH
Paul

 

[Gretchen]

Thanks guys. I really appreciate the help! One of the nicest things about this forum is that answers come so quickly! I don't have to put aside what I'm doing for days to get a "fix"!!

Again, you have my gratitude.