date to english function and tabledef ?

[mortgsteps]

Is there a simple function or code snippet to convert a date such as 06/01/1999 to 1st of June 1999

and

how can i do the following assuming all variables are declared correctly:

this works fine
set tdf = mydb.tabledefs!orders
debug.print tdf.fields.count

I want to pass the name of the table as a variable i.e.

tablename = "orders"
set tdf = mydb.tabledefs! & tablename
debug.print tdf.fields.count

i get an object error
thanks [sig][/sig]

 

[kathryn]

I think that it should be:

tablename = "orders"
set tdf = mydb.tabledefs(tablename)
debug.print tdf.fields.count


As to your date question, if you actually want 1st, 2nd, 3rd, etc. I think you are going to have to make a cross-reference table with two columns, the cardinal number and the associated ordinal number.

Then creating the date the way you want it would be very easy. You could create a function that took a date as an input and have the function output the format you want.

Let me know if this sounds like what you are looking for. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[mortgsteps]

thanks kathryn - you are going to spoil me
with such a fast and accurate response. i really appreciate it. [sig][/sig]

 

[MichaelRed]

morgsteps,

You can come CLOSE to the date format thinggggyyyyy with the standard in VBA:

? Format(#6/1/99&quot;, &quot;Medium Date&quot

(actually returns &quot;01-Jun-1999&quot

Closer w/:

? Format(#6/1/99&quot;, &quot;d mmmm 1999&quot
(actually returns &quot;1 June 1999&quot

alas, kathryn is correct, to do the WHOLS NINE YARDS you will need the look-up to add the text chars after the date.

However I think the function below will be somewhat easier than the whole table XRef thinnnngggggyyyyy.

Public Function DateToEngl(DateIn As Date) As String

Select Case Day(DateIn)
Case 1, 21, 31
DateToEngl = Trim(Str(Day(DateIn))) & &quot;st of &quot; & (Format(DateIn, &quot;mmmm yyyy&quot)

Case 2, 22
DateToEngl = Trim(Str(Day(DateIn))) & &quot;nd of &quot; & (Format(DateIn, &quot;mmmm yyyy&quot)

Case 3
DateToEngl = Trim(Str(Day(DateIn))) & &quot;rd of &quot; & (Format(DateIn, &quot;mmmm yyyy&quot)

Case Else
DateToEngl = Trim(Str(Day(DateIn))) & &quot;th of &quot; & (Format(DateIn, &quot;mmmm yyyy&quot)
End Select


End Function
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[kathryn]

Michael,

Your right, I didn't take the time to analyze the ordinal numbers to see the rules. Also, I should have realized that with only 31 days, there had to be a limited list.

Collaboration is great!

Kathryn [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[kathryn]

Michael,

You're right, I didn't take the time to analyze the ordinal numbers to see the rules. Also, I should have realized that with only 31 days, there had to be a limited list.

Collaboration is great!

Kathryn [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]