Converting dates into text

[ddennis]

Dear all:

I have created a form for diploma printing purposes. I have created a date field in this format: 6/4/2004.

I wish to display the date in another field, but in this format: the fourth day of june two thousand four.

I am not much of a VB wizard, so any help is greatly appreciated.

Dion

 

[SydneyGeek]

Hi Dion,
you'll need to do this --
1. Create a table called tblLookup with an ID field (AutoNumber), InValue (Number) and OutValue (Text). The ID field is the Primary Key. Sample data is

tblLookup.xls
	A	B	C	D
1	LookupID	InValue	OutValue
2	1	1	First
3	2	2	Second
4	3	3	Third
5	4	4	Fourth
6	5	5	Fifth
7
8	23	23	Twenty-Third
9	24	24	Twenty-Fourth
10	25	25	Twenty-Fifth
11	26	26	Twenty-Sixth
12	27	27	Twenty-Seventh
13	28	28	Twenty-Eighth
14	29	29	Twenty-Ninth
15	30	30	Thirtieth
16	31	31	Thirty-First
17	32	2003	TwoThousandandThree
18	33	2004	TwoThousandandFour
19	34	2005	TwoThousandandFive
20	35	2006	TwoThousandandSix
21	36	2007	TwoThousandandSeven
22	37	2008	TwoThousandandEight
23	38	2009	TwoThousandandNine
24	39	2010	TwoThousandandTen
tblLookup

2. Place this code into a new module and call the module basDateFunctions:

Function WordyDate(TheDate As Date) As String
  Dim TheDay As String
  Dim TheMonth As String
  Dim TheYear As String
  Dim MonthArray(1 To 12) As Variant
  Dim i As Integer, j As Integer, k As Integer
  
  MonthArray(1) = "January"
  MonthArray(2) = "February"
  MonthArray(3) = "March"
  MonthArray(4) = "April"
  MonthArray(5) = "May"
  MonthArray(6) = "June"
  MonthArray(7) = "July"
  MonthArray(8) = "August"
  MonthArray(9) = "September"
  MonthArray(10) = "October"
  MonthArray(11) = "November"
  MonthArray(12) = "December"
  
  i = Month(TheDate)
  TheMonth = MonthArray(i)
  j = Day(TheDate)
  TheDay = DLookup("[OutValue]", "tblLookup", "[Invalue]=" & j)
  k = Year(TheDate)
  TheYear = DLookup("[OutValue]", "tblLookup", "[Invalue]=" & k)

  WordyDate = "The " & TheDay & " Day of " & TheMonth & ", " & TheYear
End Function

3. To use, create a calculated field in a query. Use an expression like PrintDate: WordyDate(dDate) where dDate is the name of the date field that you want to express in words. Output is:

qryWordy.xls
	A	B	C	D
1	dDate	FullName	PrintDate
2	30-Jul-04	BugsBunny	TheThirtiethDayofJuly,TwoThousandandFour
3	12-Jun-04	DaffyDuck	TheTwelfthDayofJune,TwoThousandandFour
qryWordy

You can adjust the text to suit what you need. It will do valid dates to 2010, at which time you can add more rows to the tblLookUp table.

Denis

 

[Norie]

Denis

Looks good

One thing though why not use

TheMonth = Format(TheDate, "mmmm")

to get the month.

 

[SydneyGeek]

Hi Norie,

thanks for the suggestion -- that would be an improvement. I've been doing a lot with arrays recently so that was my first thought

Denis

 

[Norie]

Denis

For years I used to do exactly what you did and I frequently used the Format function but it was only recently I found out about this.

 

[ddennis]

Dear Denis:

Many, many thanks.

Dion