Multi Search Criteria Form/Query

[NickWren]

So I have a query that is presented through a subform called qrySampleStatus, I have on the main form 2 fields for dates (start and end dates) for the sample intake. There are two types of tests saved on this query which have different start date fields.
Field 1: [ReceivedSampleDate]


Criteria: Between Nz([Forms]![frmSampleStatus]![StartDate],#1/1/1900#) And Nz([Forms]![frmSampleStatus]![EndDate],#1/1/2200#)

Field 2: [CGACollectionDate]
Criteria: Between Nz([Forms]![frmSampleStatus]![StartDate],#1/1/1900#) And Nz([Forms]![frmSampleStatus]![EndDate],#1/1/2200#)

Then the the form's query has 4 other search fields;
Field 3: [PatientID]
Criteria: Like '*' & SearchPatientID & '*'

Field 4: [PatientName]
Criteria: Like '*' & SearchPatientName & '*'

Field 5: [PhysicianName]
Criteria: Like '*' & SearchPhysicianName & '*'

Field 6: [SampleID]
Criteria: Like '*' & SearchSampleID & '*'

So the dates work fine (They are in the first slots of the query), but if I put the rest of the criteria in the first slot it doesnt work nor does the other slots really; do I have to many search parameters if so how can I put this in VBA so that it can actually go through or is there another solution.

[ranman256]

I doesnt do any good to put NZ on the date range. If the dates are null , you wont get any data.
That date range MUST be enforced before it runs. I must have data in the fields.

If you use these various criteria, you must have logic to do so....

sub btnFind_Click()
dim sWhere as string

 select case true
     case not IsNull(StartDate) then
         sWhere = " between " & StartDate &  " and " & EndDate

    case not IsNull(SearchPatientName)
         sWhere = " Like '*' & SearchPatientName & '*'"

end select
docmd.openquery "qsFind",,,sWHere
end sub

[NickWren]

This is what I have but it keeps coming up with an error and I cant figure out why.

Private Sub Command220_Click()

Dim sWhere As String

 Select Case True
    Case Not IsNull(SearchPatientID)
         sWhere = "Like '*' & SearchPatientID & '*'"

    Case Not IsNull(SearchPatientName)
         sWhere = "Like '*' & SearchPatientName & '*'"
         
    Case Not IsNull(SearchPhyisicanName)
         sWhere = "Like '*' & SearchPhysicianName & '*'"
         
    Case Not IsNull(SearchSampleID)
         sWhere = "Like '*' & SearchSampleID & '*'"

End Select

Forms!frmSampleStatus!subformSampleStatus.Requery
DoCmd.OpenForm "frmSearchSampleStatus", acNormal, , sWhere, , acHidden
End Sub

[NickWren]

i even tried using DoCmd.OpenQuery .... but it says it has to many parameters if I wanted to use the sWhere

In VBA if I put

WHERE [PatientName] = "Like '*' & SearchPatientName & '*'"

somewhere above in the case select would that work for a where clause?

[ssanfu]

Lets start on terminology so we are talking grapefruit to grapefruit.

Only Tables have fields.
Forms have controls.
Forms can be unbound or bound. If a Form is unbound, it has unbound controls. If a form is bound, it has a record source of a table or query.
A bound Form can have unbound or bound controls. A bound control is tied (bound) to a field in the form record source.

----------------------------

It sounds like you want to make a search form. There are many ways to do this. Here is one.

Setting a filter

Lets say you have a form "frmTests" that has a record source of "qrySampleStatus".
This is a simple query that is something like

SELECT tblPatients.PatientID_PK, tblPatients.PatientName, tblTests.*
FROM tblPatients INNER JOIN tblTests ON tblPatients.PatientID_PK = tblTests.PatientID_FK;

In this case, "PatientID_FK" is a foreign key to the patient table and is a Long Integer.

The form Default View is set to Continuous Forms view.
You open the form and every record is displayed.

Now you add some unbound controls in the form header.
You add 4 text boxes and 2 combo boxes. The text boxes you rename: "tbPatientID" (tb = text box), "tbReceivedSampleDate", "tbCGACollectionDate" and "tbSampleID".
The combo boxes you rename: "cboPatientName" and "cboPhysicianName".
And you add a button to execute the code. An example of the button click code is at http://www.allenbrowne.com/ser-62code.html
(You will have to modify the code for your table and control names.)

So you enter the search criteria you want, then click the button. The records that match the criteria are displayed, if any.

You can add another button to remove the filter. The code for that is simple:

Me.Filter = ""
Me.FilterOn = False

---
Limiting records returned

Another option is to use criteria to limit records returned. You would use an unbound form with unbound controls and the same code except it wouldn't set a filter. It would be a string variable like "strWhere" that would be used in a line like:

DoCmd.OpenForm "frmSearchSampleStatus", acNormal, , sWhere