Conversion Date to Words

[sanbhau]

I have two fields in the a table. These two fields are Emp_ID and DateOfBirth. The DateOfBirth field is formatted as medium date. I want to convert these dates into words such as 18-Apr-1960 into Eighteenth April, Nineteen hundred and sixty in corresponding query.

Please guide me in as simple terms as possible considering my lower skill level.

[CJ_London]

think you will need a UDF (User Designed Function) to combine data from a lookup tables

alternatively you could adapt this code

https://support.microsoft.com/en-us/kb/213360

Returning to my suggestion the table would look like this

tblDatestxt
Num....DayTxt.........YearTxt
1.........First............One
2.........Second........Two
...
...
19.......Nineteenth...Nineteen
...
...
60.......""...............Sixty
61.......""...............Sixty One

all the way up to 99 - obviously DayTxt would be blank after 31

then a UDF (put in a module with a different name to the function)

Option Compare Database
Option Explicit

Public Function DateToTxt(Target as Date) as String
Dim Datetxt as string

    Datetxt=dlookup("Daytxt","tbleDatestxt","[Num]=" & day(target)) 'add day
    Datetxt=Datetxt & " " &  format(target,"mmmm") 'add month
    Datetxt=Datetxt & ", " & dlookup("Yeartxt","tbleDatestxt","[Num]=" & left(year(target),2)) 'add first part of year
    Datetxt=Datetxt & " hundred and " & dlookup("Yeartxt","tbleDatestxt","[Num]=" & right(year(target),2)) 'add second part of year

    DateToTxt=Datetxt

End Function

You may need a third column in the table if you don't like 'twenty one hundred and sixteen', but for you to play around with

[sanbhau]

thanks for helping my problems have been sorted out.