using SQL statement to querry dates from access database

[BoxerBrats]

ok having trouble here trying to querry database by dates here is my code any help would be great

Private Sub cmdWorkOrderSearch_Click()
If optWorkOrderScheduled.Value = True Then
WOStatus = "Scheduled"
ElseIf optWorkOrdersIssued.Value = True Then
WOStatus = "Active"
ElseIf optWorkOrdersCompleted.Value = True Then
WOStatus = "Completed"
Else
optWorkOrdersAll.Value = True
End If
Date1 = calWorkOrderSearchDate1.Value
Date2 = calWorkOrderSearchDate2.Value

If (chkWorkOrderSearchText.Value = 1) And (chkWorkOrderSearchType.Value = 1) Then
If cmbWorkOrderField.Text = "Equipment Name" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE EquipmentID = '" + txtWorkOrderSearchText.Text + "' and Status = '" + WOStatus + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE EquipmentID = '" + txtWorkOrderSearchText.Text + "'and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Work Order Number" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WONum = '" + txtWorkOrderSearchText.Text + "' and Status = '" + WOStatus + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WONum = '" + txtWorkOrderSearchText.Text + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "' "
End If
ElseIf cmbWorkOrderField.Text = "Name" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WorkAssignedTo = '" + txtWorkOrderSearchText.Text + "'and Status = '" + WOStatus + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WorkAssignedTo = '" + txtWorkOrderSearchText.Text + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Priority Level" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE PriorityLevel = '" + txtWorkOrderSearchText.Text + "'and Status = '" + WOStatus + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE PriorityLevel = '" + txtWorkOrderSearchText.Text + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Work Order Type" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WOType = '" + txtWorkOrderSearchText.Text + "' and Status = '" + WOStatus + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WOType = '" + txtWorkOrderSearchText.Text + "' and EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
End If
End If

ElseIf chkWorkOrderSearchText.Value = 1 Then
If cmbWorkOrderField.Text = "Equipment Name" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE EquipmentID = '" + txtWorkOrderSearchText.Text + "' and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE EquipmentID = '" + txtWorkOrderSearchText.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Work Order Number" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WONum = '" + txtWorkOrderSearchText.Text + "' and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WONum = '" + txtWorkOrderSearchText.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Name" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WorkAssignedTo = '" + txtWorkOrderSearchText.Text + "'and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WorkAssignedTo = '" + txtWorkOrderSearchText.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Priority Level" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE PriorityLevel = '" + txtWorkOrderSearchText.Text + "'and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE PriorityLevel = '" + txtWorkOrderSearchText.Text + "'"
End If
ElseIf cmbWorkOrderField.Text = "Work Order Type" Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WOType = '" + txtWorkOrderSearchText.Text + "' and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE WOType = '" + txtWorkOrderSearchText.Text + "'"
End If
End If

ElseIf chkWorkOrderSearchType.Value = 1 Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE EquipmentType = '" + cmbWorkOrderSearchType.Text + "' and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE EquipmentType = '" + cmbWorkOrderSearchType.Text + "'"
End If

ElseIf chkWorkOrderSearchDates.Value = 1 Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE StartDate => " + Date1 + " and Status = '" + WOStatus + "' "
If optWorkOrdersAll.Value = True Then
DBSQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, StartDate, CompletionDate From WorkOrders WHERE StartDate => Date1 "
End If

End If

Set rstWorkOrder = New ADODB.Recordset
With rstWorkOrder
.Open DBSQL, conDB, adOpenStatic, adLockBatchOptimistic
Set dbdWorkOrderSearchGrid.DataSource = rstWorkOrder
End With
dbdWorkOrderSearchGrid.Refresh
SSTabWorkOrderScheduled.Tab = (4)

End Sub

Private Sub cmdWorkOrderSort_Click()
Dim strConnection As String
Dim rstdbrs As ADODB.Recordset
Dim path As String

path = App.path & "\Database.mdb"

strConnection = "driver={Microsoft Access Driver (*.mdb)};dbq=" & App.path & "\Data\Database.mdb" & ";uidpwd"

Set conDB = New ADODB.Connection
conDB.Open strConnection


Set rstdbrs = New ADODB.Recordset

Const SQL = "SELECT EquipmentID, WorkAssignedTo, Department, PriorityLevel, WONum, WOType, CompletionDate, Status From WorkOrders WHERE PriorityLevel = 'Low'"

With rstdbrs
.Open SQL, conDB, adOpenStatic, adLockBatchOptimistic
Set dbdWorkOrderCompletedGrid.DataSource = rstdbrs
End With
rstdbrs.Sort = dbdWorkOrderCompletedGrid.Columns(4).DataField & " Desc"
dbdWorkOrderCompletedGrid.Refresh
rstdbrs.Close
Set rstdbrs.ActiveConnection = Nothing
Set rstdbrs = Nothing
End Sub

 

[CCLINT]

How about being more specific?
Posting a bunch or code here is nonesense when it only may have to do with a small portion.

Your question has to do with dates. Then the sql statements which you are having problems with should be the only sql statements posted - you need yourself to narrow down the problem as much as possible before posting it here.

Can tell us which part exactly you are having problems with and what is not happening?
Or do we have to inspect all of it for you?!

Date criteria in a query (here JET Mdb) need the format of:

WHERE SomeDate = #yyyy-MM-dd#
or
WHERE SomeDate = #MM/dd/yyyy#

including the surrounding # character

Try hard coding the dates in your sql statement first to see if it works, and if so, then change it to the date varibale (first formating it of course)