473,230 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

week format

Hi all,
I am using office XP and have a question regarding the format function.
I am using wk:format([pickandscratch].[schedule],"WW") to show what week a
certain date is. The problem is that a week runs from Sun to Sat in Access
and our warehouse week runs from Sat to Fri. This is making my "week to
date" calculations a day out. Is there a way that I can specify which day a
new week starts on within this query????
Many thanks in advance,

Mark
Nov 12 '05 #1
4 2532
On Sat, 1 May 2004 04:19:13 +0100, Mark Reed wrote:
Hi all,
I am using office XP and have a question regarding the format function.
I am using wk:format([pickandscratch].[schedule],"WW") to show what week a
certain date is. The problem is that a week runs from Sun to Sat in Access
and our warehouse week runs from Sat to Fri. This is making my "week to
date" calculations a day out. Is there a way that I can specify which day a
new week starts on within this query????

Many thanks in advance,

Mark


Look up the datepart() function in VBA help.

wk:DatePart("ww",[DateField],7)
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 12 '05 #2
Mark Reed wrote:
Hi all,
I am using office XP and have a question regarding the format function.
I am using wk:format([pickandscratch].[schedule],"WW") to show what week a
certain date is. The problem is that a week runs from Sun to Sat in Access
and our warehouse week runs from Sat to Fri. This is making my "week to
date" calculations a day out. Is there a way that I can specify which day a
new week starts on within this query????
Many thanks in advance,

Mark

If you put a , after "WW" the function will prompt you for the first day
of the week...Sunday is the default. Look at the Format function in
help. Use vbSunday...vbSaturday to specify the day.

You can even tell it various start dates for the first week on the year
if you add another comma.

Nov 12 '05 #3
Magic, works a treat!!!!

Thankyou very much
Mark
"fredg" <fg******@example.invalid> wrote in message
news:r3****************************@40tude.net...
On Sat, 1 May 2004 04:19:13 +0100, Mark Reed wrote:
Hi all,
I am using office XP and have a question regarding the format function. I am using wk:format([pickandscratch].[schedule],"WW") to show what week a certain date is. The problem is that a week runs from Sun to Sat in Access and our warehouse week runs from Sat to Fri. This is making my "week to
date" calculations a day out. Is there a way that I can specify which day a new week starts on within this query????

Many thanks in advance,

Mark


Look up the datepart() function in VBA help.

wk:DatePart("ww",[DateField],7)
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 12 '05 #4
Most date functions that support "week" also support your specifying the
"first day of the week"... for example, in recent versions, check help on
the Weekday function.

Larry Linson
Microsoft Access MVP

"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:wG***************@newsfe3-win.server.ntli.net...
Hi all,
I am using office XP and have a question regarding the format function. I am using wk:format([pickandscratch].[schedule],"WW") to show what week a
certain date is. The problem is that a week runs from Sun to Sat in Access
and our warehouse week runs from Sat to Fri. This is making my "week to
date" calculations a day out. Is there a way that I can specify which day a new week starts on within this query????
Many thanks in advance,

Mark

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Shuffs | last post by:
Could someone, anyone please tell me what I need to amend, to get this function to take Sunday as the first day of the week? I amended the Weekday parts to vbSunday (in my code, not the code...
4
by: Mark Reed | last post by:
Hi all, I am using office XP and have a question regarding the format function. I am using wk:format(.,"WW") to show what week a certain date is. The problem is that a week runs from Sun to Sat in...
9
by: Ray | last post by:
I need to convert the normal calendar to show the week no., the period no. and the financial year. The financial year format is as follows:- Date start: 2 May, 2005 7 days a week, 4 weeks a...
3
by: Mal | last post by:
Hello, Any advice on a function to convert a given week number to a date? Ideally I'd like the first day of that week. I'm using this to compare year to year....using the week number as the...
2
by: jnikle | last post by:
I'm stumped on this one. I have a report that shows production calculations for a user-selected period divided up into weeks. Currently, I just use format(,"ww") to get the calendar week for...
11
by: shsandeep | last post by:
I used the following query to retrieve the date in dd-mon-yyyy format. db2 => SELECT RTRIM(CHAR(DAY(COVG_TYP_STRT_DT))) || '-' || RTRIM(MONTHNAME(COVG_TYP_STRT_DT)) || '-' ||...
6
by: aarklon | last post by:
Hi folks, I found an algorithm for calculating the day of the week here:- http://www.faqs.org/faqs/calendars/faq/part1/index.html in the section titled 2.5 what day of the week was 2 august...
10
by: Jim | last post by:
I'm sure this has been asked before but I can't find any postings. I have a table that has weekly inspections for multiple buildings. What I need to do is break these down by the week of the...
1
by: Wayne | last post by:
I'm using the following expression in the query grid to show the Month and Year of the date field "TxtDate": Week: Format(,"ww yy",4) I am grouping the results on this field. Is there a...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.