simple query but result is difficult to analyze.

[narendertangutooei]

hi Friend,
I have a search form with 3 parameters
Name, Supplier, ProductType


at any condition the below code the result is showing all the records from QrySrchPolymer. Output is not at all changing for any given search criteria


please let me know where is the mistake and fix it.


waiting for Ur suggestion.

ID	Supplier.Name	PolymerName.Name	ProductType.Name	Color.Name	EModulus	Thickness	Datasheet
7	supplier 1	NARENDER	product1	color	10	1	0
8	ad	polymer 1	duct	color1	20	20	0
9	Anions Pharma	poly	type	color	30	3	0
10	divya	divya	product1	color1	30	48	0

Name : (table) PolymerName.Name ----> CName(Dim)
Supplier : Supplier.Name ----> SName
ProductType : ProductType.Name ----> TName




Private Sub Command121_Click()
' conditions a,b,c,ab,bc,ca,abc,all empty


Dim Qry, crt As String
Dim CName, SName, TName As String


'strString = Replace(strString, " ", "")


On Error Resume Next
If Not IsNull(Me.ChemicalName) Then
CName = Replace(Me.ChemicalName, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE PolymerName.Name Like '*" & CName & "*' "



ElseIf Not IsNull(Me.SupplierName) Then
SName = Replace(Me.SupplierName, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE PolymerName.Name Like '*" & SName & "*' "


ElseIf Not IsNull(Me.ProductType) Then
SName = Replace(Me.ProductType, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE ProductType.Name Like '*" & TName & "*' "



ElseIf Not IsNull(Me.ChemicalName) And Not IsNull(Me.SupplierName) Then
CName = Replace(Me.ChemicalName, " ", "")
SName = Replace(Me.SupplierName, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE PolymerName.Name Like '*" & CName & "*' and Supplier.Name Like '*" & SName & "*' "



ElseIf Not IsNull(Me.SupplierName) And Not IsNull(Me.ProductType) Then
SName = Replace(Me.SupplierName, " ", "")
TName = Replace(Me.ProductType, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE Supplier.Name Like '*" & SName & "*' AND ProductType.Name Like '*" & TName & "*'"



ElseIf Not IsNull(Me.ProductType) And Not IsNull(Me.ChemicalName) Then
CName = Replace(Me.ProductType, " ", "")
CName = Replace(Me.ChemicalName, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE ProductType.Name Like '*" & TName & "*' and PolymerName.Name Like '*" & CName & "*' "


ElseIf Not IsNull(Me.ChemicalName) And Not IsNull(Me.SupplierName) And Not IsNull(Me.ProductType) Then
CName = Replace(Me.ChemicalName, " ", "")
SName = Replace(Me.SupplierName, " ", "")
TName = Replace(Me.ProductType, " ", "")
Qry = "SELECT * FROM QrySrchPolymer WHERE PolymerName.Name Like '*" & CName & "*' and Supplier.Name Like '*" & SName & "*' AND ProductType.Name Like '*" & TName & "*'"

Else
Qry = "SELECT * FROM QrySrchPolymer"
End If
'MsgBox Qry
DoCmd.OpenReport "RptqrySearchPolymer", acViewReport, , OpenArgs:=Qry
End Sub

[John_G]

Does the MsgBox display the correct SQL? (uncomment it, though).
I suspect the problem is in the report. What does the report do with the OpenArgs, and does the report have a record source defined?

[June7]

Your OpenArgs are in the wrong location. Need 2 more commas:

DoCmd.OpenReport "RptqrySearchPolymer", acViewReport, , , , OpenArgs:=Qry

Instead of building a full SQL statement and passing in OpenArgs, can just build the WHERE clause and pass it in the WHERE CONDITION argument (which is where you have placed OpenArgs parameter).

[John_G]

Your OpenArgs are in the wrong location.

Actually, I dont think so. Since the OP used the OpenArgs:= syntax, it shouldn't matter should it (I'm not sure - I never use it)? But mixing the two kinds of parameter references is not a good idea, anyway.
More important is how that argument is being used in the report.

[davegri]

Why not just make the recordsource of the report itself Qry? Simple.
EDIT:
I just realized that Qry is not a named query, but just a string. I'll shortly post UDF here to convert the string to a named query. Call the UDF just before opening the report.
OK, here's the code:

' Purpose   : Attach new SQL property to an existing querydef. If the Query doesn't exist,
'           : create it with the passed SQL.
'---------------------------------------------------------------------------------------
'
Function fcnCustomizeSQL(qName As String, strPassedSQL As String) As Boolean
    Dim qthisQuery As DAO.QueryDef
    'if the query has been deleted, create it
    If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
        Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Set qthisQuery = Nothing
        Exit Function
    End If
    'else modify it
    Set qthisQuery = CurrentDb.QueryDefs(qName)
    qthisQuery.SQL = strPassedSQL
    Set qthisQuery = Nothing
End Function

qName is the name to give to the named query. strPassedSQL is the name of SQL string.
qName value would be the name of the report's recordsource. The name stays the same, and just the querys change.
Since you're creating a named query, it's easy to test, just run it!

[June7]

Okay, John, agreed. And I too have never used that parameter syntax.

Still recommend setting the report RecordSource to: SELECT * FROM QrySrchPolymer;

Then just build the WHERE clause. Open report with parameter in WHERE CONDITION:

DoCmd.OpenReport "RptqrySearchPolymer", acViewReport, , Qry

Then no need to modify query object or for code in report to handle OpenArgs.

Name is a reserved word and should avoid using reserved words as names. Also is used in multiple tables, recommend not duplicating field name across tables.

[John_G]

June7 - I agree completely.

Far better to do filtering outside the report, and use the WHERE parameter positionally.

John

[Micron]

I just realized that Qry is not a named query, but just a string.

Uh, no I don't think so. I'd say it's a variant. OP has made the classic error of multi line declarations.
Dim Qry, crt As String
Dim CName, SName, TName As String
Not saying it's the cause, but I think my esteemed peers would agree that leaving it up to Access to properly handle a variant variable when you don't realize it is a variant not a good idea.
narendertangutooei: please use code tags and indentation for anything more than a few lines of code. It makes it much easier to read - see post #5.