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

 

[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"

 

[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.