Listing dates without reference to years; displaying only the day

[a2b2c2]

Good evening experts...

I am a greenhorn access guy. I developed my own database to keep track of climbing trips. To cut to the chase...
I am trying to list all the dates of my climbs with no regard for the year...as if the year wasn't even there. So when I sort the list, I will get a long list of dates with no year and no reference to a year. So it would start Jan 1 Jan 1 Jan 3 Jan 3 Jan 4...etc. Those dates could be from different years. I have tried the DatePart and was able to list all the climbs in January for example, however the order still references the year.

Second question: Is there a way to use a date input and have it only display the day (Monday, Wednesday...etc)? I have tried the choose function, but with no luck.

Some additional info: I have one table entitled "Climbs"; I have many fields to hold all sorts of climbing data...but also included the date. My key is just the climb # which is determined more when I input a climb and not necessarily the order of climbs. That is determined by the date and time.

My apologies in advance for the possible confusion in this question. I have researched this on and off for years and can't seem to nail down the procedure, on the assumption there is one. I am not versed in SQL.

Thank you so much,

Jim Retemeyer

[ItsMe]

If you do not want to display the year you could probably use the Left() function in aggregate with inStrREv()

This may give you issues with sorting in order because yhe result will be a string (text). So a better option may be to split the day out with the day() function and the month out with the month() function. Store each value in its own control on a form or in an alias in a query. you could add a third control/alias to concatanate the two back together.

As for day of the week, you can get an integer value using the Weekday() function. Then you could translate the result using a value list or a table.

Dim intMyDay As Integer
intMyDay = Weekday(#12/30/2013#)
MsgBox intMyDay

[June7]

Format(Date(),"dddd")

will return Monday, Tuesday, etc.

Refer to Access Help or web for more info on Format function.

If the order still sorts by year then need to specify different field in query for sorting.

[ItsMe]

Forgot about the format option...

[orange]

Design tables with full date, record full date, but you can display only Day or Day/Month via Format as June7 has said.

[a2b2c2]

Thank you very much ItsMe. I was able to use your example zip file. The only problem I ran into was that "1" was followed by "10" in both the day and month sort. Am I inputting the date in a certain way that doesn't distinguish between 01 and 1. I don't if I am making myself clear. Your example, in the zip file, sorting of Day/Month with no year works perfectly.

THank you,

Jim

[a2b2c2]

Thank you June 7. It works perfectly

Jim

[June7]

I looked at ItsMe's example db. The day and month extract fields sort properly. The NoYear will not sort properly because it is a text value and alpha sort rules. 10 will sort before 2.

I don't know what you are doing in your db but if the values are text and not numbers, alpha sort will be applied. You can tell if a field is text or number by left or right alignment.

[ItsMe]

June's explanation in post 8 is the reason I went down the path I did with Day and Month function. It will provide you an opportunity to sort (keeping in mind your rules about extracting month and day). I hope all of this is making sense.

[a2b2c2]

I looked up my design in my table and the Data Type for the Date is Date/Time as in your example. I think it has something to do with how I copied and pasted your query example. Everytime I run the query I get a message, twice, for a parameter "Query1.MyMonth" then "Query1.Date". I hit OK each time and then the query runs...but with the alpha sort...not numeric. I feel I am so close, but missing something simple. I am amazed how easy this seems for you all.

THanks,

Jim

[June7]

Access query engine is not finding fields named "Query1.MyMonth" and "Query1.Date"

Post your exact SQL statement for analysis or provide the db. See instructions at bottom of my post.

[a2b2c2]

Okay...I fixed the parameter problem.
I also misspoke when I said that your example query sorted the Day/Month perfectly. It is actually doing the same thing that mine does. I have a feeling it has to do with the "/" being inserted in the last column, which, I believe, makes it a alpha sort?

[June7]

No, it would be alpha sort even without the / because the result of the expression is still a string.

Test this expression:

NoYear: Format([MyDate],"mm/dd")

[a2b2c2]

Excellent June 7. It does exactly what I want it to do. Thank you very much.
I marked thread as solved.

Jim