aligahk06-davegri-v01.zip
The attached DB contains the below code:
Code:
Option Compare Database
Option Explicit
Private Sub cmdShowAll_Click()
Me.RecordSource = "Select * from woPR;"
End Sub
Private Sub Search_Click()
'i want to click search command button after input from date and to date field value then it return all corresponding records.
Dim strCriteria As String, task As String
Me.Refresh
If IsNull(Me.FromDate) Or IsNull(Me.ToDate) Then
MsgBox " Enter the Date Range", vbInformation, "Date Range required "
Me.FromDate.SetFocus
Exit Sub
End If
'if costCode is required in the search, uncomment the next 5 lines
'' If IsNull(Me.costCode) Then
'' MsgBox "costCode is required", vbInformation, "costCode required"
'' Me.costCode.SetFocus
'' Exit Sub
'' End If
If Not IsNull(Me.costCode) Then
strCriteria = "CCode ='" & Me.costCode & "' AND "
End If
strCriteria = strCriteria & "[dDate]>= #" & Me.FromDate & "# And [dDate] <= #" & Me.ToDate & "#"
task = " select * from woPR where " & strCriteria & " order By [dDate]"
'Debug.Print task
Me.RecordSource = task
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "no match"
Me.RecordSource = "select * from woPR"
End If
End Sub
You can make the costCode required or not.
If you have a list of costCodes, you can change the textbox to a combobox to select a valid costCode.