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]

My attempt at a SQL string since none of my previous Querys utilize Fields as Conditions in WHERE clause.

I wanted to set my SQL statement as a declared string before the DoCmd.RunSQL
I feel like that my WHERE clause is not going to be Syntaxed correctly. especially since i am using Dates based on form fields. what do i need to change on this? I got an Compile error highlighting the WHERE clause of my SQL string stating it was expecting "a line number or label or end of statement"

sqlstr = "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 AND Maindata.SupplierCode=Me.Code AND Maindata.Partnumber=Me.Pnum AND Maindata.AisinName=Me.Pname AND Maindata.Parttype=Me.Ptype" & _
          " AND Maindata.NAMC=Me.Cust AND Maindata.AACTQE=Me.qe AND Maindata.OfficialIssuancedate BETWEEN Me.Ostart AND Me.Oend AND Maindata.InitialIssuancedate BETWEEN Me.istart AND Me.iend"

 

[Micron]

Just a quick reply to help you advance (hopefully) because I've got something to do in the next 60 minutes or so.
You must concatenate variables and references/expressions, otherwise what you are doing is making that portion a literal string that refers to something that doesn't exist. You have no field in the underlying table named Me.Ostart, but that's what you're saying.
BETWEEN Me.Ostart AND Me.Oend

" . . . BETWEEN #" & Me.Ostart & "# AND "# & Me.Oend & "# AND Maindata.InitialIssuancedate . . . "
When doing this, always debug.print you sql variable and examine the output. If it looks right you could try continuing on. However, I recommend copy it and paste into a new query in sql view, then switch to datasheet view. If it balks, it will flip back to sql view and most likely highlight at or near the offending part. Often the failure is a missing space. If it produces output that looks right, you should be good to go. The exception might be in the case of UPDATE query because that will often produce a sheet of nulls because it's showing you how many records will be updated, but not always what the update will be. I say datasheet view because you can safely display the results of action queries without actually running them.

EDIT - yes you also need delimiters for dates (#) and text (" or ')

 

[Micron]

Update: I managed to get what seems to be a well formed sql statement. Since I don't have your form, I could not use Me.anything so I substituted number values otherwise those nulls (missing controls) caused code to run-on. I also found that in trying to lay out the code, the line continuation characters presented a problem (and I don't like them anyway) so I used my method. You could sub your control names where the numbers are and see what you get. Note that I aliased the table instead of repeating it ad nauseam. BTW, you should not have special characters in object names (save for underscore, which I never use) - QIMS#

sqlstr = "SELECT T1.ID, T1.QIMS#, T1.Doctype, T1.Rank, T1.SupplierCode, T1.PartNumber, T1.OverallStatus, T1.Defect, T1.Qty,"
sqlstr = sqlstr & " T1.OfficialIssuancedate, T1.InitialIssuancedate, T1.NAMC, T1.AisinName, T1.AACTQE, T1.Parttype FROM"
sqlstr = sqlstr & " Maindata As T1 WHERE T1.Doctype= " & 99 & " AND T1.SupplierCode= " & 88 & " AND"
sqlstr = sqlstr & " T1.Partnumber= " & 44 & " AND T1.AisinName= " & 33 & " AND T1.Parttype= " & 22 & " AND"
sqlstr = sqlstr & " T1.NAMC= " & 11 & " AND T1.AACTQE= " & 9 & " AND T1.OfficialIssuancedate BETWEEN #"
sqlstr = sqlstr & 8 & "# AND #" & 7 & "# AND T1.InitialIssuancedate BETWEEN #" & 6 & "# AND #" & 5 & "#"

My output was

SELECT T1.ID, T1.QIMS#, T1.Doctype, T1.Rank, T1.SupplierCode, T1.PartNumber, T1.OverallStatus, T1.Defect, T1.Qty,
T1.OfficialIssuancedate, T1.InitialIssuancedate, T1.NAMC, T1.AisinName, T1.AACTQE, T1.Parttype FROM Maindata As T1
WHERE T1.Doctype= 99 AND T1.SupplierCode= 88 AND T1.Partnumber= 44 AND T1.AisinName= 33 AND T1.Parttype= 22
AND T1.NAMC = 11 AND T1.AACTQE= 9 AND T1.OfficialIssuancedate BETWEEN #8# AND #7# AND T1.InitialIssuancedate BETWEEN #6# AND #5#

Note that I presumed all controls that don't seem to be dates will contain number data type, which is probably not true where the field name contains the word "name" so text delimiters would be required as well.

 

[Kemidan2014]

I took your advice about copying it into a new query SQL and figured out where my syntax was incorrect for starters i switched all my "AND" to "OR" to get the flexibility i was needing (except for the BETWEEN Date 1 AND Date 2 bit)

Do i need to bracket off the Date portion? or does access know that if i am prefacing with BETWEEN it expects an AND? and not treat the field after that AND as seperate criteria?

AND i found the compile error... I forgot a "& _" After "FROM Maindata"

 

[Micron]

No brackets for date portion required but if it makes deciphering (for you) easier then no harm adding them. Yes, AND would be proper.
Therein lies one reason why I don't use & _

 

[Kemidan2014]

So I am getting a Runtime 2342 a runSQL action requires a statement

sqlstr = "SELECT Maindata.ID, Maindata.[QIMS#], Maindata.Doctype, Maindata.Rank, Maindata.SupplierCode, Maindata.PartNumber, Maindata.OverallStatus, Maindata.Defect,"
sqlstr = sqlstr & " Maindata.Qty, Maindata.OfficialIssuancedate, Maindata.InitialIssuancedate, Maindata.NAMC, Maindata.AisinName, Maindata.AACTQE, Maindata.Parttype"
sqlstr = sqlstr & " FROM Maindata"
sqlstr = sqlstr & " 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"
sqlstr = sqlstr & " 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"

DoCmd.RunSQL (sqlstr)

 

[welshgasman]

Add Debug.Print sqlstr before you try and run it.
Post back the output.

However I would remove the brackets as well?

 

[Kemidan2014]

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
"

 

[Kemidan2014]

Playing around with it I tried something like this

 Dim qdf As QueryDef
    strSQL = "Insert my massice select statement here'
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "tempQry"
    On Error GoTo 0
    Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
    DoCmd.OpenQuery ("tempQry")

When i clicked the button in my form it proceded to run the qry where it pops up individual windows asking for Values INSTEAD of referencing the form. which tells me my Select statement is correct

Then i tried removing all of the qrydef stuff and doing "DoCmd.RunSQL "sqlstr" in quotes and it threw up an ERROR 3129 asking that its expecting SELECT, UPDATE, INTO etc..

 

[Micron]

I suggest you look up your own errors and if not successful, then state that when asking. The cause of the error should be obvious if you did that.
EDIT - you are also not using it as advised in post 10. Pretty sure you cannot run sql from code. You'd have to use a recordset or querydef. Might as well just copy/paste the debug.print output into a new query to test if that's what you're trying to do.