Select from form

[samonwalkabout]

I have a select query that gets it criteria from a form using the [forms]![frmrep]![field] format... this works fine up to two criteria (selecting two diffrent fields to filter by) then any additional fields with criteria dont work? no error messages the query just ignores them.......... is this a 2000 bug? is there a way around?

 

[jfgambit]

Are you sure that all the fields on the form are filled in? If not, are you using an "if" statement in the criteria (i.e. IIF([Forms]![frmName]![FieldName] = "",[FieldNameinTable],([Forms]![frmName]![FieldName]"?

Also, have you considered having the query re-written with the On Click event of a command button from the form?

Example:

Private Sub cmdSearch_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef

Set dbNm = CurrentDb()

strSQL = "SELECT tblCustomers.CustID AS [CUST ID], tblCustomers.CName AS NAME, tblCustomers.CPhone AS PHONE, tblCustomers.CEMail AS EMAIL, tblCustomers.CCardExp AS [EXP DATE], tblCustomers.BCity AS CITY, tblCustomers.BState AS STATE, tblOrders.OrderID AS [ORDER] " & _
"FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustID = tblOrders.CustID"
strWhere = "WHERE"
strOrder = "ORDER BY tblCustomers.CustID;"

' set where clause conditions
If Not IsNull(Me.txtCustID) Then
strWhere = strWhere & " (tblCustomers.CustID) Like '*" & Me.txtCustID & "*' AND"
End If
If Not IsNull(Me.txtOrder) Then
strWhere = strWhere & " (tblOrders.OrderID) Like '*" & Me.txtOrder & "' AND"
End If
If Not IsNull(Me.txtName) Then
strWhere = strWhere & " (tblCustomers.CName) Like '*" & Me.txtName & "*' AND"
End If
If Not IsNull(Me.txtPhone) Then
strWhere = strWhere & " (tblCustomers.CPhone) Like '*" & Me.txtPhone & "*' AND"
End If
If Not IsNull(Me.txtEMail) Then
strWhere = strWhere & " (tblCustomers.CEMail) Like '*" & Me.txtEMail & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'

Me.lstInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub


HTH

 

[samonwalkabout]

im using [forms]![frmexample]![example] or [forms]![frmexample]![example] is null. So blank fields are not a problem, im requerying the a subform to display the results on the afterupdate of the combo box example. It all works it just wont do more than 2 at a time. Will look into this code a bit more but would love a quick fix for my exisiting set-up......like thats ever gonna happen lol

 

[Pat Hartman]

More than two criteria should be fine. You'll need to post your code.

 

[samonwalkabout]

SELECT [Inital PMQ data].[PMQ #], [Inital PMQ data].[Start Date], [Inital PMQ data].Material, [Inital PMQ data].supplierID, [Comment blocks].Details, [Progress Meter].[Production Runs], [Inital PMQ data].Priority, [Comment blocks].[Comments 1], [Comment blocks].[Comments 2], [Comment blocks].[Comments 3], [Comment blocks].[Comments 4], [Comment blocks].[Comments 5], [Comment blocks].[Comments 6], [Comment blocks].[Comments 7], [Comment blocks].[Comments 8], [Comment blocks].[Comments 9]
FROM ([Inital PMQ data] LEFT JOIN [Comment blocks] ON [Inital PMQ data].[PMQ #] = [Comment blocks].[PMQ #]) LEFT JOIN [Progress Meter] ON [Inital PMQ data].[PMQ #] = [Progress Meter].[PMQ #]
WHERE ((([Inital PMQ data].[PMQ #])=[forms]![frmprodrep]![pmqnumber]) AND (([Inital PMQ data].Material)=[forms]![frmprodrep]![mater]) AND (([Inital PMQ data].supplierID)=[forms]![frmprodrep]![supp])) OR ((([Inital PMQ data].Material)=[forms]![frmprodrep]![mater]) AND (([Inital PMQ data].supplierID)=[forms]![frmprodrep]![supp]) AND (([forms]![frmprodrep]![pmqnumber]) Is Null)) OR ((([Inital PMQ data].Material)=[forms]![frmprodrep]![mater]) AND (([Inital PMQ data].supplierID)=[forms]![frmprodrep]![supp])) OR ((([Inital PMQ data].supplierID)=[forms]![frmprodrep]![supp]) AND (([forms]![frmprodrep]![mater]) Is Null)) OR ((([Inital PMQ data].Material)=[forms]![frmprodrep]![mater]) AND (([forms]![frmprodrep]![supp]) Is Null)) OR ((([forms]![frmprodrep]![mater]) Is Null) AND (([forms]![frmprodrep]![supp]) Is Null));

still not working......just works for the 1st 2 supp and mater. but if i put in PMQnumber 1st then its works and supp. its always the 3rd one that does nothing.

 

[samonwalkabout]

Growing all the time

I think i know the cause now!!!

On closer inspection it seems as if my code is growing all the time i.e ever time i run the query it adds a new line into the critiea, this is why the last option stops working and then the next as the code becomes to long.. i think... any idea how i can stop the query changing (growing) every time i run it.

 

[samonwalkabout]

If i keep running it it keeps adding critea and sub querys untill it reaches a point and says " you cancelled the previos operation"

 

[Pat Hartman]

I neatened up the query and removed the extraneous parentheses to improve readability. What "it" is adding additional crtieria. Your own code or some gremlin in Access? When the selection criteria is especially complex as yours is, Access often reformats it if you switch to QBE view so that it better fits that display format. Sometimes, the reformatting actually breaks the selection criteria. There is no way to get Access to reformat the code so that it retains the same logic so the trick is to prevent it from feeling the need to reformat what you have written. When you have criteria this complex, do not ever switch to QBE view. As long as you save the query in SQL view and never switch to QBE view, Acess won't reformat your query. I write notes regarding this to myself that I put in the query description to warn myself to NEVER switch views for the queries with this problem.

SELECT 
[Inital PMQ data].[PMQ #], 
[Inital PMQ data].[Start Date], 
[Inital PMQ data].Material, 
[Inital PMQ data].supplierID, 
[Comment blocks].Details, 
[Progress Meter].[Production Runs], 
[Inital PMQ data].Priority, 
[Comment blocks].[Comments 1], 
[Comment blocks].[Comments 2], 
[Comment blocks].[Comments 3], 
[Comment blocks].[Comments 4], 
[Comment blocks].[Comments 5], 
[Comment blocks].[Comments 6], 
[Comment blocks].[Comments 7], 
[Comment blocks].[Comments 8], 
[Comment blocks].[Comments 9] 
FROM ([Inital PMQ data] 
LEFT JOIN [Comment blocks] ON [Inital PMQ data].[PMQ #] = [Comment blocks].[PMQ #]) 
LEFT JOIN [Progress Meter] ON [Inital PMQ data].[PMQ #] = [Progress Meter].[PMQ #] 
WHERE 
   ([Inital PMQ data].[PMQ #]=[forms]![frmprodrep]![pmqnumber] AND [Inital PMQ data].Material=[forms]![frmprodrep]![mater] AND [Inital PMQ data].supplierID=[forms]![frmprodrep]![supp]) 
OR ([Inital PMQ data].Material=[forms]![frmprodrep]![mater] AND [Inital PMQ data].supplierID=[forms]![frmprodrep]![supp] AND [forms]![frmprodrep]![pmqnumber] Is Null) 
OR ([Inital PMQ data].Material=[forms]![frmprodrep]![mater] AND [Inital PMQ data].supplierID=[forms]![frmprodrep]![supp]) 
OR ([Inital PMQ data].supplierID=[forms]![frmprodrep]![supp] AND [forms]![frmprodrep]![mater] Is Null) 
OR ([Inital PMQ data].Material=[forms]![frmprodrep]![mater] AND [forms]![frmprodrep]![supp] Is Null) 
OR ([forms]![frmprodrep]![mater] Is Null AND [forms]![frmprodrep]![supp] Is Null);

 

[samonwalkabout]

It worked!!

Thanks for your help. Re-wrote the whole thing in SQL view and it works again and again with out any additions to the code.