Question Datepart (1 Viewer)

[mousemat]

Hi all

I am creating a access db for use with a training company. All is working great.

What I want to do is create certificates within Access and then print by the Course load. Easy enough and I can do this.

The courses usually last two or three days, so i want to create a field which has the start and finish date on the certificate formatted correctly.

I currently use this

CourseDate: DatePart("d",[StartDate]) & " & " & DatePart("d",[EndDate]) & "  " & Format([EndDate],"mmmm") & "  " & DatePart("yyyy",[EndDate])

within a query which returns this

29 & 30 May 2013

what i want to print on the certificate is this

29th & 30th May 2013

I need to have the 'st' or 'nd' or 'rd' or 'th' in the date.

Im guessing some sort of VBA code, but cant think how or where to put it. I suppose in the report would be the place, but it might be nicer if it was in the query so I could use it elsewhere.

Anyone have any ideas?

 

[spikepl]

and if it runs across the end of the month?

 

[plog]

You're going to have to write your own function to do that. Check out this post:

http://www.access-programmers.co.uk/forums/showthread.php?t=101895

 

[mousemat]

Spikepl

Hadn't thought about, up till now, that hasn't happened, but I can see there will be a time when it does happen.

plog

thanks for the link. i will have a look at that