Search From List and Key Search

[netchie]

Hi,

I am trying to serach data using filter but it won't show anything when clicking a Search button.



Issue # 1 (Search data for Origin)

To search a data, I am chossing from Origin list which are Deviation, Other, Regulatory, etc. (please see attached). What I am trying to achieve is to show data based from the Origin I chose after clicking Search button. How can I do this?

Issue #2 (Search data with given key word)

How to create a key word search? Let's say I want to search data for my SubjDesc field which I can only type few letters, all SubjDecs with same word that I typed in will show up?

Thanks so much for your help!

[pbaldy]

Nobody but me will be able to help if you don't post your code. You also didn't say what specific problem(s) you were having. At a glance you seem to be conditionally building SQL based on the selected items, which is what I would do. Does this help debug the SQL?

http://www.baldyweb.com/ImmediateWindow.htm

[netchie]

This is what I have after clicking Search button:

Private Sub btnSearch_Click()
Dim strSQL As String

CARSearchResultsForm.Visible = True
ViewCAR.Visible = True

strSQL = "SELECT tCARs.CarNo, tCARs.SubmittedBy, tCARs.OpenDate, tCARs.ClosedDate, tCARs.Source, tCARs.Status,tCARs.StudyNo, tCARs.SourceIdentifier, tCARs.Type, tCARs.TypeIdentifier, tCARs.ReasonForCAR" & _
" FROM tCARs" & _
" WHERE 1=1"

If (Me.CarNo <> "") Then
strSQL = strSQL & " AND tCARs.CARNo = " & Me.CarNo.Value
End If

Select Case Me.Status.Value
Case "Open"
strSQL = strSQL & " AND tCARs.ClosedDate IS NULL"
Case "Closed"
strSQL = strSQL & " AND tCARs.ClosedDate IS NOT NULL"
Case "On-Going"
strSQL = strSQL & " AND tCARs.ClosedDate IS NULL"
Case "Declined"
strSQL = strSQL & " AND tCARs.ValidCAR = 0"
'Case "30-Days Overdue"
' strSQL = strSQL & " AND tCARs.OpenDate < date()-30 AND tCARs.ClosedDate IS NULL"
End Select

If (Me.Source.Value <> "(All)") Then
strSQL = strSQL & " AND tCARs.SubmittedBy = '" & Me.Source.Value & "'"
End If

If (Me.Source.Value <> "(All)") Then
strSQL = strSQL & " AND tCARs.Source = '" & Me.Source.Value & "'"
End If

If (Me.Sourceidentifier.Value <> "(All)") Then
strSQL = strSQL & " AND tCARs.SourceIdentifier = '" & Me.Sourceidentifier.Value & "'"
End If

If (Me.SourceType.Value <> "(All)") Then
strSQL = strSQL & " AND tCARs.Type = '" & Me.SourceType.Value & "'"
End If

If (Me.TypeIdentifier.Value <> "(All)") Then
strSQL = strSQL & " AND tCARs.TypeIdentifier = '" & Me.TypeIdentifier.Value & "'"
End If

If (Me.ProcessOwner.Value <> "(All)") Then
strSQL = strSQL & " AND tCARs.CarNo IN (SELECT CarNo FROM tCARTeam WHERE Type = 'Process Owner' AND Name = '" & Me.ProcessOwner.Value & "')"
End If

If (Me.BeginDate.Value <> "") Then
Select Case Me.DateType.Value
Case "Date Open"
strSQL = strSQL & " AND tCARs.OpenDate >= #" & Me.BeginDate.Value & "#"
Case "Date Close"
strSQL = strSQL & " AND tCARs.ClosedDate >= #" & Me.BeginDate.Value & "#"
End Select
End If

If (Me.EndDate.Value <> "") Then
Select Case Me.DateType.Value
Case "Date Open"
strSQL = strSQL & " AND tCARs.OpenDate <= #" & Me.EndDate.Value & "#"
Case "Date Close"
strSQL = strSQL & " AND tCARs.ClosedDate <= #" & Me.EndDate.Value & "#"
End Select
End If

Me.CARSearchResultsForm.Form.RecordSource = strSQL
Me.CARSearchResul

[pbaldy]

You still haven't mentioned what problem you're having. Does that code not work correctly for origin?

You don't appear to have added code for #2. You'd use a wildcard search:

strSQL = strSQL & " AND FieldName Like '*" & Me.Whatever & "*'"

[netchie]

Problem 1 - when I chose from Origin list and clicked Search button to show the report, it does not show anything. I'm getting blank search result form and that's my problem. It will only show the complete data when I chose "All" from the list.

Problem 2 - where should I put your code anywhere from SQL of Search button--> strSQL = strSQL & " AND FieldName Like '*" & Me.Whatever & "*'"

The field I want to use a wildcard search is for SubjDesc field so is this correct -->

strSQL = strSQL & " AND SubjDesc Like '*" & Me.SubjDesc& "*'" ?

When I use the actual wildscard search I will just type a word and an asterisk with it?

[netchie]

This what I put in VBA of my Search button:

Select Case Me.SubjDesc.Value
strSQL = strSQL & " AND SubjDesc Like '*" & Me.SubjDesc & "*'"
End Select

When I run it, it says Compile error: Statements and labels invalid between Select Case and first Case

[pbaldy]

1) Did you try the debug solution I posted earlier? I did, and it highlights the problem pretty clearly.

2) You haven't used the proper syntax of a Select/Case statement. Frankly it's not appropriate for this anyway. You just want to know if something was entered:

If Len(Me.SubjDesc & vbNullString) > 0 Then

[netchie]

1. Couldn't follow the steps, sorry. I tried so many times. If I'm going to be compared to an Access student, I am in nursery school for Access. Super Dummy The Dummiest ever! I think I'm a hopeless case.

2. What I was trying to get are data that has the same "word" that I will typed in for SubjDesc. If I typed "Apple*", all data with apple will show up.

By the way, this issues are the same as on the database that I sent you.

[pbaldy]

1) It's a really important tool. I suggest you spend some time trying to master it. In the mean time, see attached.

2) I understand what you're trying to do, and the strSQL line looks fine. It just needs to be within an If/Then test like the one I posted. You don't need to add a wildcard to what you type; the code has it built in.

[netchie]

First of all, what happened to the old format of this site? It took me awhile to see all my posts. I really thought I was on a wrong site at first lol. My goodness!!

Anyways, pbaldy... please please please bear with me please??????? About the attachment, what exactly do you want me to do? I am super confused! Is the attachment the answer for my issue #1? What exactly I need to do to fix it? And why is it your attachment with big red circle below it is under "Immediate" and mine is under "Watches". What exactly I need to do to fix Issue #1? You have 3 litle red circles and a big one. So what are they?

[pbaldy]

Did you look at what was circled? On the big one, did it seem odd that two different fields were looking for the same value? Would that value be appropriate for both fields? In the circled code, did it seem odd that in a test of a control named "Source" you added SQL testing a field named "SubmittedBy"?

[netchie]

Ahhh! I'm such a stupid girl *hit head on the wall*. You should have told me that earlier (just kidding) hehehe. Pbaldy! You rock! My issue #1 fixed!

Is there a way you coud help me on Issue #2? Please?????? Or should I post it in a different post?

[pbaldy]

Here, you probably wanted this:

Haven't I already addressed issue 2? Change the test to the one I posted earlier. The SQL looked fine.

[netchie]

Hi pbaldy,

Yup! You both addressed my two issues. Awesomeness!!

You rock pbaldy!!!

[pbaldy]

Happy to help!