datepart error

[Jean-Paul]

Hi...

I the property I wrote:
=DatePart("ww";[Date()])
this returned a #name error
What is wrong with this code?

JP

 

[Allen Browne]

Remove the square brackets.
=DatePart("ww", Date())
With the square brackets, Access is looking for a field with that name,
whereas Date() is a VBA function.

Depending on the context, you may need to use a comma as the list separator.

 

[Jean-Paul]

Indeed, this solves the problem...

Remains another one...

I have a form with a number-value (representing the weeknumber)
In the onopen property of the form I wrote:

Me!weeknummer=DatePart("ww",date())

this indeed returns the actual weeknumber... so far everything is correct.

On this form, I have a subform.

In this subform I display all visited firms planned in that week
(=me!weeknummer)

When the form is opened, no data is displayed in this subform.
When I change the value in the weeknumber, I get the correct data, even
for the current week.

How to solve this problem?
Thanks

 

[Allen Browne]

I didn't follow that.

If this is in the ControlSource of a text box, drop the brackets and the
Me!. Try:
=[weeknumber]

Or perhaps you could just put this in the Control Source of the text box:
=DatePart("ww",date())

 

[Jean-Paul]

No, no, the weekday stuff doesn't pose any problem, except

I set the default value of an entryfield to the current weeknumber.
The same form has a subform displaying all firms that need to be visited
in that week.

Upon opening, this subform is empty,

When I change the weeknumber and finally get back o the current week,
all firms are displayed as they should in this subform.

The subform recordset is based upon following sql:

SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].Datum_bezoek, [Adressen metaal].bezoeken
FROM [Adressen metaal]
WHERE ((([Adressen metaal].bezoeken)=True) AND
(([forms].[bezochten].[weeknummer])=DatePart("ww",[Datum_bezoek])))
ORDER BY [Adressen metaal].Naam;

It doesn't seem to be triggered upon opening the form...

Does this all make some sence?
Thanks
JP

 

[Allen Browne]

The problem could be to to with timing or data types.

In query design, choose Parameters on the Query menu.
Access pops up the parameter dialog. Type in a line:
[forms].[bezochten].[weeknummer] Long Integer
Also, set the Format property of the weeknummer text box to General Number
(or something that tells it that it's a number.)

Of perhaps just pressing F9 (to reload the subform) might help.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

No, no, the weekday stuff doesn't pose any problem, except

I set the default value of an entryfield to the current weeknumber.
The same form has a subform displaying all firms that need to be visited
in that week.

Upon opening, this subform is empty,

When I change the weeknumber and finally get back o the current week, all
firms are displayed as they should in this subform.

The subform recordset is based upon following sql:

SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].Datum_bezoek, [Adressen metaal].bezoeken
FROM [Adressen metaal]
WHERE ((([Adressen metaal].bezoeken)=True) AND
(([forms].[bezochten].[weeknummer])=DatePart("ww",[Datum_bezoek])))
ORDER BY [Adressen metaal].Naam;

It doesn't seem to be triggered upon opening the form...

Does this all make some sence?
Thanks
JP

I didn't follow that.

If this is in the ControlSource of a text box, drop the brackets and the
Me!. Try:
=[weeknumber]

Or perhaps you could just put this in the Control Source of the text box:
=DatePart("ww",date())