Sort by date and year

[Douglasrac]

I have a query that feed a report and the format of the field is mmm yyyy.



I was trying to sort by Month because it was sorting by A to Z, and reading the forum I create a new field that is MM, so I can sort the number and that will sort the month. But now it got worst, since january 2011 comes above december 2010.

How can I sort by real DATE?

P.S.: The field mmm yyyy is a summary of several dates. In the table its a normal date field dd mm yyyy. So in the query its a summary by month.

[pbaldy]

I'd create a new field in the query:

Format(DateField, "yyyymm")

which will sort properly. You can display the other format to the user.

[Douglasrac]

I'd create a new field in the query:

Format(DateField, "yyyymm")

which will sort properly. You can display the other format to the user.

Thanks.
But how do I change the way the user see? In the report I'm trying to change but no sucess.

I also want to create now a query to show only this week records. How do I filter that? The only date criteria I know is today() and date between etc. But this week is possible?

[pbaldy]

I would have 2 fields in the query, one with each format. In the report's Sorting and Grouping, use mine, but in the report field visible to the user, use yours.

You can use a criteria using between:

Between OneDate And AnotherDate

replacing the 2 date references with the appropriate functions:

http://www.pacificdb.com.au/Support/...kfunctions.htm

[Douglasrac]

I would have 2 fields in the query, one with each format. In the report's Sorting and Grouping, use mine, but in the report field visible to the user, use yours.

Works perfectly!

You can use a criteria using between:

Between OneDate And AnotherDate

replacing the 2 date references with the appropriate functions:

http://www.pacificdb.com.au/Support/...kfunctions.htm

I built this:

Between ([Birthday]-Weekday([Birthday];2) +1) And ([Birthday]-Weekday([Birthday];1) +7)

But the result is all records. Like there is no criteria.

[pbaldy]

You're basically comparing records against themselves. Try

Between (Date() - Weekday(Date(), 2) + 1) And (
Date() + (7 - Weekday(Date(), 2)))

[Douglasrac]

You're basically comparing records against themselves. Try

Between (Date() - Weekday(Date(), 2) + 1) And (
Date() + (7 - Weekday(Date(), 2)))

Now it doesn't bring any records.
What is Date() ? Today?

[pbaldy]

Yes; more accurately the system date from the computer. If this is a birthday field, you'd want to go about it differently, since you really only want to match on month and day, not year.

[Douglasrac]

Something is wrong. I tried to make a simple one, only with month:

WHERE (Month([Birthday])=Month(Date());

Worked. But then I did this:

WHERE (Month([Birthday])=Month(Date()) And Weekday([Birthday])=Weekday(2));

Should appear all records of the month that have weekday Monday, right? But it is appearing one record from Friday. Day 22 to be exact.

[pbaldy]

If you're looking for birthdays in the next x days, this type of thing should work:

WHERE (((DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate]))) Between Date() And DateAdd("d",7,Date())))

[Douglasrac]

I get "Data type mismatch in the expression"

Birthdays in the next days will work fine, but this week birthdays is too hard to make?

[pbaldy]

Are there any records with no birth date? That can cause that error. If so, you can wrap the birthday field in the Nz() function:

Nz([Birthdate],0)

[Douglasrac]

Yes, there are records without birthday.

Thank you! Once more, working like a charm.

[pbaldy]

Happy to help!