Format a date in Jet sql

I want a date coming in to my
Access 2000 from a
query linked to an Oracle table.
I want it to be formatted in the
following manner :

1st day of January, 2001 OR
2nd day of April, 2001 OR
3rd day of December, 20001 OR
5th day of March, 2001 OR

I cannot have a leading zero on the day
as in (01st, 02nd, etc) must be (1st, 2nd)

I must have the right suffix after the day
as in (1st, 2nd, 3rd, 4th)


WhuxAsked:
Who is Participating?
 
lmerrellCommented:
Then call it like this:

ConvDate(YourDate, CountryIndicator)

with CountryIndicator being either a U or a C.
0
 
lmerrellCommented:
Use this function:

Public Function SuffixDate(D1 As Date) As String
      Select Case Day(D1)
        Case 1, 21, 31
          SuffixDate = Day(D1) & "st day of " & Format(D1, "mmmm, yyyy")
        Case 2, 22
          SuffixDate = Day(D1) & "nd day of " & Format(D1, "mmmm, yyyy")
        Case 3, 23
          SuffixDate = Day(D1) & "rd day of " & Format(D1, "mmmm, yyyy")
        Case Else
          SuffixDate = Day(D1) & "th day of " & Format(D1, "mmmm, yyyy")
      End Select
End Function
0
 
lmerrellCommented:
Save the function in a new module and name the module mdlSuffixDate.  Then use the expression below in your query replace yourdatefield with the name of you date field:

SuffixDate(yourdatefield)


lmerrell
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
WhuxAuthor Commented:
Works great...Thank you very much !!!
I have another twist to this though...so I'll add to the points so that it's fair.
I use the date the function creates at
at the bottom of a certificate
for service to the company for US members.
NOW I need to do the date in French !
I need to be able to take the
months the Format "mmmm" and somehow
convert the English to a French equivalent:
january = janvier
February = fevrier
March = mars
and so on
Can I do it in the same function all at one time ?


0
 
lmerrellCommented:
This would take a different function.  Give me a bit and I'll work one up for you.  It will need to pass the month number and language to another function.  What kind of indicator are you using for language?  String, numeric or boolean?
0
 
WhuxAuthor Commented:
Right now I have a one query that does
the English version only of a certifcate
of merit and another query that does
the French version only.
I could as easily have a separate function to do this
or I could pass a "C' or "U' string to the function so that it
can interpret the right language.
Examples:

"this 1st day of January, 2001"

"ce 1er jour de janvier, 2001"

So the date format has 4 components;

1.) this = ce

2.) the day suffix -
    1st, 21st, 31st = 1er, 21er, 31er
    all others = just e (as in 2e or 3e or 4e...etc)
(seems odd they don't distinquish between
nd, rd, th but this is what I was told was the
format per our office in Canada..theu should know)

3.) day of -
jour de

4.) the month -
January = janvier
February = fevrier
March = mars
April = avril
May = mai
June = juin
July = juillet
August = aout
September = septembre
October = octobre
November = novembre
December = decembre
(French months are NOT capitalized)

Thanks !!!
0
 
lmerrellCommented:
Hold on.  I'll get it for you.
0
 
lmerrellCommented:
Here you go.  Paste these in your module:

Public Function ConvDate(D1 As Date, L1 As String) As String
  If L1 = "C" Then
    Select Case Day(D1)
      Case 1, 21, 31
        ConvDate = "ce " & Day(D1) & "er jour de " & FrenchMonth(Month(D1)) & Format(D1, ", yyyy")
      Case Else
        ConvDate = "ce " & Day(D1) & "e jour de " & FrenchMonth(Month(D1)) & Format(D1, ", yyyy")
    End Select
  Else
    Select Case Day(D1)
      Case 1, 21, 31
        ConvDate = "this " & Day(D1) & "st day of " & Format(D1, "mmmm, yyyy")
      Case 2, 22
        ConvDate = "this " & Day(D1) & "nd day of " & Format(D1, "mmmm, yyyy")
      Case 3, 23
        ConvDate = "this " & Day(D1) & "rd day of " & Format(D1, "mmmm, yyyy")
      Case Else
        ConvDate = "this " & Day(D1) & "th day of " & Format(D1, "mmmm, yyyy")
    End Select
  End If
End Function


Public Function FrenchMonth(M1 As Integer) As String
  Select Case M1
    Case 1
      FrenchMonth = "janvier"
    Case 2
      FrenchMonth = "fevrier"
    Case 3
      FrenchMonth = "mars"
    Case 4
      FrenchMonth = "avril"
    Case 5
      FrenchMonth = "mai"
    Case 6
      FrenchMonth = "juin"
    Case 7
      FrenchMonth = "juillet"
    Case 8
      FrenchMonth = "aout"
    Case 9
      FrenchMonth = "septembre"
    Case 10
      FrenchMonth = "octobre"
    Case 11
      FrenchMonth = "novembre"
    Case 12
      FrenchMonth = "decembre"
  End Select
End Function
0
 
WhuxAuthor Commented:
I'll try it late this afternoon or tonight.
Thanks !!!
0
 
WhuxAuthor Commented:
Thanks.....
works Great !
0
 
lmerrellCommented:
Glad I could help!

lmerrell
0
 
WhuxAuthor Commented:
Yep...I'm a newbie to this.
I'm a Oracle developer.
We're migrating to Microsoft SQL*Server 7 and
Office 2000 from Oracle and Corel Suite.
We're just starting....
Man...what a lot to learn !
Now I'm trying to develope apps
in Access one day (jet sql & VBA),
then on the SQL*Server the next (T sql),
then on the Intranet the next (ASP, VBScript, JavaScript, VB, HTML, COM, &^%^%##@!#).
Boy, lots of options and lots of learning.
It's really great that ya can come here and get help so that you don't spend hrs/days trying to do
small things. Plus then I get to see how it's done and LEARN...hey I'm not a developer anymore
I'm a librarian !!!! constanly reading !
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.