none
day in month ms access RRS feed

  • Question

  • hi

    i want sql  to count day in month in  ms access  column name (employee_date)

    month  4 = 30 days in month

    month 5 = 31 days days in month


    • Edited by ahmeddc Thursday, May 9, 2019 11:45 PM
    Thursday, May 9, 2019 11:36 PM

Answers

  • Hello,

    Not sure if you are looking to get dates in a specific month, if that is the case the following gets last month dates

    SELECT id, employee_date 
    FROM YourTableName WHERE Year([employee_date]) = Year(Now()) And Month([employee_date]) = Month(Now()) -1

    For this month

    SELECT id, employee_date 
    FROM YourTableName WHERE Year([employee_date]) = Year(Now()) And Month([employee_date]) = Month(Now())
    These are hard coded but you could build dynamically too
    Public Sub Demo()
        Dim year = Now.Year
        Dim month = Now.Month - 1
        Dim selectStatement = "SELECT id, employee_date " &
                              "FROM YourTableName " &
                              $"WHERE Year([employee_date]) = {year}) And Month([employee_date]) = {month}"
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by ahmeddc Saturday, May 11, 2019 2:27 PM
    Friday, May 10, 2019 1:10 AM
    Moderator
  • Hi,

    You can use linq:

            'fill dt
            sda = New OleDbDataAdapter("Select treasur_date From treasur_tb", conn)
            dt = New DataTable()
            sda.Fill(dt)
           'linq
            Dim q1 = From dt1 In dt.AsEnumerable() Where dt1.Field(Of DateTime)("treasur_date").Month = 4 Select dt1
            Dim q2 = From dt1 In dt.AsEnumerable() Where dt1.Field(Of DateTime)("treasur_date").Month = 5 Select dt1
            MsgBox(q1.Count)
            MsgBox(q2.Count)

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Saturday, May 11, 2019 2:27 PM
    Friday, May 10, 2019 1:41 AM

All replies

  • Hello,

    Not sure if you are looking to get dates in a specific month, if that is the case the following gets last month dates

    SELECT id, employee_date 
    FROM YourTableName WHERE Year([employee_date]) = Year(Now()) And Month([employee_date]) = Month(Now()) -1

    For this month

    SELECT id, employee_date 
    FROM YourTableName WHERE Year([employee_date]) = Year(Now()) And Month([employee_date]) = Month(Now())
    These are hard coded but you could build dynamically too
    Public Sub Demo()
        Dim year = Now.Year
        Dim month = Now.Month - 1
        Dim selectStatement = "SELECT id, employee_date " &
                              "FROM YourTableName " &
                              $"WHERE Year([employee_date]) = {year}) And Month([employee_date]) = {month}"
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by ahmeddc Saturday, May 11, 2019 2:27 PM
    Friday, May 10, 2019 1:10 AM
    Moderator
  • Hello,

    Not sure if you are looking to get dates in a specific month, if that is the case the following gets last month dates

    SELECT id, employee_date 
    FROM YourTableName WHERE Year([employee_date]) = Year(Now()) And Month([employee_date]) = Month(Now()) -1

    For this month

    SELECT id, employee_date 
    FROM YourTableName WHERE Year([employee_date]) = Year(Now()) And Month([employee_date]) = Month(Now())
    These are hard coded but you could build dynamically too
    Public Sub Demo()
        Dim year = Now.Year
        Dim month = Now.Month - 1
        Dim selectStatement = "SELECT id, employee_date " &
                              "FROM YourTableName " &
                              $"WHERE Year([employee_date]) = {year}) And Month([employee_date]) = {month}"
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    What is required is the number of days in a certain month 
    The month and year come from the column of ms access database sql

    Friday, May 10, 2019 1:21 AM
  • Hi,

    You can use linq:

            'fill dt
            sda = New OleDbDataAdapter("Select treasur_date From treasur_tb", conn)
            dt = New DataTable()
            sda.Fill(dt)
           'linq
            Dim q1 = From dt1 In dt.AsEnumerable() Where dt1.Field(Of DateTime)("treasur_date").Month = 4 Select dt1
            Dim q2 = From dt1 In dt.AsEnumerable() Where dt1.Field(Of DateTime)("treasur_date").Month = 5 Select dt1
            MsgBox(q1.Count)
            MsgBox(q2.Count)

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Saturday, May 11, 2019 2:27 PM
    Friday, May 10, 2019 1:41 AM
  • i use this way and work fine 

    sql Format(DateSerial(Year('29/05/2019'), Month('01/05/2019') + 1, 0), 'dd') as [Days]


    Problem  now When I put the name of the date column into the database="treasur_date", the error appears

    sql  Format(DateSerial(Year(treasur_date,'yyyy'), Month(treasur_date,'MM') + 1, 0), 'dd') as [Days]

    error


    • Edited by ahmeddc Friday, May 10, 2019 2:00 AM
    Friday, May 10, 2019 1:59 AM