change a number to positive value

[Peter Bailey]

I have an expression that returns the week number in a row but descending
from a date:

Week Number:
DateDiff("ww",DateAdd("ww",1,forms!FrmBookingCurve Input!TxtStart.value),[The
Date])

It allows me to pass values to a graph thats shows bookings over a 20 week
period.

because of the nature of the datediff it returns negative values. I would
like to see on the graph a positive.

How might I change that figure to a positve ? is there an easy function that
I can use in the query grid?

regards
Peter

[Allen Browne]

Abs() returns the absolute value of a number (i.e. ignores the sign).

Week Number:
Abs(DateDiff("ww",DateAdd("ww",1,forms!FrmBookingC urveInput!TxtStart.value),[The
Date]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cj**********@newsg3.svr.pol.co.uk...

I have an expression that returns the week number in a row but descending
from a date:

Week Number:
DateDiff("ww",DateAdd("ww",1,forms!FrmBookingCurve Input!TxtStart.value),[The
Date])

It allows me to pass values to a graph thats shows bookings over a 20 week
period.

because of the nature of the datediff it returns negative values. I would
like to see on the graph a positive.

How might I change that figure to a positve ? is there an easy function
that
I can use in the query grid?

regards
Peter

[Peter Bailey]

Thanks Allen that worked just a treat. One other question here is an example
of the row returned by the query that returns week numbers. What I would
like is it to ignore part of the week ie the last record has monday only in
week one.

Qrygetdailycountofdosmoduleenrollments Date Number of Module Enrollments
Day of the Week Week Number
14/04/2003 1 Monday 21
15/04/2003 0 Tuesday 21
16/04/2003 2 Wednesday 21
17/04/2003 0 Thursday 21
18/04/2003 0 Friday 21
21/04/2003 0 Monday 20
22/04/2003 0 Tuesday 20
23/04/2003 1 Wednesday 20
24/04/2003 0 Thursday 20
25/04/2003 1 Friday 20
28/04/2003 0 Monday 19
29/04/2003 0 Tuesday 19
30/04/2003 1 Wednesday 19
01/05/2003 2 Thursday 19
02/05/2003 2 Friday 19
05/05/2003 0 Monday 18
06/05/2003 0 Tuesday 18
07/05/2003 1 Wednesday 18
08/05/2003 2 Thursday 18
09/05/2003 0 Friday 18
12/05/2003 0 Monday 17
13/05/2003 1 Tuesday 17
14/05/2003 0 Wednesday 17
15/05/2003 2 Thursday 17
16/05/2003 0 Friday 17
19/05/2003 1 Monday 16
20/05/2003 1 Tuesday 16
21/05/2003 0 Wednesday 16
22/05/2003 1 Thursday 16
23/05/2003 0 Friday 16
26/05/2003 0 Monday 15
27/05/2003 0 Tuesday 15
28/05/2003 0 Wednesday 15
29/05/2003 1 Thursday 15
30/05/2003 0 Friday 15
02/06/2003 5 Monday 14
03/06/2003 0 Tuesday 14
04/06/2003 0 Wednesday 14
05/06/2003 2 Thursday 14
06/06/2003 0 Friday 14
09/06/2003 1 Monday 13
10/06/2003 3 Tuesday 13
11/06/2003 0 Wednesday 13
12/06/2003 0 Thursday 13
13/06/2003 0 Friday 13
16/06/2003 0 Monday 12
17/06/2003 0 Tuesday 12
18/06/2003 1 Wednesday 12
19/06/2003 0 Thursday 12
20/06/2003 2 Friday 12
23/06/2003 3 Monday 11
24/06/2003 2 Tuesday 11
25/06/2003 4 Wednesday 11
26/06/2003 0 Thursday 11
27/06/2003 2 Friday 11
30/06/2003 0 Monday 10
01/07/2003 2 Tuesday 10
02/07/2003 2 Wednesday 10
03/07/2003 4 Thursday 10
04/07/2003 0 Friday 10
07/07/2003 3 Monday 9
08/07/2003 3 Tuesday 9
09/07/2003 0 Wednesday 9
10/07/2003 2 Thursday 9
11/07/2003 5 Friday 9
14/07/2003 1 Monday 8
15/07/2003 3 Tuesday 8
16/07/2003 0 Wednesday 8
17/07/2003 1 Thursday 8
18/07/2003 5 Friday 8
21/07/2003 0 Monday 7
22/07/2003 1 Tuesday 7
23/07/2003 4 Wednesday 7
24/07/2003 3 Thursday 7
25/07/2003 9 Friday 7
28/07/2003 1 Monday 6
29/07/2003 5 Tuesday 6
30/07/2003 5 Wednesday 6
31/07/2003 0 Thursday 6
01/08/2003 1 Friday 6
04/08/2003 5 Monday 5
05/08/2003 3 Tuesday 5
06/08/2003 3 Wednesday 5
07/08/2003 2 Thursday 5
08/08/2003 0 Friday 5
11/08/2003 3 Monday 4
12/08/2003 2 Tuesday 4
13/08/2003 0 Wednesday 4
14/08/2003 3 Thursday 4
15/08/2003 1 Friday 4
18/08/2003 2 Monday 3
19/08/2003 1 Tuesday 3
20/08/2003 3 Wednesday 3
21/08/2003 0 Thursday 3
22/08/2003 4 Friday 3
25/08/2003 0 Monday 2
26/08/2003 2 Tuesday 2
27/08/2003 5 Wednesday 2
28/08/2003 1 Thursday 2
29/08/2003 1 Friday 2
01/09/2003 0 Monday 1
regards
Peter
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Abs() returns the absolute value of a number (i.e. ignores the sign).

Week Number:
Abs(DateDiff("ww",DateAdd("ww",1,forms!FrmBookingC urveInput!TxtStart.value),
[The Date]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cj**********@newsg3.svr.pol.co.uk...

I have an expression that returns the week number in a row but descending
from a date:

Week Number:
DateDiff("ww",DateAdd("ww",1,forms!FrmBookingCurve Input!TxtStart.value),[The Date])

It allows me to pass values to a graph thats shows bookings over a 20 week period.

because of the nature of the datediff it returns negative values. I would like to see on the graph a positive.

How might I change that figure to a positve ? is there an easy function
that
I can use in the query grid?

regards
Peter

[Allen Browne]

Whenever you work by "weeks", you have this kind of issue.

Not sure what result you are after, whether you want to use criteria to
filter out certain weeks, or use the 3rd argument of DatePart() to specify
what day should be considered the start of the week, or ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cj**********@news6.svr.pol.co.uk...

Thanks Allen that worked just a treat. One other question here is an
example
of the row returned by the query that returns week numbers. What I would
like is it to ignore part of the week ie the last record has monday only
in
week one.

Qrygetdailycountofdosmoduleenrollments Date Number of Module Enrollments
Day of the Week Week Number
14/04/2003 1 Monday 21
15/04/2003 0 Tuesday 21

[Peter Bailey]

I just discovered it I changed the "ww" to "w" and then filtered out <21 in
the grid for criteria.

Thanks Allen for replying so quickly, i was just about to post my solution.

youve helped me a lot over the past few weeks and the form is now looking
really professional albeit from me an unprofessional.

Regards

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Whenever you work by "weeks", you have this kind of issue.

Not sure what result you are after, whether you want to use criteria to
filter out certain weeks, or use the 3rd argument of DatePart() to specify
what day should be considered the start of the week, or ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cj**********@news6.svr.pol.co.uk...

Thanks Allen that worked just a treat. One other question here is an
example
of the row returned by the query that returns week numbers. What I would
like is it to ignore part of the week ie the last record has monday only
in
week one.

Qrygetdailycountofdosmoduleenrollments Date Number of Module Enrollments Day of the Week Week Number
14/04/2003 1 Monday 21
15/04/2003 0 Tuesday 21

[Bob Quintal]

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in
news:cj**********@newsg3.svr.pol.co.uk:

I have an expression that returns the week number in a row but
descending from a date:

Week Number:
DateDiff("ww",DateAdd("ww",1,forms!FrmBookingCurve Input!TxtStar
t.value),[The Date])

It allows me to pass values to a graph thats shows bookings
over a 20 week period.

because of the nature of the datediff it returns negative
values. I would like to see on the graph a positive.

How might I change that figure to a positve ? is there an easy
function that I can use in the query grid?

regards
Peter

Just exchange the positions of the two date expressions, e.g. put
[the date] before forms!Frm.....

--
Bob Quintal

PA is y I've altered my email address.