[RESOLVED] SQL concerns...

I am attempting to check my EmDpsst field in my access2003 database. I am attempting to check all the dates in the field, EmDpsst and see if they are less than 90 days. If so I plan to report them to a txtDpsst.

So far I have the following code:

Code:

---

Private Sub CallCheck2()
    Dim strSQL As String
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    cn.ConnectionString = GetConnString
   
    Dim days As Long
    Dim Expired As String
   
    strSQL = "SELECT EmLast, EmDpsst FROM tblEmployee Group BY EmDpsst"

    cn.Open
    rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
   
    days = DateDiff("d", Now, EmDpsst)
   
    If rs.RecordCount > 0 Then
        Do Until rs.EOF
            If rs.Fields(EmDpsst) = days < 90 Then
                txtDpsst.Text = "Name: " & rs.Fields(EmLast) & " Expiring: " & rs.Fields(EmDpsst) & vbCrLf & txtDpsst.Text
            End If
            rs.MoveNext
        Loop
    End If
    rs.Close
End Sub

---

Only my problem is that it is not working lol. Any ideas how I am able to properly adjust my coding?


Much appreciated,
Azeccia

Ok adjusted my coding too:

Code:

---

Private Sub CallCheck2()
    Dim strSQL As String
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    cn.ConnectionString = GetConnString
   
    Dim days As Long
    Dim Expired As String
   
    strSQL = "SELECT EmDpsst,EmLast FROM tblEmployee"

    cn.Open
    rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
   
    rs.Fields("EmDpsst") = dpsstcheck
    'Send a memo to the frmMain's txtDpsst about the employee's expiring DPSST card.
    dpsstcheck = DateDiff("d", Now, EmDpsst)
   
    If rs.RecordCount > 0 Then
        Do Until rs.EOF
            If dpsstcheck < 90 Then
                txtDpsst.Text = "Name: " & rs.Fields("EmLast") & " Expiring: " & rs.Fields("EmDpsst") & vbCrLf & txtDpsst.Text
            End If
            rs.MoveNext
        Loop
    End If
    rs.Close
End Sub

---

I now have it reporting all entries in the database since I can not find a way to force my dpsstcheck = DateDiff("d", Now, EmDpsst).

How can I make this work?

Firstly, I suggest you put Option Explicit in the Declarations Section of your Form. You'll then see that EmDpsst is undefined. In fact the statement is in the wrong place.

Code:

---

    If rs.RecordCount > 0 Then
        Do Until rs.EOF
            days = DateDiff("d", Now, rs.Fileds("EmDpsst")
            If  days < 90 Then
                txtDpsst.Text = "Name: " & rs.Fields("EmLast") & " Expiring: " & rs.Fields("EmDpsst") & vbCrLf & txtDpsst.Text
            End If
            rs.MoveNext
        Loop
    End If

---

That assumes that EmDpsst is a Date type. If it's not then use CDate

Code:

---

days = DateDiff("d", Now, CDate(rs.Fileds("EmDpsst"))

---

Ok adjusted my code to your suggestion:

Code:

---

Option Explicit

Private Sub CallCheck2()
    Dim strSQL As String
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    cn.ConnectionString = GetConnString
   
    Dim dpsstcheck As Long
   
    strSQL = "SELECT EmDpsst,EmLast FROM tblEmployee"

    cn.Open
    rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
   
    rs.Fields("EmDpsst") = dpsstcheck
    'Send a memo to the frmMain's txtDpsst about the employee's expiring DPSST card.
   
    If rs.RecordCount > 0 Then
        Do Until rs.EOF
            dpsstcheck = DateDiff("d", Now, CDate(rs.Fields("EmDpsst")))
            If dpsstcheck < 90 Then
                txtDpsst.Text = "Name: " & rs.Fields("EmLast") & " Expiring: " & rs.Fields("EmDpsst") & vbCrLf & txtDpsst.Text
            End If
            rs.MoveNext
        Loop
    End If
    rs.Close
End Sub

---

It worked pretty well except it seems to detect Null entires in the Emdpsst field as less than 90 days aswell.

I was trying to force the user to enter all dates in the database via dtpickers so they can't lose the date format however when they dont want to enter a date, the field is automatically set to value of "0".

I don't want to force the user to enter in a date for this field if they don't want to since sometimes they wont have the accurate info everytime the app is used.

Is there a way i can adjust your code to ignore values of "0"?

Well, in most Database Systems it's possible to give a field a default value if none is specified. I suspect it would be better if you checked the user input and if the Date is 0 then set a default (perhaps Today's date, ie Now) or tell the user that the date can't be blank and force them to enter one

Edit: Or you could check the days difference and if it's zero, just ignore the record

Code:

---

            If dpsstcheck < 90 And dpsstcheck <> 0 Then

---

Edit2: On reflection that's not a very good idea. Perhaps you should check EmDpsst for zero rather than dpsstcheck

Code:

---

            If dpsstcheck < 90 and rs.Fileds("EmDpsst") <> 0 Then

---

If dpsstcheck < 90 And rs.Fields("EmDpsst") <> 0 Then

Worked like a charm. I may consider setting all date fields to date() by default but for now you helped me big time.

Thank you again I appreciated it,
Azeccia