Query sorted by month and year

[Evans2]

Hi all,

I've got a form based on a query that lists all product samples sent out to customers in ascending order by MM/DD/YY. I can see how this will become overwhelming quickly as many samples are sent out each day and users will have to scroll endlessly to find something.



I'd like to organize all of the items by month and year.

What I have now:

8690	08/29/14	CRD
8691	08/29/14	KMK
8692	08/29/14	SHE
8693	09/02/14	EBS
8693	09/02/14	EBS
8694	09/02/14	EBS

What I would like:

+August 2014

8690	08/29/14	CRD
8691	08/29/14	KMK
8692	08/29/14	SHE

+September 2014

8693	09/02/14	EBS
8693	09/02/14	EBS
8694	09/02/14	EBS

Is it possible to have all samples grouped by month and year where the users can just click on the month and have a list expand?
Thank you,

Evan

[burrina]

I would use 2 combo's for this, one for the year and one for the month. If to be used on a form.
Default Value for year;

Year(Now())

Default Value for month;

Format(DateSerial(Year(Date()),Month(Date()),Day(D ate())),"mmmm ")

If in a query;

CurMonth: Format(DateSerial(Year(Date()),Month(Date()),Day(D ate())),"mmmm ")

HTH

[Evans2]

Thanks burrina,

I'm very new to SQL. Could you tell me where I might insert the code you mentioned above? I haven't been able to get it to work.

Here is the SQL for the query:

SELECT OutboundSamplingT.SO, OutboundSamplingT.DateofEntry, OutboundSamplingT.RequestedBy, OutboundSamplingT.PackedBy, OutboundSamplingT.MailedBy, OutboundSamplingT.SentDate, OutboundSamplingT.Customer, SampleEntryBoxT.Origin, SampleEntryBoxT.Type, SampleEntryBoxT.SampleRequested
FROM OutboundSamplingT INNER JOIN SampleEntryBoxT ON OutboundSamplingT.[SO] = SampleEntryBoxT.[SO Number]
WHERE (((OutboundSamplingT.SentDate) Is Not Null));

Much appreciated.

Evan Stockdale

[June7]

Controls used to input filter criteria must be UNBOUND. As burrina suggested, use comboboxes - cbxYear and cbxMonth.

Filter options:

1. dynamic parameterized query (I never use this method)
WHERE OutboundSamplingT.SentDate Is Not Null AND Year([date field])=Forms!formname!cbxYear AND Month([date field])=Forms!formname!cbxMonth

2. I prefer VBA code builds filter string http://allenbrowne.com/ser-62code.html

[Evans2]

Thanks for the reply June7,

I'm trying out Filter option #1 first. Would it be possible to get some help with the SQL syntax? I'm getting an error message so I'm sure something is out of place.

[June7]

Get rid of the comma in front of AND.