ADP Date Issues

[MelissaKT]

Hello. I have just started using an ADP instead of an MDB - so I'm a complete newbie!! I'm attempting to build a SQL string to query a listbox - but I'm having some issues on the date format. The SQL statement that I'm using is:

Private Sub cmdSearch_Click()
Dim strSQL As String

strSQL = "SELECT tblPurchaseOrder.PONumber, tblDepartment.Department, " & _
            "tblPurchaseOrder.InputDate, tblVendor.VendorName, " & _
            "tblPurchaseOrder.InputUser, tblPurchaseOrder.Reference, " & _
            "tblPurchaseOrder.CommissionPOStatus, " & _
            "tblPurchaseOrder.AuditorPOStatus, " & _
            "tblPurchaseOrder.DeleteFlag, tblPaymentType.PaymentType, " & _
            "tblPurchaseOrder.ConfirmationNum " & _
        "FROM tblPurchaseOrder INNER JOIN " & _
            "tblDepartment ON " & _
            "tblPurchaseOrder.DepartmentID = tblDepartment.DepartmentID INNER " & _
            "Join " & _
            "tblVendor ON " & _
            "tblPurchaseOrder.Vendor = tblVendor.VendorID INNER JOIN " & _
            "tblPaymentType ON " & _
            "tblPurchaseOrder.PaymentType = tblPaymentType.PaymentTypeID " & _
            "WHERE "
If Not IsNull(Me.txtConfirmation) And Len(Me.txtConfirmation) > 0 Then
    strSQL = strSQL & "tblPurchaseOrder.ConfirmationNum =" & Me.txtConfirmation & " AND "
End If

If Not IsNull(Me.txtPONo) And Len(Me.txtPONo) > 0 Then
    strSQL = strSQL & "tblPurchaseOrder.PONumber =" & Me.txtPONo & " AND "
End If

If Not IsNull(Me.txtDtSubmitted) And Len(Me.txtDtSubmitted) > 0 Then
    strSQL = strSQL & "tblPurchaseOrder.InputDate = '" & Me.txtDtSubmitted & "' AND "
End If

If Not IsNull(Me.cboAuditorStatus) And Len(Me.cboAuditorStatus) > 0 Then
    strSQL = strSQL & "tblPurchaseOrder.AuditorPOStatus =N'" & Me.cboAuditorStatus.Value & "' AND "
End If

If Not IsNull(Me.cboPurchasingStatus) And Len(Me.cboPurchasingStatus) > 0 Then
    strSQL = strSQL & "tblPurchaseOrder.CommissionPOStatus =N'" & Me.cboPurchasingStatus.Value & "' AND "
End If

If Not IsNull(Me.cboDept) And Len(Me.cboDept) > 0 Then
    strSQL = strSQL & "tblPurchaseOrder.DepartmentID =" & Me.cboDept.Value & " AND "
End If


If IsNull(Me.txtReference) Or Len(Me.txtReference) = 0 Then
    strSQL = strSQL & "tblPurchaseOrder.Reference is null or tblPurchaseOrder.Reference Like '%' "
Else
    strSQL = strSQL & "tblPurchaseorder.Reference Like'" & Me.txtReference & "%'"
End If

strSQL = strSQL & "ORDER BY tblPurchaseOrder.PONumber"

Me.lstPO.RowSource = strSQL
Me.lstPO.Requery

End Sub

Anyway, everything except the date field seems to be working. I've attempted to format it using the Convert(char(10),tblPurchaseOrder.InputDate,101) but either I'm using it incorrectly or it just doesn't like that format either. I would truly appreciate any help!! Thank you in advance.

 

[cmmrfrds]

Please show an example of what you did to format the date and the result of the formatting.

 

[MelissaKT]

Sure, here is one of the things that I tried:

If Not IsNull(Me.txtDtSubmitted) And Len(Me.txtDtSubmitted) > 0 Then
    strSQL = strSQL & "Convert(char(10),tblPurchaseOrder.InputDate,101) = '" & Me.txtDtSubmitted & "' AND "
End If

The result set included an input date of both 05/25/06 and 05/30/06 which is the exact same results that I'm getting using the original code. What Am I doing wrong?

I'm afraid that I'm a little confused on how to handle dates using an ADP.


Oh, got it!! It has to do with the last line

If IsNull(Me.txtReference) Or Len(Me.txtReference) = 0 Then
    strSQL = strSQL & "tblPurchaseOrder.Reference is null or tblPurchaseOrder.Reference Like '%' "
Else
    strSQL = strSQL & "tblPurchaseorder.Reference Like'" & Me.txtReference & "%'"
End If

It includes the dates that I'm asking for as well as all rows that have a reference number. Sorry to be a numskull! I guess it's true what they say about Assuming!!