Search results

.. and here's the rest: ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND...

What Access does to the query is this: PARAMETERS [Forms]![frmMain]![Status] Text ( 255 ); SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1...

Well, I'm close. Your query *should* look like this: Parameters [Forms]![frmMain]![Status] Text; SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method...

Simply make sure that "ALL" is in the combo box Row Source - I assume it's a Value List, right? If you want all records regardless of status, then try what I suggested. The criteria needs to end up looking like: (combo box = "ALL") OR ((combo box = "OPEN") AND (Test status flags for open)) OR...

Clearly, you're calculating "Status" here: IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And...

Then stick this at the front of your WHERE clause: ([Forms]![frmMain]![Status]="ALL") OR ...

What is your criteria for "All?" And why in the world are you using IIf that will slow down your query immeasurably? For example, this: IIf(IsNull([Analyst]) And [Due Date]<Date(),True,False))=IIf([Forms]![frmMain]![Status]="OVERDUE",-1,0) Can be more simply stated as: (IsNull([Analyst])...

Try this: SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1, tblInvestigations.Ref2, tblInvestigations.Issue, tblInvestigations.[Root Cause]...

What is the Row Source of Status, and what is the Bound Column? Also, why are you using IIf? Instead of: (IIf([Due Date]<Date(),True,False)=IIf([Forms]![frmMain]![Status]="OVERDUE",-1,0) You could simply do: ([Due Date] < Date()) And ([Forms]![frmMain]![Status]="OVERDUE")

GSan- That's not my experience. Try going to the Immediate Window and running the FixCrosstabOrder code from there. I assume you fixed the query name and the field name in the code I posted. That should put "File Totals" at the end. The code basically renumbers the columns. Another...

After you run that code, what does the output of the query look like when you open it?

I just called it from the Immediate Window, then opened the query in Datasheet view. I would set the new SQL, close the QueryDef, then call the FixCrosstabOrder, then set the SourceObject. After you run FixCrosstabOrder, what do you see when you open the query? It worked for me.

GSan- I'm using 2007 also. I did some more dinking around and I found that you must set the ColumnOrder property for ALL the columns. This apparently what Access does internally if you move a field position in Datasheet view in the UI. If you just set ColumnOrder for that totals field, it...

GSan- A couple of comments on your code: Set LegalServiceDB = CurrentDb 'Get the current version of the query that is used by the crosstab query Set queryDefinition = LegalServiceDB.QueryDefs("TotalFileCountByNameAndT ype_Crosstab") ' <= Is this just a copy error? I see a space in the name...

Yes, that's correct. But your assignment of the ColumnOrder should push the total field to the end. As I look at your code again, I'm wondering what this is doing: ' Call the procedure to set the ColumnOrder property. Call SetQueryDefFieldProperty(totalFields) What is totalFields? The code...

Gsan- The ColumnOrder property is what determines the display order. What is the ColumnOrder property of the other fields?

That's very curious. If you open the query separately, does the total column show up in the right place?

GSan- Try setting the ColumnOrder property *before* you load the subform SourceObject. When you load the SourceObject first, it's picking up the default column sequence, and that won't change if you change the query after the load.

GSan- Setting that property should define the order that the columns appear in the query Datasheet. It's the same as opening the query in Datasheet view, moving the column to the end, then saving. Next time you open, the column order should hold. I tested in queries that had a Column...

Well, that's interesting. You get no results from either query? With an INNER JOIN, you should get rows that match in both tables. With a LEFT JOIN, you'll get all the rows from the table on the "Left" (WIA) and any matching on the "right". If you're getting no matches, then there may be...