[RESOLVED] SQL concerns...

[Azeccia]

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:

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

[Azeccia]

Ok adjusted my coding too:

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?

[Doogle]

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.

    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

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

[Azeccia]

Ok adjusted my code to your suggestion:

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"?

[Doogle]

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

            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

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

[Azeccia]

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