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

avi wrote:
> 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

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

Look at the options for Format() in help.

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

> ? format(date,"ww",,vbFirstFullWeek)
> =A0 =A0 =A0 =A0 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 ?

avi wrote:
> 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 ?

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.

> Did you read the help topic? =A0

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 ... =3D 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 =3D Date
' Returns current system date in the system-defined long date format.
MyStr =3D 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....

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" <avi38521@gmail.com> wrote in message 
news:13a68da0-d65b-4566-a1aa-804b0d85e9da@e37g2000yqn.googlegroups.com...
> 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 


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

On Jan 5, 4:23=A0pm, "Michiel Rapati-Kekkonen" <no-m...@nonsense.zz>
wrote:
> I suppose that when you use
>
> =3DFormat(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!

avi wrote:
> 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!

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.

> So what's the value of MyStr? =A0I 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

explanation here:

http://www.xtremevbtalk.com/showthread.php?t=313044

http://support.microsoft.com/kb/q200299/

solution is provided by Sinndho here:

http://www.dbforums.com/microsoft-access/1652093-wrong-week-number-date-format.html#post6442635

well done
Interesting solution, too.

M


"avi" <avi38521@gmail.com> wrote in message 
news:856ad9f4-9944-422e-a5d4-d2fab2b16bc6@u7g2000yqm.googlegroups.com...
> solution is provided by Sinndho here:
>
> http://www.dbforums.com/microsoft-access/1652093-wrong-week-number-date-format.html#post6442635
> 


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