Strange query additions

[tjtross]

I am very new to the Access world. When I set up a query to be run, I am using a several combo boxes to to enter Criteria for the query. I have the following code, enter in the criteria "Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)" It works great until I save it and exit Access. When I come back in to the database, it has duplicated itself multiple times below the original criteria line and totally screws up my query.
I have several other criteria lines being driven by combo boxes and they are all doing the same thing.
Any help would be greatly appreciated.

 

[KenHigg]

Access has to take the entries in the criteria lines and form them into what it thinks is the correct sql statement when you close the form. These are the results you see when you open it back up. The only work around is to really get you head around how the and/or stuff works within rows and columns when you have multiple criteria. Generally it's all 'and' between cols and or between rows. Then see if you can refashion your criteria where Access does bomb when it tries to re-write it. And... it may be the 'and' in your between statement that access is having trouble with... Maybe if you put parens around it?

 

[tjtross]

Here is what it looks like when I save and exit Access. The only things are put in are under:

strArea: [Forms]![frmStartup]![CA_Area] or [Forms]![frmStartup]![CA_Area] is null

Date: Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)

strRegId: [Forms]![frmStartup]![CB_RegInd] or [Forms]![frmStartup]![CB_RegInd] is null

It adds all the extra lines and creates some expressions at the end of the query.

I am so confused with this and it slows everything down.

 

[JHB]

Show also the SQL string!

 

[Galaxiom]

It is exactly as Ken explained.

Access does this sometimes. In my experience is an equivalent expression to what you designed although more verbose. The database engine should run the same query anyway.

You need to manage the AND, OR and parentheses in a single row if you want it to stay. A good time to start writing queries in the SQL View.

 

[tjtross]

Here is the SQL query after I enter the criteria:
SELECT qryForm_Date.Date, tblInformation.strArea, tblInformation.strRegInd, tblInformation.strOrderType, tblInformation.strOrderNo, tblInformation.strFLOC, tblInformation.strDescription, Sum(tblProgram.lngProgramHrs) AS SumOflngProgramHrs, Sum(tblPlanned.lngPlannedHrs) AS SumOflngPlannedHrs, Sum(tblScheduled.lngScheduledHrs) AS SumOflngScheduledHrs, Sum(tblActual.lngActualHrs) AS SumOflngActualHrs
FROM (((((tblInformation RIGHT JOIN tblActual ON tblInformation.strOrderOps = tblActual.strOrderOps) LEFT JOIN tblScheduled ON tblInformation.strOrderOps = tblScheduled.strOrderOps) LEFT JOIN tblPlanned ON tblInformation.strOrderOps = tblPlanned.strOrderOps) LEFT JOIN tblProgram ON tblInformation.strOrderOps = tblProgram.strOrderOps) LEFT JOIN tblStatus ON tblInformation.strOrderOps = tblStatus.strOrderOps) LEFT JOIN qryForm_Date ON tblInformation.strOrderNo = qryForm_Date.strOrderNo
GROUP BY qryForm_Date.Date, tblInformation.strArea, tblInformation.strRegInd, tblInformation.strOrderType, tblInformation.strOrderNo, tblInformation.strFLOC, tblInformation.strDescription
HAVING (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strArea)=[Forms]![frmStartup]![CA_Area] Or [Forms]![frmStartup]![CA_Area] Is Null) AND ((tblInformation.strRegInd)=[Forms]![frmStartup]![CB_RegInd] Or [Forms]![frmStartup]![CB_RegInd] Is Null) AND ((tblInformation.strOrderType)=[Forms]![frmStartup]![CA_WType] Or [Forms]![frmStartup]![CA_WType] Is Null))
ORDER BY tblInformation.strArea, tblInformation.strRegInd, tblInformation.strOrderType;


and here it is after Access "helps' me:
SELECT qryForm_Date.Date, tblInformation.strArea, tblInformation.strRegInd, tblInformation.strOrderType, tblInformation.strOrderNo, tblInformation.strFLOC, tblInformation.strDescription, Sum(tblProgram.lngProgramHrs) AS SumOflngProgramHrs, Sum(tblPlanned.lngPlannedHrs) AS SumOflngPlannedHrs, Sum(tblScheduled.lngScheduledHrs) AS SumOflngScheduledHrs, Sum(tblActual.lngActualHrs) AS SumOflngActualHrs
FROM (((((tblInformation RIGHT JOIN tblActual ON tblInformation.strOrderOps = tblActual.strOrderOps) LEFT JOIN tblScheduled ON tblInformation.strOrderOps = tblScheduled.strOrderOps) LEFT JOIN tblPlanned ON tblInformation.strOrderOps = tblPlanned.strOrderOps) LEFT JOIN tblProgram ON tblInformation.strOrderOps = tblProgram.strOrderOps) LEFT JOIN tblStatus ON tblInformation.strOrderOps = tblStatus.strOrderOps) LEFT JOIN qryForm_Date ON tblInformation.strOrderNo = qryForm_Date.strOrderNo
GROUP BY qryForm_Date.Date, tblInformation.strArea, tblInformation.strRegInd, tblInformation.strOrderType, tblInformation.strOrderNo, tblInformation.strFLOC, tblInformation.strDescription
HAVING (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strArea)=[Forms]![frmStartup]![CA_Area]) AND ((tblInformation.strRegInd)=[Forms]![frmStartup]![CB_RegInd]) AND ((tblInformation.strOrderType)=[Forms]![frmStartup]![CA_WType])) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strRegInd)=[Forms]![frmStartup]![CB_RegInd]) AND ((tblInformation.strOrderType)=[Forms]![frmStartup]![CA_WType]) AND (([Forms]![frmStartup]![CA_Area]) Is Null)) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strArea)=[Forms]![frmStartup]![CA_Area]) AND ((tblInformation.strOrderType)=[Forms]![frmStartup]![CA_WType]) AND (([Forms]![frmStartup]![CB_RegInd]) Is Null)) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strOrderType)=[Forms]![frmStartup]![CA_WType]) AND (([Forms]![frmStartup]![CA_Area]) Is Null) AND (([Forms]![frmStartup]![CB_RegInd]) Is Null)) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strArea)=[Forms]![frmStartup]![CA_Area]) AND ((tblInformation.strRegInd)=[Forms]![frmStartup]![CB_RegInd]) AND (([Forms]![frmStartup]![CA_WType]) Is Null)) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strRegInd)=[Forms]![frmStartup]![CB_RegInd]) AND (([Forms]![frmStartup]![CA_Area]) Is Null) AND (([Forms]![frmStartup]![CA_WType]) Is Null)) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND ((tblInformation.strArea)=[Forms]![frmStartup]![CA_Area]) AND (([Forms]![frmStartup]![CB_RegInd]) Is Null) AND (([Forms]![frmStartup]![CA_WType]) Is Null)) OR (((qryForm_Date.Date) Between Nz([Forms]![frmStartup]![CD_StartM],0) And Nz([Forms]![frmStartup]![CD_EndM],3000/12)) AND (([Forms]![frmStartup]![CA_Area]) Is Null) AND (([Forms]![frmStartup]![CB_RegInd]) Is Null) AND (([Forms]![frmStartup]![CA_WType]) Is Null))
ORDER BY tblInformation.strArea, tblInformation.strRegInd, tblInformation.strOrderType;

 

[Galaxiom]

I turn complex report recordsource queries into Union queries to stop Access trashing the SQL layout. Should work for your problem too because the Query Designer can't handle UNION queries.

Create a one field table with no records. I call my table Dummy.

Add this line to the end of the query:

UNION ALL SELECT Null, Null, Null FROM Dummy

Put in as many Nulls as needed to match the number of fields in the real query.