Another Date Sorting Question...

[bodhi23]

Happy New Year all!

I'm new to the forum and have been lurking for a few weeks... gotten some very good answers to my questions.

I have been searching and searching for the answer to a particular problem, which could fall under either reports or queries, I'm just not sure which is the problem yet (though I'm thinking it can be fixed in the query...)

Ok, here goes:
I have a table containing copious amounts of student data. I want to create a list of students' names, email addresses and birthdates so that I can have a work study student send an e-card to each student.

I've got the basic query done, which includes the students names, their birthday, email address and a couple of other fields whereby I am selecting enrolled, active students (not shown). Currently, the query is only being sorted by student names, as sorting by birthday put the dates in order by year, month, day. I really could care less if the year shows up at all, though the original field in the table is formatted as Date/Time, Short Date. I just need the month and day... It'd be nice if I could get it in Academic Year format, August to July, but calendar order (Jan-Dec) would be acceptable...

I have tried creating the report in a variety of ways, and am not having much success with the sorting. I want to sort the list by the birthdate: "mm/dd" or "mmmm/dd". I've tried grouping by [DOB], which allows me to create the month/day format I want to use, but the list is still being sorted by the YEAR in the [DOB] field. So I get the proper format, in an incorrect order. I've played a little with the Order By field and the Group On field, but I don't know enough about expressions or SQL to puzzle this one out with out a little direct assistance... any suggestions would be most appreciated! Thanks!

 

[sonny]

Well, I guess you could use MID on that field into another by using:

Returns a specific number of characters from a text string starting at the position you specify.

Syntax

NewMonth:MID(text,start_num,num_chars)

Text is the text string from which you want to extract the characters.

Start_num is the position of the first character you want to extract. The first character in Text is 1.

Num_chars specifies how many characters to return from Text.

 

[bodhi23]

Call me thick, but that didn't make any sense to me.

First: What does MID mean?

Second: Where do I put that information?

I have a Date/Time format from the original table, 99/99/0000,0,0. Not using a text field.

Are you suggesting I create a second field in the report and use "NewMonth:MID(text,start_num,num_chars)" as the Control Source?

If I do that, will it sort the dates properly by month in calendar order? What I want to do is go from 99/99/0000 to 99/99 or MMMM DD, in calendar order...

Would you expand upon your suggestion for me please?
Thanks for your help!

 

[The_Doc_Man]

First, MID$ is a function name. But it only applies if your B'day info is in text mode with fixed format including leading zeros for the day part. If it is a date field, that's another ball of wax.

Second, you are discovering a fact about DB design (in a roundabout way). If you want to sort on something, you need a way to provide it in the format you want to sort. I.e. if you want to sort by month and day, you need to have fields containing the month and day WITHOUT THE YEAR!

The way to do this involves queries. Add this fact to your newbie arsenal - in most if not all cases, a query does anything a table can do, and more.

So if you want to sort by Month and Day, you need a couple of formulas in your query to COMPUTE the month and day, plus the rest of the record that you wanted to see.

Let's say for the sake of argument that the birthdate info is [BDate] ('cause it is a short name and I hate long field names.)

In your query grid, one of the fields might be Month: DatePart("mm", [Bdate]) and another would be Day: DatePart("dd", [BDate]). Once these fields exist, you could sort on those two items in the query. Be sure to include any other required data in the rest of the query so you can see everything you need in one place. (One-stop shopping... a good query-building guideline at any time.)

Now, if the date is not in date format (boo, hiss!!!!), convert it using CDate first. That would be... Month: DatePart("mm", CDate([BDate])) for the month.

By the way, look up DatePart in the help files BEFORE you actually use this because you might prefer another way of expressing the date. Also, I might have forgotten the exact syntax for the parts you wanted. (It happens, and I'm shooting from the hip here.) The help topic will include or at least point you to the right list of possible date parts you can call out using that function.

 

[sonny]

Sorry about the answer I gave you... I read too fast and thought I had what you asked.

In a query this will produce the month, the year and the month + year

SELECT Format([myDate],"mm") AS [Month], Format([myDate],"yy") AS [Year], Format([myDate],"mm/yy") AS [Both]
FROM tblDates;

 

[bodhi23]

I'm a self-taught Access user, so some things are a little muddy for me... Sonny, your answer sure did sound good - I just wasn't sure of the application!

The_Doc_Man - let me look into that DatePart function and see if I can make it work - that sounds like what I'm looking for.

Good thing I like to use queries for reports! Useful when data changes frequently...

I'll holler back if DatePart doesn't solve my problem!
Many thanks!


edit - incidentally, the birthdate field is labeled [DOB] - see if that suits your short field name passion!

 

[bodhi23]

Well now that didn't take long.

Ok, this is what I've done:

In the query design grid, I have added 2 new columns:
Field: Month: DatePart("mm", [DOB])
Table: tbl2004_2005Student
Sort: Ascending

and

Field: Day: DatePart("dd", [DOB])
Table: tbl2004_2005Student
Sort: Ascending

When I save it and try to view the data, I get an error message telling me there's an extra ")" in the field for Month...

What have I done wrong?
(If it makes a difference, I'm on Access 2002, WinXP Pro)