none
SQL count function if difference in two date in two column less than 7 RRS feed

  • Question

  •  

     i am counting value  in SQL database with multiple condition it work. but i want one more condation.

      in which two column contain date. if date differance is less than 7 day then it count.

    below formula i am using i give zero value

    fldPosttestDate and DateOfPretest contain date

    cmdSQL.CommandText = "Select  COUNT (TYPEOFCLIENT) FROM PREPOSTTESTREPORT WHERE TYPEOFCLIENT = 1 AND SEX = 1 AND (fldReferredBy BETWEEN 3 and 10) AND fldPosttestCounselingdone = 2 AND (fldPosttestDate - DateOfPretest) <7 AND MONTH(fldPosttestDate)= '" & ComboBox1.Text & "' AND YEAR(fldPosttestDate)= '" & ComboBox2.Text & "' "
                

    Wednesday, September 16, 2015 6:04 AM

Answers

  • 1.  Can either of the dates be NULL?  If so call the NZ function to ensure a value greater than 6 is returned, assuming you would not want such rows taken into account, or to ensure a value less than 7 is returned if you do want such rows taken into account.  The former is not strictly necessary, but the latter would be.

    2.  The MONTH and YEAR functions return an integer expression, not a string expression, so, assuming the months are listed in ComboBox1 as numbers from 1 to 12, you do not need to wrap the values of the combo boxes in literal quotes characters.

    If the month values in the combo box are the month names, set the control's RowSource property to the value list:

    1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

    and set its BoundColumn property to 1 and its ColumnWidths property to 0.

    3.  You do not need to reference the control's Text property.

    Try this:

    cmdSQL.CommandText = _
        "SELECT  COUNT (*) " & _
        "FROM PREPOSTTESTREPORT " & _
        "WHERE TYPEOFCLIENT = 1 " & _
        "AND SEX = 1 " & _
        "AND fldReferredBy BETWEEN 3 and 10 " & _
        "AND fldPosttestCounselingdone = 2 " & _
        "AND (NZ(fldPosttestDate,#3000-01-01#) - NZ(DateOfPretest,#1900-01-01#)) < 7 " & _
        "AND MONTH(fldPosttestDate) = " & ComboBox1 & _
        " AND YEAR(fldPosttestDate) = " & ComboBox2

    I would also recommend that you do not accept the meaningless default names like ComboBox1 which Access gives to controls, but rename them as something meaningful, e.g. cboMonth, immediately after adding then to a form or report.  If you change the name after writing event procedures for the control, however, the link between the control and the event procedure will be broken, so you'll need to delete the procedure from the form or report's module and recreate it.

    Ken Sheridan, Stafford, England


    Wednesday, September 16, 2015 9:34 AM

All replies

  • Hi,

    You can try this.

    cmdSQL.CommandText = "Select  COUNT (TYPEOFCLIENT) FROM PREPOSTTESTREPORT WHERE TYPEOFCLIENT = 1 AND SEX = 1 AND (fldReferredBy BETWEEN 3 and 10) AND fldPosttestCounselingdone = 2 AND (DateDiff ('d', fldPosttestDate, DateOfPretest) <7) AND MONTH(fldPosttestDate)= '" & ComboBox1.Text & "' AND YEAR(fldPosttestDate)= '" & ComboBox2.Text & "' "

    Thanks.


    Sethu


    Wednesday, September 16, 2015 6:13 AM
  • 1.  Can either of the dates be NULL?  If so call the NZ function to ensure a value greater than 6 is returned, assuming you would not want such rows taken into account, or to ensure a value less than 7 is returned if you do want such rows taken into account.  The former is not strictly necessary, but the latter would be.

    2.  The MONTH and YEAR functions return an integer expression, not a string expression, so, assuming the months are listed in ComboBox1 as numbers from 1 to 12, you do not need to wrap the values of the combo boxes in literal quotes characters.

    If the month values in the combo box are the month names, set the control's RowSource property to the value list:

    1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

    and set its BoundColumn property to 1 and its ColumnWidths property to 0.

    3.  You do not need to reference the control's Text property.

    Try this:

    cmdSQL.CommandText = _
        "SELECT  COUNT (*) " & _
        "FROM PREPOSTTESTREPORT " & _
        "WHERE TYPEOFCLIENT = 1 " & _
        "AND SEX = 1 " & _
        "AND fldReferredBy BETWEEN 3 and 10 " & _
        "AND fldPosttestCounselingdone = 2 " & _
        "AND (NZ(fldPosttestDate,#3000-01-01#) - NZ(DateOfPretest,#1900-01-01#)) < 7 " & _
        "AND MONTH(fldPosttestDate) = " & ComboBox1 & _
        " AND YEAR(fldPosttestDate) = " & ComboBox2

    I would also recommend that you do not accept the meaningless default names like ComboBox1 which Access gives to controls, but rename them as something meaningful, e.g. cboMonth, immediately after adding then to a form or report.  If you change the name after writing event procedures for the control, however, the link between the control and the event procedure will be broken, so you'll need to delete the procedure from the form or report's module and recreate it.

    Ken Sheridan, Stafford, England


    Wednesday, September 16, 2015 9:34 AM
  •  If you change the name after writing event procedures for the control, however, the link between the control and the event procedure will be broken, so you'll need to delete the procedure from the form or report's module and recreate it.

    Hi Ken,

    You can just rename the procedure(s) in the form or report's module form the old control name to the new control name.

    Imb.

    Wednesday, September 16, 2015 9:46 AM
  • You can just rename the procedure(s) in the form or report's module form the old control name to the new control name.
    True, but being the world's worst typist, I can never trust myself to get the name right, so I tend to cut and paste the code back into a new procedure.

    Ken Sheridan, Stafford, England

    Wednesday, September 16, 2015 10:23 AM