Issues with Datepart function.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James D. - 25 Apr 2006 15:43 GMT

Hi I have a query in Access 2003 that I am working on that is designed to
bring up all records between week 1 and a user entered paramater week"
between 1 and [please enter end week]".

Week is derived by usings the following statment in design view of Access's
query builder in a blank field of the above query" week:
datepart("ww",[rptDate])".

Unfortunatly it looks like the returned value is is string or variant format
as when I run it between between 1 and 8 it bring up weeks like 34, 44 in
addition to what I was looking for.

I since have created a table from a make table query in which the "week"
field is now of number data type, ran the above query on it and it works
great, only pulling the info I need from the range.

I then change the week formula to the following on the original query "
Cdbl(datepart("ww",[rptdate])" thinking that would help. Unfortunatly it
still acts like a string.

Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message

KARL DEWEY - 25 Apr 2006 15:55 GMT

Untested -- Try using --
week: datepart("w",[rptDate])

It could be the leading zero is what made it think it was text.

> Hi I have a query in Access 2003 that I am working on that is designed to
> bring up all records between week 1 and a user entered paramater week"
[quoted text clipped - 17 lines]
>
> Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message

John Spencer - 25 Apr 2006 17:14 GMT

Comment:

DatePart("w", Date()) returns a number from 1 to 7 representing the day of
the week (Sunday to Saturday)
DatePart("ww", Date() returns a number from 1 to 53 representing the week of
the year.

As to the original problem, I would try something like the following.

Field: WeekNum: DatePart("ww",[rptDate])
Criteria: Between 1 and CLng([please enter end week])

If that fails, make sure that rptDate is a datetime field. If it is text,
then either convert it or use the DateValue function in the query to change
the string to a date.

> Untested -- Try using --
> week: datepart("w",[rptDate])
[quoted text clipped - 24 lines]
>>
>> Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message

Tom Ellison - 25 Apr 2006 15:55 GMT

Dear James:

I would suggest that it is not the DatePart() value that is text, but the
thing to which you're comparing it that is text. You do not supply the code
for that side. Please try:

DatePart("ww", repDate) BETWEEN 1 AND CInt([please enter end week])

or something similar.

Tom Ellison

> Hi I have a query in Access 2003 that I am working on that is designed to
> bring up all records between week 1 and a user entered paramater week"
[quoted text clipped - 19 lines]
>
> Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message

James D. - 27 Apr 2006 20:57 GMT

Perfect!! I finished testing and you are correct. The paramater value when
entered is evaluated as a string expression and needs to be casted to the
proper datatype.

Good call on the soltn as I never would have seen it, and thanks for your
help.

On a related question: Now try not to laugh, but can anyone explain to me
why there are 53 possible weeks in a year? Any date that I have past Dec 25th
2004 comes up as week 53. Any explanation would be great.

Thanks.

James D.

> Dear James:
>
[quoted text clipped - 31 lines]
> >
> > Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message

Tom Ellison - 27 Apr 2006 22:19 GMT

Dear James:

1/3/2004 shows as the last day of the first week of the year. 1/4/2004 is
the first day of the second week, and every 7 days is another week from
there forward. There are 366 days in 2004, which is 52 weeks plus 2 days.
So, the last few days of the year must be the 53rd week. Make sense? The
year usually starts with only a partial first week.

Tom Ellison

> Perfect!! I finished testing and you are correct. The paramater value
> when
[quoted text clipped - 54 lines]
>> >
>> > Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message

John Spencer - 28 Apr 2006 12:37 GMT

Dear James,

In addition to what Tom has posted, you might want to look up the help on
DatePart. There are optional arguments to the function to determine which
week is the first week of the year (first full week; week with Jan 1 in it;
first week with at least 4 days) and which day of the week is the first day
of the week. You will still end up with 53 weeks in the year since 365/7
always is more than 52.

> Dear James:
>
[quoted text clipped - 68 lines]
>>> >
>>> > Any suggestions would be awesome as I am out of Ideas at this point.

Reply to this Message