Setting Week 1 in SQL

[Guest]

Hi all,

Alright, I have a bit of a problem, and I'm not quite sure of how to solve it.

I have a SELECT query that retrieves data that is between a certain date
range. I've been able to right code to get the start and end date of a range
that is given in weeks.

My problem is, I send that data to Excel, and the query I've written to
extract week from the date is slightly wrong. I had (I believe) the same
issue with the code in Access. That is, what is considered the start of the
year. Access defaults to where Jan 1 falls, and I wanted it where a majority
of days of the first month fall. So I used the following:

Do
weekNum = DatePart("ww", startDate, vbSunday, vbFirstFourDays)
If weekNum = fromWeek Then
flagDateFound = True
Else
startDate = DateAdd("d", 1, startDate)
End If
Loop Until flagDateFound = True

Now, I used the following in my query to send the data to Excel:
Format(DatePart(" & Chr(34) & "ww" & Chr(34) & ",DTL_DATE)," & Chr(34) &
"00" & Chr(34) & ")

Is there a way to set the first week for running queries? My weeks look off
right now, and I'd like to fix it.

The best example I can use is January of 2005. The 1st falls on a Sat, but
my 'week' is from Sun - Sat, so I wanted week 1 to be Jan 2-8, 2005. The
code I wrote works fine for that case, but when using the Format in my query,
it doesn't work as I'd like.

If anyone has any suggestions, I'd appreciate it. If I need to explain
something further, let me know. I think I made sense.

Thanks,
Jay

 

[Douglas J Steele]

You can use the DatePart function in your query. You just can't pass it
named constants like vbSunday and vbFirstFourDays. Instead, use the values
for those constants (1 and 2 respectively):

DatePart("ww", [MyDateField], 1, 2)

 

[Guest]

Works like a charm.

Thanks.

You can use the DatePart function in your query. You just can't pass it
named constants like vbSunday and vbFirstFourDays. Instead, use the values
for those constants (1 and 2 respectively):

DatePart("ww", [MyDateField], 1, 2)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

Alright, I have a bit of a problem, and I'm not quite sure of how to solve it.

I have a SELECT query that retrieves data that is between a certain date
range. I've been able to right code to get the start and end date of a range
that is given in weeks.

My problem is, I send that data to Excel, and the query I've written to
extract week from the date is slightly wrong. I had (I believe) the same
issue with the code in Access. That is, what is considered the start of the
year. Access defaults to where Jan 1 falls, and I wanted it where a majority
of days of the first month fall. So I used the following:

Do
weekNum = DatePart("ww", startDate, vbSunday, vbFirstFourDays)
If weekNum = fromWeek Then
flagDateFound = True
Else
startDate = DateAdd("d", 1, startDate)
End If
Loop Until flagDateFound = True

Now, I used the following in my query to send the data to Excel:
Format(DatePart(" & Chr(34) & "ww" & Chr(34) & ",DTL_DATE)," & Chr(34) &
"00" & Chr(34) & ")

Is there a way to set the first week for running queries? My weeks look off
right now, and I'd like to fix it.

The best example I can use is January of 2005. The 1st falls on a Sat, but
my 'week' is from Sun - Sat, so I wanted week 1 to be Jan 2-8, 2005. The
code I wrote works fine for that case, but when using the Format in my query,
it doesn't work as I'd like.

If anyone has any suggestions, I'd appreciate it. If I need to explain
something further, let me know. I think I made sense.

Thanks,
Jay