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

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 & "' "
            

Answer 1

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

Answer 2

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

Answer 3

 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.

Answer 4

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