Results 1 to 13 of 13

Thread: Count Months

  1. #1
    Join Date
    Mar 2004
    Location
    Seattle
    Posts
    9

    Unanswered: Count Months

    I need to do something that should be relatively simple. I want the user to enter dates into 2 fields: OpenDate and CloseDate. Then I want to Count the months that those dates occupy.

    Example: OpenDate = 6/20/04
    CloseDate = 8/20/04
    Count Months = 3 (June, July and August)

    Anyone know how I can do this????

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    DateDiff("m",OpenDate,CloseDate)+1

  3. #3
    Join Date
    Mar 2004
    Location
    Seattle
    Posts
    9
    Thanks for your quick response! It worked like a charm.

  4. #4
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by DCKunkle
    DateDiff("m",OpenDate,CloseDate)+1
    Why is it when I try this in Access SQL view I get an error message saying

    "Data type mismatch in Criteria expression"

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It's probably because the OpenDate and CloseDate aren't actually date fields. If you check the table they are coming from, are the fields strings or dates? If they are strings then try this:

    DateDiff("m",CDate(OpenDate),CDate(CloseDate))+1

    CDate will convert a string to a date, assuming the string looks like a date (mm/dd/yy).

  6. #6
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by DCKunkle
    It's probably because the OpenDate and CloseDate aren't actually date fields. If you check the table they are coming from, are the fields strings or dates? If they are strings then try this:

    DateDiff("m",CDate(OpenDate),CDate(CloseDate))+1

    CDate will convert a string to a date, assuming the string looks like a date (mm/dd/yy).
    DC,

    I am new to Access so please bare with me.

    Firstly, what does a string mean?

    Secondly when I type the above or anything similar in SQL view I get an error message saying

    "Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE.2"

    When I get this message, does that mean that I have to add SELECT or one of the above?

    Lastly, what does 'CDATE' mean in the code that you have written?

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by hali99
    DC,

    I am new to Access so please bare with me.

    Firstly, what does a string mean?

    Secondly when I type the above or anything similar in SQL view I get an error message saying

    "Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE.2"

    When I get this message, does that mean that I have to add SELECT or one of the above?

    Lastly, what does 'CDATE' mean in the code that you have written?

    I'll answer 2 of these

    1) A string is the database term for plain text. It can also be thought of as alphanumeric if that helps. It is used for text/memo fields.

    2) The code DateDiff("m",CDate(OpenDate),CDate(CloseDate))+1
    will do nothing by itself. A SQL statement must have a select and from statement at the least. Try:

    Code:
    Select DateDiff("m",CDate(OpenDate),CDate(CloseDate))+1
    from table_name
    something like that. The SQL view expects a SELECT FROM statement or it will not work. Hope that helps on some of your ?'s


    3) Read above a couple of posts - Cdate is used if the field is text instead of date - If you are entering dates, you should make it a date field though.

    Hope that helps.

  8. #8
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by ss659
    I'll answer 2 of these

    1) A string is the database term for plain text. It can also be thought of as alphanumeric if that helps. It is used for text/memo fields.

    2) The code DateDiff("m",CDate(OpenDate),CDate(CloseDate))+1
    will do nothing by itself. A SQL statement must have a select and from statement at the least. Try:

    Code:
    Select DateDiff("m",CDate(OpenDate),CDate(CloseDate))+1
    from table_name
    something like that. The SQL view expects a SELECT FROM statement or it will not work. Hope that helps on some of your ?'s


    3) Read above a couple of posts - Cdate is used if the field is text instead of date - If you are entering dates, you should make it a date field though.

    Hope that helps.

    Got it. Thanks ss659.

  9. #9
    Join Date
    Mar 2004
    Posts
    52
    ss,

    Could you please tell me, if I want to know which members membership will be expired within the next 20days, how would I do that?

    I have MemberExpiryDate in my Member table.

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by hali99
    ss,

    Could you please tell me, if I want to know which members membership will be expired within the next 20days, how would I do that?

    I have MemberExpiryDate in my Member table.

    Off the top of my head (havent tested so you may have to play around with it)

    Code:
    SELECT member_name, member_id
    from your_table
    where join_date <= now() + 20
    Im sure someone can think of something better, but I'll explain this and maybe you will get an idea.

    So today is March 24

    20 days from now is April 13th. So you want the name and ID of everyone who's join date is less than or equal to April 13th.

    Member ID -------- Join Date
    1 -------------------- April 14th
    2--------------------- March 25th
    3---------------------April 13th
    4----------------------July 25th

    Members 2 and 3 would be pulled back b/c their membership expires within those 20 days.

    Hope that gives you a good idea!

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    "SELECT * FROM YourTable WHERE (Mydate BETWEEN #" & Date & "# AND DateAdd('d',20,[Mydate]));"

  12. #12
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by ss659
    Off the top of my head (havent tested so you may have to play around with it)

    Code:
    SELECT member_name, member_id
    from your_table
    where join_date <= now() + 20
    Hope that gives you a good idea!
    You know what? I can't thank you enough! It works.

  13. #13
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by M Owen
    "SELECT * FROM YourTable WHERE (Mydate BETWEEN #" & Date & "# AND DateAdd('d',20,[Mydate]));"
    M Owen, thanks for your efforts to.

Posting Permissions

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