access vba training - Date formats in Functions

James

Yes I have an idea. The TEXT function does not work as I presented it. I didn't notice the problem because I've been messing with my computer and had the date on Jan 2008.

I then checked some books and they all say there is no way of changing the months value to Text.

So I created a Function that takes the date you specify ie Totay() and by checking the Month value retrurns a month's name.

The code is:

Public Function MonthText(MyDate As Date) As String

Dim Monthvalue As Byte

MonthValue = Month(MyDate)

Select Case MonthValue

Case 1
MonthText = "January"

Case 2
MonthText = "February"

Case 3
MonthText = "March"

Case 4
MonthText = "April"

Case 5
MonthText = "May"

Case 6
MonthText = "June"

Case 7
MonthText = "July"

Case 8
MonthText = "August"

Case 9
MonthText = "September"

Case 10
MonthText = "October"

Case 11
MonthText = "November"

Case 12
MonthText = "December"

End Select

End Function

Then your formula would read

="USD Balance as at "&DAY(TODAY()) & " " & MonthText(TODAY()) & " " &YEAR(TODAY())

This should solve your problem

Carlos