?
Solved

Format a date in Jet sql

Posted on 2001-06-04
12
Medium Priority
?
340 Views
Last Modified: 2007-11-27
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
Comment
Question by:Whux
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 7

Expert Comment

by:lmerrell
ID: 6153954
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
 
LVL 7

Expert Comment

by:lmerrell
ID: 6153969
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
 

Author Comment

by:Whux
ID: 6164214
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 7

Expert Comment

by:lmerrell
ID: 6164282
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
 

Author Comment

by:Whux
ID: 6164398
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
 
LVL 7

Expert Comment

by:lmerrell
ID: 6164414
Hold on.  I'll get it for you.
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 6164473
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
 
LVL 7

Accepted Solution

by:
lmerrell earned 400 total points
ID: 6164485
Then call it like this:

ConvDate(YourDate, CountryIndicator)

with CountryIndicator being either a U or a C.
0
 

Author Comment

by:Whux
ID: 6164514
I'll try it late this afternoon or tonight.
Thanks !!!
0
 

Author Comment

by:Whux
ID: 6165503
Thanks.....
works Great !
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 6165523
Glad I could help!

lmerrell
0
 

Author Comment

by:Whux
ID: 6165601
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question