Sorting access query data

[outlook]

PLEASE HELP!!!

I want to sort data in order by date, but it only sorts in alaphabetical order.

Does anyone know if I'm doing something wrong? Or how I can correct it?

Thanks.

 

[SydneyGeek]

If you have a date field (eg, real dates not text), make sure that the Sort row says Ascending (or Descending if you want last date first).

If the date sorting needs to be the highest priority, put the field first in the grid; Access prioritises sorting in left to right order across the grid.

Denis

 

[outlook]

It's a group query, so it takes real dates in the origial query, and then totals them by month in the group query.

Thus the month first is Dec, then Feb, Then Jan.

Any ideas?

 

[SydneyGeek]

Create a field with the month number, and sort on that. There is no need to display it in the query result; just use it for sorting.

Also, if you build a crosstab using the wizard and group dates, you will find that they generally sort by date rather than alphabetically. Check out the expression that Access uses for the date formatting.

Denis

 

[outlook]

I'm sorry to be a pain, but when you say "Create a field with the month number", what exactly do you mean by this?

Thank you, AGAIN!

 

[SydneyGeek]

Assuming that you have a real date field in the data set, you can use that.

If you only have the text items "Apr", "Aug", "Feb", etc they will sort alphabetically.

However, you can use the CHOOSE function to turn those descriptions into month numbers. Something like this, in the query --

MonthNumber:CHOOSE([your date heading field],"Jan","Feb","Mar",...,"Dec")

Change the name of the heading field to suit. Add the full list of months to the CHOOSE function. I got lazy, and only typed enough to demonstrate the concept.

Denis

 

[outlook]

Thank you for your help so far, but I am still having problems ...

Table has an actual date field called DEPOSIT DATE, so data would be 01/01/2011 for example.

Initial query then counts the number of deposits on each date, and shows them by the BRANCH field.

The total query then uses the intial query, by grouping the data by into months, and shows them in full month and year format, ie December 2010.

At the moment the total query is sorting the month by alphabetical order, which I do not want, so as you suggested I added a CHOOSE field. However it doesn't change the sort order, and when I show it, it's blank.

Somehow I've missed something in what your instructions. Shall I post my choose code? Or maybe you have some other ideas?

Thank you.

 

[SydneyGeek]

Paste what you have, but another option would be to have an extra expression in the query. eg,

DepositMonth:Month([DEPOSIT DATE])

You can group on that as well, and you should be able to sort the months correctly.

Denis

 

[outlook]

This is the field that groups the actual dates together:
DEPOSIT DATE By Month: Format$([Deposit Query].[DEPOSIT DATE],'mmmm yyyy')

Then, there's the field you suggested to sort the month by number:
MonthNumber: Choose([DEPOSIT
DATE],"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

My first thought is that maybe the choose function should be full months and years, ie. "January 2011"?

Also, what happens when you have Jan 2011 & Jan 2012 for example if you just use "Jan"?

Thanks.

 

[SydneyGeek]

Try creating another field in the query:

DepositMonth:Month([Deposit Query].[DEPOSIT DATE])

Denis