Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Unanswered: access - converting 01 into January, 02 into feb etc

    How can I show on a report the value of a MM into alpha?

    ex: 1 = jan, 2 = feb etc

    I have a field that is just 1, 2, 3 - but want to show it as jan, feb, mar on the report and have the report sort by these alpha months.
    Last edited by goblue18; 07-21-04 at 12:16.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    use "mmm" for your month format and then group accordingly.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2004
    Posts
    6

    Where do I place the mmmm?

    where do I place the mmmm format? and what does the syntax look like?

    thanks

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by goblue18
    where do I place the mmmm format? and what does the syntax look like?

    thanks
    mmmm is the FORMAT for the fully qualified name of the month (January, February, ...)

    mmm is the FORMAT for the "short" name of the month ...

    Try looking up the Format function in the help.

    As for where to place it? That depends on how your report is structured and how you're retrieving data ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Displaying Month Name

    Hi

    I must admit I cannot find a built in function that I can persuade to display the month from the month integer as you require.

    So I have written one as follows:-

    Code:
    Function ThisMonth(ByVal iMonth As Integer) As String
        
        Do Until iMonth <= 12
            iMonth = iMonth - 12
        Loop
        
        Select Case iMonth
            Case Is = 1
                ThisMonth = "January"
            Case Is = 2
                ThisMonth = "Feruary"
            Case Is = 3
                ThisMonth = "March"
            Case Is = 4
                ThisMonth = "April"
            Case Is = 5
                ThisMonth = "May"
            Case Is = 6
                ThisMonth = "June"
            Case Is = 7
                ThisMonth = "July"
            Case Is = 8
                ThisMonth = "August"
            Case Is = 9
                ThisMonth = "September"
            Case Is = 10
                ThisMonth = "October"
            Case Is = 11
                ThisMonth = "November"
            Case Is = 12
                ThisMonth = "December"
            End Select
        
    End Function
    This should be pasted into a global VBA module.

    You can use this in queries to display the text instead of the month integer.
    ie
    SELECT Field1, Fiels2, ThisMonth([month_integer_fieldname]) AS Month
    FROM tblYourTable;


    HTH

    If there is a built method please let us know !


    MTB

  6. #6
    Join Date
    Jul 2004
    Posts
    6

    Thanks

    thanks alot!!

    I found a way:

    have "beg date" define in a db as 06/04/1994

    Have another column where the field is defined as

    format([beg date], "mmm")

    Then I use that new column in the report.

    thanks

    the "mmm" will get you "feb"
    "mm" will get you "02"
    "m" will get you "2"
    "mmmm" will get you 'february'
    yy = 94
    yyyy = 1994
    dddd = monday
    dd = 08
    d = 8

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi agian

    That is just formatting a date!
    I thought (wrongly) that you wanted to turn the integer 2 (or CInt("02")) into "February" etc.

    Sorry.

    But an interesting execise!

    MTB

  8. #8
    Join Date
    Jul 2004
    Posts
    6

    ..

    I did want to do that, but then I realized that the original field was an actual date.

    thanks though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •