Solved

Format a date in Jet sql

Posted on 2001-06-04
12
285 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 100 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now