Solved

Format a date in Jet sql

Posted on 2001-06-04
12
311 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
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.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 34
Access 2013 DoCmd.TransferSpreadsheet Not Working in Win 10 8 28
Why get error when delete all records on a sub-form 2 16
Help with DoEvents 8 26
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

773 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