retrieving the day of the year

[Guest]

Hello, i'm wondering if anyone can help me on this.

instead of retrieving the day of the month from an Access tasks table:

st = DatePart("d", rs.Fields("tasks.task_ass_date"))
nxt = DatePart("d", rs.Fields("tasks.task_ass_date")) + 1
due = DatePart("d", rs.Fields("task_ddate"))

I would like to retrieve the day of the year:

st = DatePart("y", rs.Fields("tasks.task_ass_date"))
nxt = DatePart("y", rs.Fields("tasks.task_ass_date")) + 1
due = DatePart("y", rs.Fields("task_ddate"))

but it gives me a 1004 "application defined error."

Is this possible to do using DatePart?

Thanks

 

[Dave Peterson]

I don't speak the Access, but if I were doing this in plain old VBA, I'd do:

dim myDate as date
dim myDayOfYear as long
mydate = dateserial(2007, 4, 24)

mydayofyear = mydate - dateserial(year(mydate),1,0)
msgbox mydayofyear

=====
The 0th date of any month is the last day of the previous month.

I could have used this, too:
mydayofyear = mydate - dateserial(year(mydate)-1,12,31)

 

[Tom Ogilvy]

from the immediate window:

? DatePart("y",Date)
100

? datePart("y",DateValue("Feb 15, 2007"))
46

 

[Guest]

I assume tasks is the table name.. Try removing that when accessing the
recordset values. I.e.:

st = DatePart("y", rs.Fields("task_ass_date"))
nxt = DatePart("y", rs.Fields("task_ass_date")) + 1
due = DatePart("y", rs.Fields("task_ddate"))

 

[Guest]

Dave I think for what you are doing the day of the year if possiable will not
work
because you will run out of columns and will get that error

 

[Guest]

that's possible, Mike, but I'm not sure. i switched the rows with the
columns, so that the day of the year would go down the page instead of
across, and i get the same error.

Thanks for all the help so far. Apologies, should have told people that i'm
not just retrieving these date records, but pasting them into a kind of
calendar where each column corresponds to a day. as it's set up, it retrieves
the task assigned date as the day of the month, but i would like to have it
retreive the day of the year instead. instead of a monthly layout, i would
like a yearly layout.