Results 1 to 13 of 13
  1. #1
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    A Combo Box with Current and Next Month Default Values.

    Hi, I have a combo box "CboMonth" at form which is required to always shows as default the exiting month and the next month in format of some thing like 12-2018, 01-2019 etc.



    I used this =DateSerial(Year(Date()),Month(Date()),Day(Date()) ) but its showing only the current month and also not in desired format.


    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You want combobox dropdown to show these 2 values to choose from?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Yeah, and these values shall automatically move on "existing month and always next month"

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Combobox RowSource can be a ValueList or a query. Unfortunately functions and expressions won't work. Cannot do: Format(Date(), "mm-yyyy").

    So options are:

    1. VBA code that builds the values and sets RowSource property

    2. A UNION query that references any table as a source but doesn't really pull any data from it, like:

    SELECT Format(Date(),"mm-yyyy") AS DT FROM Table1 UNION SELECT Format(DateAdd("m",1,Date()),"mm-yyyy") FROM Table1;

    However, if you want them to sort so current month is always first:

    SELECT Format(Date(),"yyyy-mm") AS DT FROM Table1 UNION SELECT Format(DateAdd("m",1,Date()),"yyyy-mm") FROM Table1;

    Or this:
    SELECT Format(Date(),"yyyy-mm") AS YM, Format(Date(),"mm-yyyy") AS MY FROM Table1 UNION SELECT Format(DateAdd("m",1,Date()),"yyyy-mm"), Format(DateAdd("m",1,Date()),"mm-yyyy") FROM Table1;

    And set properties:
    ColumnCount: 2
    ColumnWidths: 0";1"
    BoundColumn: 2
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Whilst I was deriving a solution, June responded with a similar solution to mine involving a UNION query and a dummy table which is required for the query but not used
    As I had already done it, you may as well have it as another solution.
    This creates records in the format Dec 2018 & Jan 2018

    Code:
    SELECT DISTINCT Format(Date(),"mmm yyyy") AS MY FROM Table1;
    UNION SELECT DISTINCT Format(DateAdd("m",1,Date()),"mmm yyyy") FROM Table1;
    If possible use a reference Table1 with only one record. If so OMIT both of the DISTINCT
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Thanks both of you and its working fantastic in either case. Always you guys have great ideas.

    Just a minor issue that after selecting in combo box its showing as first day of month format 01/01/2019 not 01-2019 and then saved in the table at same format.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you bind this combobox to a date/time type field? If you want data saved as 01-2019 then must bind to a text type field. Or you can leave it saved as full date and set Format property to: mm-yyyy

    A full date will be more versatile.

    I did not create another table nor was DISTINCT needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Its done by setting format mm-yyyy.

    Thanks a lot.

  9. #9
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    I set up another criteria and query to get the year by using the same table of months.

    SELECT Format(Date(),"yyyy") AS SchYear FROM TblCDate UNION SELECT Format(DateAdd("m",1,Date()),"yyyy") FROM TblCDate;
    Its giving the proper results as 2018 and 2019 which is stored in table as short text format.

    Now to get the start date and end of years, i used
    SStartDate: DateSerial(Year([SchYear]),1,1)
    Which is resulting 01/01/1905

    whats wrong here?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't need Year function and it would require a full date to extract year from: DateSerial([SchYear], 1, 1)

    Recommend storing year in a number field. But if you already save full date in another field, there is no need to also save just year, extract it when needed.

    Simpler expression for the RowSource:

    SELECT Year(Date()) AS SchYear FROM TblCDate UNION SELECT Year(Date())+1 FROM TblCDate;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Thanks. Its done as you advised

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I may have been wrong about the SchYear value. Does the school year run from like Sep to May? If so, then just extracting the SchYear from a saved date value would not be a simple extraction. Not terribly difficult but not just simply Year(Date()).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    cap.zadi is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    I used the same of yours logic regarding months to pop up in the cbomonth.

    To pop up the current and next year 2018/2019 in the Cbo for selection and then via query to get the starting date and end of related year.

    Its done and working fine.

    Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-12-2015, 05:14 AM
  2. Get first day and month of current year
    By Ruegen in forum Programming
    Replies: 3
    Last Post: 12-01-2014, 06:45 PM
  3. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  4. default month and year
    By beefyalby in forum Forms
    Replies: 3
    Last Post: 12-05-2010, 11:40 PM
  5. Current month in a field as default
    By leandrosarno in forum Access
    Replies: 6
    Last Post: 02-09-2010, 05:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums