Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2004
    Posts
    11

    Unanswered: Expression HELP - Convert text Month Name to Number

    I have an Access database query with a month column (with full names of months in them) . I need to build an expression that will take

    DECEMEBER and make it 12
    November and make it 11
    October and make it 10 and so on.

    I tried this and it worked for 1 month at a time ****** Expr1: IIf([month]="January","1") ****** but how would I format it to make all the months corespond with the right number?

    The table value is not a month field it is a text field. so I don't think any of the date variables will work. I hope this is simple one for you access guys out there. I really need some help on this one.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm sure there's a way to do this using access specific functions, but I couldn't find one. So I made one:

    Code:
    Public Function StrToMonth(strIn As String) As Integer
    Dim arrMonth(12) As Variant
    Dim i As Integer
    
    arrMonth(0) = "January"
    arrMonth(1) = "February"
    arrMonth(2) = "March"
    arrMonth(3) = "April"
    arrMonth(4) = "May"
    arrMonth(5) = "June"
    arrMonth(6) = "July"
    arrMonth(7) = "August"
    arrMonth(8) = "September"
    arrMonth(9) = "October"
    arrMonth(10) = "November"
    arrMonth(11) = "December"
    
    For i = 0 To UBound(arrMonth) - 1
        If strIn = arrMonth(i) Then
            StrToMonth = i + 1
            Exit Function
        End If
    Next i
    Usage:

    StrToMonth("January")

    returns 1
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you could also JOIN to a table
    "January", 1
    "February", 2
    "March", 3
    etc

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2004
    Posts
    11
    Teddy,thanks for the speedy reply. I am very new to access. Where would I put this information. I am trying ot paste it into expression builder and it will not take. Do I need to take it to SQL view?

    My statement there reads the following:

    SELECT DISTINCTROW customers.ID, customers.month, Count(*) AS total_requests, customers.year
    FROM customers
    GROUP BY customers.ID, customers.month, customers.year;

    I am confused. But I know what you have done will work if I can get it in the right place.

    Thanks


    I should add that I would like to leave the month name in my query and add an addition colum with the numeric value of the month colum field

    Thanks Again
    Last edited by rrayfield; 12-29-04 at 12:25. Reason: additional info

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Looks like you have some research to do on using modules huh?

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2004
    Posts
    11
    OK.... Looks like an am very new now right???

    OK, I took your code and created the module in Visual Basic editor. Then I went back to my query and inserted this into my expression builder

    monthnumber: StrToMonth([month])

    I ran the query and I get the error
    ** Compile Error: expected end function **

    What am I missing??

    Thanks

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    **code supplied may be intentionally incomplete**



    What do you suppose you're missing based on that error message?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DELETED (cos Teddy want it that way)
    currently using SS 2008R2

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Oh my ... Being evil today eh Ted?????

    So, are you also a charter member of the evil programmer's club (like me)?

    Also, technically you coulda iterated from 1 thru 12 and then not have to offset by 1 the return value ...
    Last edited by M Owen; 12-29-04 at 12:57.
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Dec 2004
    Posts
    11
    I got IT! Thanks a Million guys. I would have never gotthat one without your help.... works great!

    The Lesson helped also, I have never used modules before I guess I will start now.

    Thanks again

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Good show sir!

    You're well on your way to the frustrating impossibilities of making access do tasks it was not meant to do!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by M Owen
    Oh my ... Being evil today eh Ted?????

    So, are you also a charter member of the evil programmer's club (like me)?
    Not that evil. You wouldn't like me when I'm evil.

    Also, technically you coulda iterated from 1 thru 12 and then not have to offset by 1 the return value ...
    Yeah I spose, didn't think of that.

    I guess I'm in the habit of using dynamic 0-based arrays due to the languages and apps I generally have to deal with. Hard coding 1-12 would have been just fine in this case though, I'll have to change my "personal" version.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Not that evil. You wouldn't like me when I'm evil.



    Yeah I spose, didn't think of that.

    I guess I'm in the habit of using dynamic 0-based arrays due to the languages and apps I generally have to deal with. Hard coding 1-12 would have been just fine in this case though, I'll have to change my "personal" version.
    Hmmm ... I did detect the smell of some C/C++ programming there ...

    As for evil ... Yes I would ... That begs the question of how evil do you think I am???? (Since you said "not THAT evil") ...
    Last edited by M Owen; 12-29-04 at 13:22.
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    delphi 7 and php actually.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    delphi 7 and php actually.
    Ok ... PASCAL and a C derivative web language ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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