Struggling with Editting a "search" query

[Kemidan2014]

So a couple months ago i created a Query, Form, and Report to allow users to Search the Maindata Table and present Records in a report given specific criteria for feilds.

everything was working fine until someone had actually asked me for a list of records by group for a criteria I had not originally provided a field for in my form. So when i went to add said feild so that i could provide a response for that request i noticed that my original query had automatically expanded with dozens of mix and match criterias.

and now when i try to put the query in Design mode, Access is freezing on me, forcing me to end task.

Originally in the query there was just 1 criteria [Forms]![Formname].[Fieldname] for like 9 fields all related to the Main data table.

I tried to compact and repair the database but it did not seem to alleviate the problem. any suggestions?

 

[Kemidan2014]

I actually had success running it as Querydef and using the form as criteria input for the WHERE clause by changing all the "Me." with "Forms!FormName!" and it produced a Datasheet view with results.

I appreciate your support and Advice

 

[Micron]

You can't use Me in a query sql, but if you concatenate Me.myField in vba it should work if you make the resulting sql the recordsource for a recordset, form or report. That is because Me.myField gets replaced with e.g. 55, or a date, or a text value. I thought you wanted this for a report in the end and I got sidetracked when it became about opening a query datasheet. Sorry about that. Glad you got it solved.

 

[welshgasman]

Did what you suggested and it just regurgitates the SQL SELECT statement string

"
SELECT Maindata.ID, Maindata.[QIMS#], Maindata.Doctype, Maindata.Rank, Maindata.SupplierCode, Maindata.PartNumber, Maindata.OverallStatus, Maindata.Defect, Maindata.Qty, Maindata.OfficialIssuancedate, Maindata.InitialIssuancedate, Maindata.NAMC, Maindata.AisinName, Maindata.AACTQE, Maindata.Parttype FROM Maindata WHERE Maindata.Doctype=Me.Type OR Maindata.SupplierCode=Me.Code OR Maindata.Partnumber=Me.Pnum OR Maindata.AisinName=Me.Pname OR Maindata.Parttype=Me.Ptype OR Maindata.NAMC=Me.Cust OR Maindata.AACTQE=Me.qe OR Maindata.OfficialIssuancedate BETWEEN Me.Ostart AND Me.Oend OR Maindata.InitialIssuancedate BETWEEN Me.istart AND Me.iend
"

You were advised to concatenate the controls/variables into the sql string? in post #13
That is the way I have always done it.
If you cannot do that successfully, then perhaps try another approach?

Pass parameters to Query via QryDef

I have been reading how I can pass parameters to a query using VBA but am struggling to find an example I fully understand. So say I have a form that when opened by one user, the parameter for a yes/no field is false but for another I would want it to be true, how would I change that on the go...

www.access-programmers.co.uk