Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2013
    Posts
    10

    Unanswered: Date format monthly only !

    Hi Folks
    I have set up a table, and added a field where the end user should insert a date to populate the table with diffrent activities in this format 01/01/2013

    What i want to do, is not to insert date like that : 01/01/2013, but just january 2013, because all my activities are monthly reports ! so i don' really need a full date, or months numbers !

    I've tried to set up a combo box with months ,names and another with years, but the problem is when setting up a query to extract monthly reports things get messy !

    Is there a way to fix that ? thank you in advence

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you want to strote Date/Time data, it's always better to use a column of Date/Time data type. You can easily solve your problem by using the Format() function, such as:
    Code:
    SELECT... FROM... WHERE Format([MyDateTimeColumn], "mmyyyy") = "052013"
    You can also use the Month() and Year() functions, as well as the DatePart() function.
    Have a nice day!

  3. #3
    Join Date
    May 2013
    Posts
    10
    Hi, thanks for the answer

    As am a bit noob in access, can you please do me a favour and explain me more on how to spell that thing and where ?

    My date field in the table is called Month, and set to Date/time option, and in default value i just put this =Date()

    Thank you

  4. #4
    Join Date
    May 2013
    Posts
    10
    Help me please, am struggling with this

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    "Month" is a reserved word in Access, so you should really call your column something else (ReportMonth, for example).

    You can set the Format of the column to "mmmm yyyy". This will just show the month and year. You will need to write some form of validation on the data entry form to make sure that the values entered are set back to the first of the month. You can do this with the DateAdd, DateDiff, Month and Year functions within VBA.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sinndho View Post
    When you want to strote Date/Time data, it's always better to use a column of Date/Time data type. You can easily solve your problem by using the Format() function, such as:
    Code:
    SELECT... FROM... WHERE Format([MyDateTimeColumn], "mmyyyy") = "052013"
    You can also use the Month() and Year() functions, as well as the DatePart() function.
    actually I'd contend if you want to store only the month and year then you'd be better off storing the date as YYYYMM, that way round it will sort. however a far smarter solution is to store date information as dates but convert it to the month/year as required using the MONTH(mydatevalue) and YEAR(mydatevalue)

    so the choice is either you are storing dated events, in which case use a date column or storng something else and then use a suitable column

    how data is stored inside the DB is different to the way its presented in forms or reports. a date inside an Access db is stored as a decimal number not 01/01/2013.

    you can always abstract the data on the way out of the DB, but you cannot add the day back into abstracted data.
    eg
    select my, column, list from MyTable
    where month(mydatecolumn) in (3,4,5) and Year(mydatecolumn) = 2013
    ORDER BY month(mydatecolumn), Year(mydatecolumn)

    one reason to store date information as dates is that you can use the pre determined date and time functions to analyse your data. ferinstance what happens if you need to, say compare May 2012 with May 2013.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2013
    Posts
    10
    Good Afternoon Fellas
    Thank you so much for the answers, the fact is that i've searched the net for that thing but apprently no one seem to know the exact way to do this thing and there are no tutorials about it either
    as am a noob in access, i've tried to put something in VBA, but i don't know all the formulas so am still struggling with it !
    And sadely according to what you said above, i think that i'll keep writing 01/01/2013 in everything i insert in my forms to show that this is January !

    Some dude told me that there is no fix to this the other day and i haven't blieved it.....anyway thank you for the answers and have a good day

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store your events on the date they actually occurred
    then when abstracting the data from the db put it into the format you want to display. thats 'the' way to do it, not kludge your data

    you can conjure up the mointh and year usign format or the year & month functions

    doing it your way may work right now, but it forces your application down a specific pathway, a pathway that could easily become limiting if the app changes its requirememnts.

    OR if you must store events with a year and month then store using an integer format such as yyyymm eg june 2013 is 201306
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2013
    Posts
    10
    you can conjure up the mointh and year usign format or the year & month functions
    How to spell it please ? the field in the table is called Month, and it's on Date/time option, and now am writing 01/01/2013 to insert data that belongs to January .....They are monthly reports am forced to do so !

    Also the aim of this is to ease the data inserting for my colleagues but i don't think they will appreciate the 01/01/2013 every time they need to insert data .....this is why am asking if the thing is possible or not !

    actually i put this in the default value : =Date()

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok first off do not use reserved words for your table and or column names. it can cause havoc with subsequent form and report design

    use the date of the event

    you can easily get the month of the event date by using the month function, indeed there is a whole library of date time functions which are only available if you store date time values in the date datatype

    month(mydatecolumn) gives you the month of the date value
    year(mydatecolumn) gives you the year of the date value
    format (mydatecolumn, "yyyymm") gives you the year and month of the mydatecolumn
    you will be surprised how quickly data entry specialist can enter data. I doubt you need worry about 'making it easier' for your data capture.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2013
    Posts
    10
    Ok, Thank you for the explanations and the usefull links

    One last question : Do that means that i'll have to store dates separatly, mean one field for months and another one for years ? ......yes i know sounds silly as a question but am a newbie lolll

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To paraphrase Johnny Cash in his famous song, name your column Bill or George, ANYTHING BUT MONTH. Creating a column with a name that is a reserved word like SELECT or UPDATE or even MONTH is a really, really bad idea. It confuses the machine, most people, and just generally makes life difficult for no good reason!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Tinco View Post
    Ok, Thank you for the explanations and the usefull links

    One last question : Do that means that i'll have to store dates separatly, mean one field for months and another one for years ? ......yes i know sounds silly as a question but am a newbie lolll
    NO
    store the actual date of the event eg 4th June 2103 NOT some cockeyed abstraction such as 201305 or as separate years and months.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by healdem View Post
    4th June 2103
    Wow, that is some SERIOUS pre-planning!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    May 2013
    Posts
    10
    Ok, Gotcha !!

    So i'll continue storing things like 01/01/2013, and will use this in a query :
    Code:
    Expr1: Format([Dte];"mmmm")
    Where Dte is actually my date field, and yup this shows the month name in the query results not the numerical date !!

    My Original question in this post was how to make access Show the Month name instead of a full date when storing data via Forms, i was hoping that there's a way to fix that

    Thank you Guys for the Help, it's awesome of you

Posting Permissions

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