Query shows no results

[Scott]

I have a form in which the user can pick a start and end date, an Operator
Number and a job code. After clicking the OK button they should get query
results based on their choices. I also tried to set it up so the user can
leave parameters empty like job codes and see all the codes the operator
worked. No matter what parameters are chosen or not the query comes back
with no results everytime. Is there anything wrong with this code?

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_err
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDate As String
Dim strCode As String
Dim strOperNum As String
Set db = CurrentDb
If Not QueryExists("qryStaffData") Then
Set qdf = db.CreateQueryDef("qryStaffData")
Else
Set qdf = db.QueryDefs("qryStaffData")
End If
If IsNull(Me.txtEndDate.Value) Then
If IsNull(Me.txtStartDate.Value) Then
strDate = " Like '*' "
End If
Else
If IsNull(Me.txtStartDate.Value) Then
MsgBox "You have not entered a start date. " _
& "I have entered a start date one day prior " _
& "to the end date.", vbInformation, "No Start Date!"
Me.cboStartDay.Value = Me.cboEndDay.Value - 1
Me.cboStartMonth.Value = Me.cboEndMonth.Value
Me.cboStartYear.Value = Me.cboEndYear
Me.txtStartDate.Value = DateSerial(Me.cboStartYear,
Me.cboStartMonth, Me.cboStartDay)
Else
strDate = " Between '& me.txtStartDate.Value &' And '&
me.txtEndDate.value &'"
End If
End If
If IsNull(Me.cboCode.Value) Then
strCode = " Like '*' "
Else
strCode = "='" & Me.cboCode.Value & "' "
End If
If IsNull(Me.cboOperNum.Value) Then
strOperNum = " Like '*' "
Else
strOperNum = "='" & Me.cboOperNum.Value & "' "
End If
strSQL = "SELECT Data.* " & _
"FROM Data " & _
"WHERE Data.Date" & strDate & _
"AND Data.Code" & strCode & _
"AND Data.OperNum" & strOperNum & _
"ORDER BY Data.Date;"
qdf.SQL = strSQL
DoCmd.Echo False
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffData") =
acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffData"
End If
DoCmd.OpenQuery "qryStaffData"
cmdOK_Click_exit:
DoCmd.Echo True
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
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

 

[Tom Wickerath]

Hi Scott,

Try the modified procedure shown below. You are using two reserved words
(Date and Code). I highly recommend avoiding the use of reserved words for
anything that you assign a name to in Access. You might be interested in
downloading Access MVP Allen Browne's Database Issue Checker Utility, which
you can get here:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I did not attempt to address the functionality with the combo boxes for
defining a start date and end date (ie cboStartYear, cboStartMonth,
cboStartDay, cboEndYear, cboEndMonth and cboEndDay). The end date entered
makes the assumption that the time component is zero (midnight). If your
dates include any time component, then you'll need to use the DateAdd
function to add one day to the end date, and remove the = operator, so that
you are comparing with < End Date + 1.

You did not include your QueryExists function, so I left that part commented
out.


Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_err

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varDate As Variant
Dim varCode As Variant
Dim varOperNum As Variant
Dim strSELECT As String
Dim strWHERE As String
Dim strORDERBY As String
Dim strFullSQL As String

Set db = CurrentDb

strSELECT = "SELECT Data.* FROM DATA "
strWHERE = "WHERE "
strORDERBY = " ORDER BY Data.Date;"


' If Not QueryExists("qryStaffData") Then
' Set qdf = db.CreateQueryDef("qryStaffData")
' Else
Set qdf = db.QueryDefs("qryStaffData")
' End If

varDate = IncludeDateEntered
varCode = IncludeCodeCombobox
varOperNum = IncludeOperNumCombobox

strWHERE = strWHERE & (varDate + " AND ") & varCode & varOperNum

If strWHERE = "WHERE " Then
strWHERE = ""
Else
strWHERE = Left$(strWHERE, Len(strWHERE) - 5)
End If

strFullSQL = strSELECT & strWHERE & strORDERBY

' Debug.Print strFullSQL

qdf.SQL = strFullSQL

If Application.SysCmd(acSysCmdGetObjectState, _
acQuery, "qryStaffData") = acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffData"
End If

DoCmd.OpenQuery "qryStaffData"

cmdOK_Click_exit:
Set qdf = Nothing
db.Close: Set db = Nothing
Exit Sub
cmdOK_Click_err:
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
Resume
End Sub

Private Function IncludeDateEntered() As Variant
On Error GoTo ProcError

' Create the DateEntered Where portion of the SQL statement.
' We are including records where the date entered is greater
' than or equal to the user's date entry

If Not IsNull(Me!txtStartDate) Then
IncludeDateEntered = "([DATE] >= #" & Me!txtStartDate & "#)"
End If

If Not IsNull(Me!txtEndDate) Then
If Len(IncludeDateEntered) > 0 Then ' There was an entry in the
txtStartDate
IncludeDateEntered = IncludeDateEntered & _
" AND ([DATE] <= #" & Me!txtEndDate & "#)"
Else
IncludeDateEntered = "([DATE] <= #" & Me!txtEndDate & "#)"
End If
End If

If IsEmpty(IncludeDateEntered) Then
IncludeDateEntered = Null
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in IncludeDateEntered event procedure..."
Resume ExitProc
End Function

Private Function IncludeCodeCombobox() As Variant

If Not IsNull(Me.cboCode) Then 'Code Field is numeric (integer)
IncludeCodeCombobox = "

 = " & Me.cboCode & " AND "
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in IncludeCodeCombobox event procedure..."
Resume ExitProc
End Function

Private Function IncludeOperNumCombobox() As Variant

If Not IsNull(Me.cboOperNum) Then   'OperNum Field is numeric (integer)
IncludeOperNumCombobox = "[OperNum] = " & Me.cboOperNum & " AND "
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in IncludeOperNumCombobox event procedure..."
Resume ExitProc
End Function




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________