The Officer Number and Case Number are both text format which solved a
majority of the problems. I created this particular application this
way to avoid any complications when I importing everything over from
Paradox 3.5. The other part of the problem was the text box names,
there were a couple that I had thought I had changed but I didn't... I
changed them.
You were also right when it came to adding OPTION EXPLECIT, that solved
that problem.
Between the Debug.Print and testing each line by adding the apostrophe
I have managed to get the results that I want for the Officer, Officer
Number, and Incident text boxes...know I having fun again.
The only two that are still not showing any results are the date text
box and the Description text box. This is what is showing on the
debug.print for the date and incident searches:
SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber WHERE ORDER BY CaseNumber DESC
SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber WHERE Description="entr 2" ORDER BY
CaseNumber DESC
I think that the desciption problem can be solved with changing it to a
wildcard. When this area was being used in Paradox they would enter
info like ...../......./....../......, etc. The only problem is I am
not sure where the place the wildcard characters for that particular
code.
Another problem that I noticed is that when I do a search and go to
close the form it wants to save the changes; I do not want this to
happen. Would I be right to say that I could place a code under the on
close property for this subform to prevent this from happening.
Here are some features that I would like to add, If you guys could
point me in the right direction:
I would like to add another code under my reset command button to clear
out the previous search.
I am also going to add another text to search for date ranges, if I am
correct I should just be able to add another date string and add the
additional code to my existing one.
Bruce, here a copy of my current code with the updated changes:
Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber "
strSort = " ORDER BY CaseNumber DESC"
If Not IsNull(tbxDateCNS) Then
strWhere = strWhere & " AND CNDate " = Format(tbxDateCNS,
"\#m\/d\/yyyy\#")
End If
If Not IsNull(tbxCaseNumberCNS) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumberCNS &
""" "
End If
If Not IsNull(tbxOfficerCNS) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficerCNS & """ "
End If
If Not IsNull(tbxOfficerNumberCNS) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumberCNS & """ "
End If
If Not IsNull(tbxIncidentTypeCNS) Then
strWhere = strWhere & " AND Incident=""" & tbxIncidentTypeCNS &
""" "
End If
If Not IsNull(tbxDescriptionCNS) Then
strWhere = strWhere & " AND Description=""" & tbxDescriptionCNS
& """ "
End If
Debug.Print strSQL & " WHERE " & Mid(strWhere, 6) & strSort
Me.lstCNSearch.RowSource = strSQL & " WHERE " & Mid(strWhere, 6) &
strSort
End Sub
Overview: Officer, Officer Number, and Case Number are all working
exactly as I want them to. Date and Description are not showing any
results (see debug.print above).
The problem with the date is that the = sign crawled out of
the quotes:
strWhere = strWhere & " AND CNDate = "
Format(tbxDateCNS,"\#m\/d\/yyyy\#")
You can match any part of the description by using:
strWhere = strWhere & " AND Description LIKE ""*" &
tbxDescriptionCNS & "*"" "
If you only want the description to match all the text
between slashes (pay careful attentention to where I placed
the quotes and ampersands):
strWhere = strWhere & " AND ""/"" & Description & ""/"" LIKE
""*/" & tbxDescriptionCNS & "/*"" "
Note that when using Like, users can enter wildcard
characters in tbxDescriptionCNS. This can be a powerful
feature or a source of great confusion depending on your
user's understanding of these things. The confusion may be
compounded if a description might contain any of the
wildcard characters.
Sorry, I just noticed that I made a mistake for the
situation when all the text boxes have nothing entered. To
guard against this situation, change these lines to:
Debug.Print strSQL & (" WHERE " + Mid(strWhere, 6)) &
strSort
Me.lstCNSearch.RowSource = strSQL & (" WHERE " +
Mid(strWhere, 6)) & strSort
*****************************************************************
The problem of the search strings being saved is a serious
issue. These search text boxes ***MUST*** be unbound (i.e.
empty ControlSource). According to what I think you've said
so far, the form's Record Source should also be empty.
*****************************************************************
To clear the search text boxes, add a button to the form and
use code like this in its Click event procedure:
Me.tbxDateCNS = Null
Me.tbxCaseNumberCNS = Null
Me.tbxOfficerCNS = Null
Me.tbxOfficerNumberCNS = Null
Me.tbxIncidentTypeCNS = Null
Me.tbxDescriptionCNS = Null
Adding the ability to search for a range of dates is more
complicated than just adding another If block. You need to
specify how you want to deal with situations where only one
of the two date text boxes is specified, if the start date
is after the end date, etc. I suggest that you get all the
other parts working before tackling any new features.