Hi! I came across this code from Martin Greene but need a little assistance on it.
http://www.fontstuff.com/images/acctut17n.gif
http://www.fontstuff.com/access/acctut17a.htm#download
I'm trying to create one query from a form that will help users that don't know exactly what they're looking for. If the user leaves the field blank, the query will bring up all values and not a blank query. When the user selects a specific criteria such as Requestor, the query will return all records with that requestor's name. If the user selects another field such as Data Type, the query will return all records with that requestor's name and data type requested. Basically, it will whittle down the original request of all records. Now what I'm trying to do is add a date range but keep getting an error message. I've searched this site and a bunch more but cannot find an answer. I'm pretty sure its something obvious that I'm not seeing. Below is my code. Can someone please help with the code? Much appreciated!
Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strRequestor As String
Me.QueryRptComm.Visible = True
Dim strCRDCR As String
Dim strFromDate As String
Dim strToDate As String
Dim strTypeofData As String
Dim strStatus As String
Dim strInitiatedBy As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryALL") Then
Set qdf = db.CreateQueryDef("qryALL")
Else
Set qdf = db.QueryDefs("qryALL")
End If
' Get the values from the combo boxes
If IsNull(Me.cboRequestor.Value) Then
strRequestor = " Like '*' "
Else
strRequestor = "='" & Me.cboRequestor.Value & "' "
End If
If IsNull(Me.cboCRDCR.Value) Then
strCRDCR = " Like '*' "
Else
strCRDCR = "='" & Me.cboCRDCR.Value & "' "
End If
If IsNull(Me.cboFromDate.Value) Then
strFromDate = " Like '*' "
Else
strFromDate = "#" & Me.cboFromDate.Value & "# "
End If
If IsNull(Me.cboToDate.Value) Then
strToDate = " Like '*' "
Else
strToDate = "=#" & Me.cboToDate.Value & "# "
End If
If IsNull(Me.cboTypeofData.Value) Then
strTypeofData = " Like '*' "
Else
strTypeofData = "='" & Me.cboTypeofData.Value & "' "
End If
If IsNull(Me.cboStatus.Value) Then
strStatus = " Like '*' "
Else
strStatus = "='" & Me.cboStatus.Value & "' "
End If
If IsNull(Me.cboInitiatedBy.Value) Then
strInitiatedBy = " Like '*' "
Else
strInitiatedBy = "='" & Me.cboInitiatedBy.Value & "' "
End If
Me.cboRequestor.SetFocus
' Build the SQL string
strSQL = "SELECT tblCRDCR_Requests.* " & _
"FROM tblCRDCR_Requests " & _
"WHERE tblCRDCR_Requests.Requestor" & strRequestor & _
"AND tblCRDCR_Requests.DCRCR" & strCRDCR & _
"AND tblCRDCR_Requests.DateRequested" & strFromDate & _
"AND tblCRDCR_Requests.DateRequested" & strToDate & _
"AND tblCRDCR_Requests.TypeofData" & strTypeofData & _
"AND tblCRDCR_Requests.Status" & strStatus & _
"AND tblCRDCR_Requests.InitiatedBy" & strInitiatedBy & _
"ORDER BY tblCRDCR_Requests.RequestID,tblCRDCR_Requests.Requestor;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryALL") = acObjStateOpen Then
DoCmd.Close acQuery, "qryALL"
End If
' Open the query
DoCmd.OpenQuery "qryALL"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub
http://www.fontstuff.com/images/acctut17n.gif
http://www.fontstuff.com/access/acctut17a.htm#download
I'm trying to create one query from a form that will help users that don't know exactly what they're looking for. If the user leaves the field blank, the query will bring up all values and not a blank query. When the user selects a specific criteria such as Requestor, the query will return all records with that requestor's name. If the user selects another field such as Data Type, the query will return all records with that requestor's name and data type requested. Basically, it will whittle down the original request of all records. Now what I'm trying to do is add a date range but keep getting an error message. I've searched this site and a bunch more but cannot find an answer. I'm pretty sure its something obvious that I'm not seeing. Below is my code. Can someone please help with the code? Much appreciated!
Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strRequestor As String
Me.QueryRptComm.Visible = True
Dim strCRDCR As String
Dim strFromDate As String
Dim strToDate As String
Dim strTypeofData As String
Dim strStatus As String
Dim strInitiatedBy As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryALL") Then
Set qdf = db.CreateQueryDef("qryALL")
Else
Set qdf = db.QueryDefs("qryALL")
End If
' Get the values from the combo boxes
If IsNull(Me.cboRequestor.Value) Then
strRequestor = " Like '*' "
Else
strRequestor = "='" & Me.cboRequestor.Value & "' "
End If
If IsNull(Me.cboCRDCR.Value) Then
strCRDCR = " Like '*' "
Else
strCRDCR = "='" & Me.cboCRDCR.Value & "' "
End If
If IsNull(Me.cboFromDate.Value) Then
strFromDate = " Like '*' "
Else
strFromDate = "#" & Me.cboFromDate.Value & "# "
End If
If IsNull(Me.cboToDate.Value) Then
strToDate = " Like '*' "
Else
strToDate = "=#" & Me.cboToDate.Value & "# "
End If
If IsNull(Me.cboTypeofData.Value) Then
strTypeofData = " Like '*' "
Else
strTypeofData = "='" & Me.cboTypeofData.Value & "' "
End If
If IsNull(Me.cboStatus.Value) Then
strStatus = " Like '*' "
Else
strStatus = "='" & Me.cboStatus.Value & "' "
End If
If IsNull(Me.cboInitiatedBy.Value) Then
strInitiatedBy = " Like '*' "
Else
strInitiatedBy = "='" & Me.cboInitiatedBy.Value & "' "
End If
Me.cboRequestor.SetFocus
' Build the SQL string
strSQL = "SELECT tblCRDCR_Requests.* " & _
"FROM tblCRDCR_Requests " & _
"WHERE tblCRDCR_Requests.Requestor" & strRequestor & _
"AND tblCRDCR_Requests.DCRCR" & strCRDCR & _
"AND tblCRDCR_Requests.DateRequested" & strFromDate & _
"AND tblCRDCR_Requests.DateRequested" & strToDate & _
"AND tblCRDCR_Requests.TypeofData" & strTypeofData & _
"AND tblCRDCR_Requests.Status" & strStatus & _
"AND tblCRDCR_Requests.InitiatedBy" & strInitiatedBy & _
"ORDER BY tblCRDCR_Requests.RequestID,tblCRDCR_Requests.Requestor;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryALL") = acObjStateOpen Then
DoCmd.Close acQuery, "qryALL"
End If
' Open the query
DoCmd.OpenQuery "qryALL"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub