Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: to return month value

    i have a field name month which is of datatype ( text) and contains table values like jan,feb,mar,apr,may, ......

    is there any way to filter in query to see list till current month

    i tried
    Expr1: MonthName([month],1)

    it gives error

    and what should be the datatype if the table should show jan,feb... datas in table and used to show data till this month

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by eddi View Post
    ...to see list till current month...
    Sorry, but I no idea what you mean by this! The Expression

    Expr1: MonthName([month],1)

    is going to do nothing, as you've found out, because your arguments are incorrect. The correct syntax is

    MonthName([Month as Integer],[True, False])

    where [Month as Integer] is an Integer, 1-12, representing the month whose name you want to return. The second argument is either True, meaning you want the month abbreviated, or False, meaning you want the entire name returned. If left blank, the Default is False and the entore name will be returned.

    This:

    MonthName(month(Date),True)

    will return the abbreviation of the current month, if that helps you. This sapparently what you have in your 'month' Table.

    As to the Datatype for the Field holding your month abbreviations, it really has to be Text, since a month, alone, does not a Date make.

    Linq ;0)>
    Last edited by Missinglinq; 10-09-11 at 00:25.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want jan, feb...... then look at the format function.
    IIRC its format(mydatecolumn,"mmm")
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2011
    Posts
    66

    thanks for the help but did not get

    iam using in a query which has field [month] containing jan,feb,mar.... till dec
    for this [month] field the data type in table is text


    now i want [month] field values in a new field as 1,2,3.... till 12 in the query or

    because when i open the query name " till this month " i want it to show the month names till current month that is till "oct" and should not show nov & dec
    for this i tried to give this in criteria but iam not getting
    <=Now()

    what should i do to see the months till this current month

    can you help pls

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Month(datefeildname) will return the month number Of the field

    I think you need to think about how you are storing your data less is beater

    Storing a date value ie idate

    Them you can month(idate) = month number
    Or year(idate) = year
    Or day(idate) = day

    And then you can start using the date add or date dif
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Sep 2011
    Posts
    66
    Thanks for the reply myle it works with data type " date and time " but mine is text field

  7. #7
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    If you are going to continue with a text field that stores month abbreviations, I may have one solution for you. A small Access 2003 database is attached. The result is that it calculates the numbers 1 through 12 according to the month abbreviation value.

    Here is the code from the query:
    Code:
    Month_No: Int(InStr("JanFebMarAprMayJunJulAugSepOctNovDec",[MonthAbbrev])/3)+1
    
    
    Month_Number: IIf(InStr("JanFebMarAprMayJunJulAugSepOctNovDec",[MonthAbbrev])>0,[Month_No],0)
    The Month_Number field gives you a 0 (zero) if the text is not one of the 12 months. There would be a problem with this solution if your text field had incorrect values, such as "anF".
    Jerry
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Jerrydal that thinking out side the square
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This query code will do error checking so that "anF" or any other combination of wrong text that may exist in the string of month abbreviations will not give you unexpected results.

    Code:
    Month_Position: InStr("JanFebMarAprMayJunJulAugSepOctNovDec",[MonthAbbrev])+2
    
    Month_Number: IIf(([Month_Position] Mod 3)=0,([Month_Position]/3),"Bad Data")

  10. #10
    Join Date
    Mar 2011
    Location
    USA
    Posts
    2
    Thanks for the solution, JerryDal. I have struggled with this type of problem for quite a while. I am so happy that I found the solution. I am going to try an implement it in my dbase.

  11. #11
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The last query code will produce a text field because of the "bad data" part of the statement. For strictly numeric results replace "bad data" with 0 (zero) to get a zero for missing month text, or type in its place the letters: null (with no quotes) to get a null value for the month number.

  12. #12
    Join Date
    Oct 2011
    Posts
    16
    JerryDal: Brilliant!

Posting Permissions

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