Weeknumber with leading zero and year

[Guest]

In a tabel I have a field 'DueDate' the format is Date/Time and the
yyyymmdd.

Now I want to convert this in anorther field where I have the Year and Week
in the format yyyyww, but how can I get a leading zero in the weeknumber ??

My result is: 20057 where I want 200507.

Help would be very much appriciated.

OrdrupAB
Denmark

 

[Ken Snell [MVP]]

How are you converting? Are you running an update query to put this value
into that new field? Or are you wanting to display the "calculted" result to
the user?

Tell us what you're doing and show us what you're using. Essentially, you
need to concatenate the two parts of the result (the yyyy and the ww parts)
as strings, using the Format function to specify the number of "places" to
use:

yyyyww = Format(DateField, "yyyy") & Format(Format(DateField, "ww"), "00")

 

[Guest]

I have a field: DTDUDT in a table, Type = Text 8 char. (The datainput comes
from an AppendQuery linked to AS400, a the data is a date f.x. 20050218)

With another AppendQuery I take the data an 'transform' it using this:

DueDate: DateSerial(Left([DTDUDT];4);Mid([DTDUDT];5;2);Right([DTDUDT];2))
the format in the query is yyyy-ww, in the table/field I append to, is the
format Date/Time yyyy-ww.

I have been reading in the groups for about 3 hours and some places they
show ways to put a leading zero in the week.

In the end I need to summarize the amount due, where I need the Year anf
Weeknumber.

Thanks in advance
OrdrupAB
Denmark



"Ken Snell [MVP]" skrev:

 

[Ken Snell [MVP]]

To store the yyyyww data exactly as that, I would use a text field, not a
date/time field. Date/time values are stored as floating point numbers
(integer portion is the date, fractional decimal portion is the time). The
format you put on the field has no effect on how the data are stored.

However, if you want to display a date/time field's value in your "yyyyww"
format in a query, use a calculated field such as this:

MyYWValue: Format(DueDate, "yyyy") & Format(Format(DueDate, "ww"), "00")

--

Ken Snell
<MS ACCESS MVP>

I have a field: DTDUDT in a table, Type = Text 8 char. (The datainput
comes
from an AppendQuery linked to AS400, a the data is a date f.x. 20050218)

With another AppendQuery I take the data an 'transform' it using this:

DueDate: DateSerial(Left([DTDUDT];4);Mid([DTDUDT];5;2);Right([DTDUDT];2))
the format in the query is yyyy-ww, in the table/field I append to, is
the
format Date/Time yyyy-ww.

I have been reading in the groups for about 3 hours and some places they
show ways to put a leading zero in the week.

In the end I need to summarize the amount due, where I need the Year anf
Weeknumber.

Thanks in advance
OrdrupAB
Denmark



"Ken Snell [MVP]" skrev:

How are you converting? Are you running an update query to put this value
into that new field? Or are you wanting to display the "calculted" result
to
the user?

Tell us what you're doing and show us what you're using. Essentially, you
need to concatenate the two parts of the result (the yyyy and the ww
parts)
as strings, using the Format function to specify the number of "places"
to
use:

yyyyww = Format(DateField, "yyyy") & Format(Format(DateField, "ww"),
"00")

 

[Guest]

Hej Ken

First THANKS - that solved several problems - it work all the way!!

A little extra question: Now I need to sum outstanding invoices where
weeknum (as you just tought me) is less than 200453 - another sum where
weeknum equel 200501 and a sum where weeknum > than 200501.
I know I can do it by making 3 queries and then put those 3 in a fourth
query to get the results in one row - BUT isn't there an easyer wey to do
this ??

Regards Odrup AB





"Ken Snell [MVP]" skrev:

To store the yyyyww data exactly as that, I would use a text field, not a
date/time field. Date/time values are stored as floating point numbers
(integer portion is the date, fractional decimal portion is the time). The
format you put on the field has no effect on how the data are stored.

However, if you want to display a date/time field's value in your "yyyyww"
format in a query, use a calculated field such as this:

MyYWValue: Format(DueDate, "yyyy") & Format(Format(DueDate, "ww"), "00")

--

Ken Snell
<MS ACCESS MVP>

I have a field: DTDUDT in a table, Type = Text 8 char. (The datainput
comes
from an AppendQuery linked to AS400, a the data is a date f.x. 20050218)

With another AppendQuery I take the data an 'transform' it using this:

DueDate: DateSerial(Left([DTDUDT];4);Mid([DTDUDT];5;2);Right([DTDUDT];2))
the format in the query is yyyy-ww, in the table/field I append to, is
the
format Date/Time yyyy-ww.

I have been reading in the groups for about 3 hours and some places they
show ways to put a leading zero in the week.

In the end I need to summarize the amount due, where I need the Year anf
Weeknumber.

Thanks in advance
OrdrupAB
Denmark



"Ken Snell [MVP]" skrev:

How are you converting? Are you running an update query to put this value
into that new field? Or are you wanting to display the "calculted" result
to
the user?

Tell us what you're doing and show us what you're using. Essentially, you
need to concatenate the two parts of the result (the yyyy and the ww
parts)
as strings, using the Format function to specify the number of "places"
to
use:

yyyyww = Format(DateField, "yyyy") & Format(Format(DateField, "ww"),
"00")

--

Ken Snell
<MS ACCESS MVP>


In a tabel I have a field 'DueDate' the format is Date/Time and
the
yyyymmdd.

Now I want to convert this in anorther field where I have the Year and
Week
in the format yyyyww, but how can I get a leading zero in the
weeknumber
??

My result is: 20057 where I want 200507.

Help would be very much appriciated.

OrdrupAB
Denmark

 

[Ken Snell [MVP]]

An easier way? Any other way will still involve running 3 separate queries
and using the results of those queries in one query. In terms of speed,
creating three separate ones and using them as the source tables for the
fourth query is likely going to be the fastest.

Other methods:

(1) Use DSum domain function in a calculated field in the query to
calculate the sum for a specific condition.

(2) Use subquery as calculated fields in the query to calculate the sum
for a specific condition. In this case, you're essentially just putting the
first, second, and third queries into their respective SQL statements and
using them directly in the fourth query.

Personally, I would stay with what you're doing now.
--

Ken Snell
<MS ACCESS MVP>

Hej Ken

First THANKS - that solved several problems - it work all the way!!

A little extra question: Now I need to sum outstanding invoices where
weeknum (as you just tought me) is less than 200453 - another sum where
weeknum equel 200501 and a sum where weeknum > than 200501.
I know I can do it by making 3 queries and then put those 3 in a fourth
query to get the results in one row - BUT isn't there an easyer wey to
do
this ??

Regards Odrup AB





"Ken Snell [MVP]" skrev:

To store the yyyyww data exactly as that, I would use a text field, not a
date/time field. Date/time values are stored as floating point numbers
(integer portion is the date, fractional decimal portion is the time).
The
format you put on the field has no effect on how the data are stored.

However, if you want to display a date/time field's value in your
"yyyyww"
format in a query, use a calculated field such as this:

MyYWValue: Format(DueDate, "yyyy") & Format(Format(DueDate, "ww"), "00")

--

Ken Snell
<MS ACCESS MVP>

I have a field: DTDUDT in a table, Type = Text 8 char. (The datainput
comes
from an AppendQuery linked to AS400, a the data is a date f.x.
20050218)

With another AppendQuery I take the data an 'transform' it using this:

DueDate:
DateSerial(Left([DTDUDT];4);Mid([DTDUDT];5;2);Right([DTDUDT];2))
the format in the query is yyyy-ww, in the table/field I append to,
is
the
format Date/Time yyyy-ww.

I have been reading in the groups for about 3 hours and some places
they
show ways to put a leading zero in the week.

In the end I need to summarize the amount due, where I need the Year
anf
Weeknumber.

Thanks in advance
OrdrupAB
Denmark



"Ken Snell [MVP]" skrev:

How are you converting? Are you running an update query to put this
value
into that new field? Or are you wanting to display the "calculted"
result
to
the user?

Tell us what you're doing and show us what you're using. Essentially,
you
need to concatenate the two parts of the result (the yyyy and the ww
parts)
as strings, using the Format function to specify the number of
"places"
to
use:

yyyyww = Format(DateField, "yyyy") & Format(Format(DateField, "ww"),
"00")

--

Ken Snell
<MS ACCESS MVP>


In a tabel I have a field 'DueDate' the format is Date/Time and
the
yyyymmdd.

Now I want to convert this in anorther field where I have the Year
and
Week
in the format yyyyww, but how can I get a leading zero in the
weeknumber
??

My result is: 20057 where I want 200507.

Help would be very much appriciated.

OrdrupAB
Denmark