sort, group, and paramet criteria for a date

[robina]

Hhi,
I have a query that is currently returning what I want except my date field is ordered by jan/2011, jan/2012, feb/2011, feb/2012...
I need it to sort simply by the date:
jan/2011, feb/2011, mar/2011

Also, I have a form with two text boxes for the beginning date and ending date I wish to run the query on. Not working. Here is the query (based on a table named [Ftag Data tbl]:

Month: (Format([RECORD DATE],"mmm/yyyy")) total row has "Group By" and field is showing.

Open: Sum(IIf([Ftag Data tbl]![DATE COMPLETED] Is Null,1,0)) total row has "Expression"

Closed: Sum(IIf([Ftag Data tbl]![DATE COMPLETED] Is Not Null,1,0)) total row has "Expression"

Month([RECORD DATE]) Total row has "Group By" sort is Ascending, show is NOT checked

I want the date field to show MMM/yyyy and that is what its doing. I need the [RECORD DATE] to use the criteria of BETWEEN Forms![Chart_selection]![TxtBDate] AND Forms![Chart_selection]![TxtEDate]

When I add that criteria its not recognizing it. any help is appreciated

 

[Beetle]

Month([RECORD DATE]) Total row has "Group By" sort is Ascending, show is NOT checked

The above column returns the Month number (1 - 12) and then sorts by that, which is exactly what it is doing per your description. If you want it to sort by the actual date, then you need to remove the Month function from the above and just return the Record_Date to sort by.

I want the date field to show MMM/yyyy and that is what its doing. I need the [RECORD DATE] to use the criteria of BETWEEN Forms![Chart_selection]![TxtBDate] AND Forms![Chart_selection]![TxtEDate]

Once you fix the above mentioned column, place your Between criteria in the criteria row for the Record_Date column.

 

[vbaInet]

For the sorting you can drop the same date field again, untick the "Show" checkbox and apply a sort under it. You will also use this field for your criteria.

 

[robina]

When I remove the Month function from the [RECORD DATE] field (which has show unchecked) my query does not group by month anymore, it shows all dates, which is not what i need. I tried the BETWEEN DATE criteria placed in this field and it wasn't recognized. I also tried it in the [RECORD DATE] field that is being shown and it wasn't recognized either. Does it make a difference that the date fields on the form are text boxes?

I still need the RECORD DATE to group by month and to recognize the dates on a form for criteria. I appreciate everyone's time and knowledge. I sound like a novice but I'm not and it just doesn't make sense to me.

 

[vbaInet]

Right, keep the Month() function and follow what I mentioned in my post.

Beetle was just telling you why you had that behaviour.

 

[robina]

when i do that i get this error:
You tried to execute a query that does not include the specified expresssion 'Format([RECORD DATE]"mmm/yyyy") as part of an aggregate function. can you help?

 

[vbaInet]

You need to select Group By in the "Total:" row under this field.

 

[robina]

Hi,
I've done this. I am truly puzzled. I am getting the correct grouping by month now, however, if i choose a year other than present year it includes 2011 and 2012. I've broken this into 2 queries. Here is the SQL for the first query:

SELECT DISTINCTROW (Format([RECORD_DATE],"mm/yyyy")) AS Record_Month, FtagData_tbl.LINE, FtagData_tbl.TYPE, Sum(IIf([FtagData_tbl]![DATE_COMPLETED] Is Null,1,0)) AS [Open], Sum(IIf([FtagData_tbl]![DATE_COMPLETED] Is Not Null,1,0)) AS Closed
FROM FtagData_tbl
GROUP BY (Format([RECORD_DATE],"mm/yyyy")), FtagData_tbl.LINE, FtagData_tbl.TYPE, FtagData_tbl.RECORD_DATE
HAVING (((FtagData_tbl.LINE)=[Forms]![Chart_selection]![CboLine]) AND ((FtagData_tbl.TYPE)=[Forms]![Chart_selection]![CboType]) AND ((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate])) OR (((FtagData_tbl.TYPE)=[Forms]![Chart_selection]![CboType]) AND ((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]) AND (([Forms]![Chart_selection]![CboLine]) Is Null)) OR (((FtagData_tbl.LINE)=[Forms]![Chart_selection]![CboLine]) AND ((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]) AND (([Forms]![Chart_selection]![CboType]) Is Null)) OR (((FtagData_tbl.RECORD_DATE) Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]) AND (([Forms]![Chart_selection]![CboLine]) Is Null) AND (([Forms]![Chart_selection]![CboType]) Is Null))
ORDER BY FtagData_tbl.RECORD_DATE;

The final query is as follows:

SELECT DISTINCTROW Month([Record_Month]) & "/" & Year([Record_Month]) AS [Month/Year], Sum(Ftags_Chart_calcs.Open) AS [Open Ftags], Sum(Ftags_Chart_calcs.Closed) AS [Closed Ftags]
FROM Ftags_Chart_calcs
WHERE (((Ftags_Chart_calcs.LINE)=[Forms]![Chart_selection]![CboLine]) AND ((Ftags_Chart_calcs.TYPE)=[Forms]![Chart_selection]![CboType])) OR (((Ftags_Chart_calcs.TYPE)=[Forms]![Chart_selection]![CboType]) AND (([Forms]![Chart_selection]![CboLine]) Is Null)) OR (((Ftags_Chart_calcs.LINE)=[Forms]![Chart_selection]![CboLine]) AND (([Forms]![Chart_selection]![CboType]) Is Null)) OR ((([Forms]![Chart_selection]![CboLine]) Is Null) AND (([Forms]![Chart_selection]![CboType]) Is Null))
GROUP BY Month([Record_Month]) & "/" & Year([Record_Month])
ORDER BY Month([Record_Month]) & "/" & Year([Record_Month]);

Another thing that worries me is that the form i use to choose search criteria is extremely picky for date format. If you don't enter 01/01/2012 but enter 1/1/2012 or anything else it gives an error. I really feel like this could be a formatting issue, but I can't crack it.:banghead:

 

[vbaInet]

Show me the part of the criteria that filters by date.

 

[robina]

It would be more accurate for me to say search criteria rather than filter. After using dates entered into two text boxes on a form the query returns those results, then GROUPS BY month. it won't group by the actual year that is typed in the text boxes unless they are both 2012. I"m definitely out of ideas. thank you.

 

[vbaInet]

There's no part of your SQL string where you're Grouping by Year or Month. You're grouping by two of them combined.

 

[robina]

ok, then i am lost because from all that I've read, I thought I am grouping. Do you have a suggestion as to how I can do this?
thank you so much

 

[vbaInet]

If you used a Format of "mm/yyyy" in your final query you should be able to group by month and year. But in your final query you have Month() & "/" & Year() which returns 1/12 (for example) and that won't group propertly. Whereas mm/yyyy will return 01/12 and the grouping will be fine.

 

[robina]

okay, I changed my final query to the format you listed vbaInet. It groups nicely now, however, in the text boxes for beginning and end dates on my selection form, if you enter a begin date of 1/1/2011 and end date of with this format: 8/1/11 the query returns 2/12, 2/12, 4/12, 5/12, 6/12, 8/11, 8/12, 9/11 and 10/11. If I enter dates 1/1/2012 and 8/31/2012 it returns 9/`1 and 10/11.

 

[vbaInet]

You're filtering on the wrong field. Let's see your SQL statement(s),