Issues with Datepart function.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Untested -- Try using --
week: datepart("w",[rptDate])

It could be the leading zero is what made it think it was text.
 
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
 
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.


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

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

James D. said:
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.
Click to expand...
Click to expand...
 
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.

Tom Ellison said:
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


James D. said:
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.
Click to expand...
Click to expand...
 
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


James D. said:
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.

Tom Ellison said:
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


James D. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.


Tom Ellison said:
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


James D. said:
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.

Tom Ellison said:
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"
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top