wrong week number in date format

avi

unread,

Jan 4, 2010, 9:47:13 AM1/4/10

to

Hello,

I have a problem within the form where I have date with default value
'Date()' which is formatted as week ('ww') showing week number.

It was ok for the previous year but now it reads date Jan, 4th as week
No 2 while I need it to be No 1.

Is there a way to fix this in form / format property only or some hard
coding is necessary?
Please help.
TIA

Salad

unread,

Jan 4, 2010, 10:55:22 AM1/4/10

to

? format(date,"ww",,vbFirstFullWeek)
returns 1

Look at the options for Format() in help.

avi

unread,

Jan 5, 2010, 4:15:20 AM1/5/10

to

On Jan 4, 5:55 pm, Salad <sa...@oilandvinegar.com> wrote:

> ? format(date,"ww",,vbFirstFullWeek)
> returns 1
> Look at the options for Format() in help.

I understand I input this into immediate window - right?
I have then the correct output BUT still it ain't clear to me:
how can I have that "Date()" value correctly shown (formatted) as "ww"
so that my DB reads date Jan, 5th as week No 1 instead of No 2 ?

Salad

unread,

Jan 5, 2010, 4:41:29 AM1/5/10

to

Did you read the help topic? Do you have Intellisense when you type out
the command...it provides the options.

? format(date,"ww",,vbFirstFullWeek)
returns 1

I recommend you experiment.

avi

unread,

Jan 5, 2010, 7:51:37 AM1/5/10

to

> Did you read the help topic?

1)
I have read Access HELP>using expression for Format() and it says
"You can use these expressions in calculated controls on forms,
reports, and data access pages.
Expression Description ... = Format(Date(), "dddd, mmm d
yyyy") ....You can use these expressions in a calculated field in a
query."

BUT WHAT I HAVE IS NOT CALCULATED FORM CONTROL as I understand but
just default today's "Date()" value.
-----------------------------
2)
VBA HELP shows example and I tried to adjust it acc your instructions:

"Dim MyDate, MyStr
MyDate = Date
' Returns current system date in the system-defined long date format.
MyStr = Format(MyDate, "ww", , vbFirstFullWeek)"

I used this code "on load" event for the form.
(Is this correct event anyway?)
but since default "Date()" value is formatted as WW the problem
persists.

That's why I am struggling with this....

Michiel Rapati-Kekkonen

unread,

Jan 5, 2010, 9:23:09 AM1/5/10

to

I suppose that when you use

=Format(Date();"ww";0;3)

as ControlSource for the field, you'll be a happy man.
You can leave the 'Format' and 'DefaultValue' of the field empty.

hope this helps

Michiel

"avi" <avi3...@gmail.com> wrote in message
news:13a68da0-d65b-4566...@e37g2000yqn.googlegroups.com...

--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---

avi

unread,

Jan 5, 2010, 9:52:39 AM1/5/10

to

On Jan 5, 4:23 pm, "Michiel Rapati-Kekkonen" <no-m...@nonsense.zz>
wrote:

> I suppose that when you use
>
> =Format(Date();"ww";0;3)
>
> as ControlSource for the field, you'll be a happy man.
> You can leave the 'Format' and 'DefaultValue' of the field empty.
>
> hope this helps
>
> Michiel

You're right Michiel ! Thanx a lot.
I was wandering around this.

Just one note:
seems that now it is not possible to check date field itself (just to
ensure weeks are ok) OR to edit 'Date()' by retyping/putting it in the
future
as opposed to initial method (my first Q). Meaning initially it would
display WW but when click into date field it would have switch to date
(in the past too).

Seems that for that purpose I'll need to introduce additional date
field too or you have some workaround for it too?

Thanx very much!

Salad

unread,

Jan 5, 2010, 12:04:45 PM1/5/10

to

You could, I suppose, use events and properties. For example, you could
have the Default property set to something like
=CalcWW()
(might not need the = sign) and it would calc the week by calling the
function CalcWW, maybe set the fields format property, and in the
dbl-click event call some sub that sets the field to today's date, maybe
even set the format to "mm/dd/yyyy" or whatever format you use if required.

You wrote: 2)

VBA HELP shows example and I tried to adjust it acc your instructions:

"Dim MyDate, MyStr
MyDate = Date
' Returns current system date in the system-defined long date format.
MyStr = Format(MyDate, "ww", , vbFirstFullWeek)"

I used this code "on load" event for the form.
(Is this correct event anyway?)
but since default "Date()" value is formatted as WW the problem
persists.

That's why I am struggling with this....

So what's the value of MyStr? I guess I don't understand your problem.

avi

unread,

Jan 6, 2010, 6:49:00 AM1/6/10

to

> So what's the value of MyStr? I guess I don't understand your problem.

Salad, this is from VBA HELP example ("have you read it?")

By applying Michiel's solution, everything is fine with this year's
week no.
However, week No for my past dates is wrong (?)
---------------------
loquin
Super Moderator

per a post at our sister-site (xtremevbtalk.com,)
Quote:
Originally Posted by Flyguy
The datepart and the format functions have a bug which has never been
fixed since VB5, same for VBA