Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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)


0
Whux
Asked:
Whux
  • 7
  • 5
1 Solution
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
lmerrellCommented:
Then call it like this:

ConvDate(YourDate, CountryIndicator)

with CountryIndicator being either a U or a C.
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now