Question How do I calculate a week number based on a field?

[shabbaranks]

Hi all..

Im trying to calculate a week number of a submitted report. Im guessing the way to go about this as I capture the date the user is referring to I can calculate which week number that date falls in? my question is how do I do it? I tried

=Format([TimesheetTableTemp]![Task Date],"ww")

where the task date is the location of the date but I get a prompt and then error.

Thanks

 

[jdraw]

Use datepart("ww",thedate)

 

[shabbaranks]

I get an error when i try this - the code is

=DatePart("ww",[TimesheetTableTemp]![Task Date])

Thanks

 

[vbaInet]

shabbaranks,

I'm sure I've mentioned this before, when you say it errors ALWAYS mention exactly what error message is.

 

[vbaInet]

That's fine but your first post doesn't mention what the error message is and your last post still doesn't mention what error message you get?

 

[shabbaranks]

I seem to be making a right lash up of this, the previous post I deleted as it wasnt relevant. So the problem with this is. If I have a text box which should display which week number the task date field is referring to as per this code

=DatePart("ww",[TimesheetTableTemp]![Task Date])

Access prompts me to Enter Parameter Value for TimesheetTableTemp and if I just press ok I get #Error. Obviously I dont want to be prompted I want it to work out which week number it is.

Thanks again - and again

 

[vbaInet]

Then write it like this:

=DatePart("ww", [Task Date])

So next time you get prompts just know that it means it can't find that field.

 

[shabbaranks]

Legend - worked a treat thanks. The same report has start date and an end date, I dont suppose you know how I output that to 2 text boxes do you?

Thanks again

 

[vbaInet]

Give us some more to go on. How is it setup?

 

[jonathanchye]

Legend - worked a treat thanks. The same report has start date and an end date, I dont suppose you know how I output that to 2 text boxes do you?

Thanks again

Set the Control Source of those two textboxes to the textbox containing this week number.

 

[shabbaranks]

A user completes a form based on 5 days. What I would like to output is the first date in the 5 days and the last day in the 5 days so I get the week begining and the week ending.

 

[vbaInet]

In that case you will need to get the Min() and Max() of those two dates then use DatePart() on them to get the ww.

 

[jonathanchye]

A user completes a form based on 5 days. What I would like to output is the first date in the 5 days and the last day in the 5 days so I get the week begining and the week ending.

Hmm, don't quite understand you here. Do you mean the user enters a date and you want to output the week starting and week ending dates?

Try something like this :

Dim tempDay as string
tempDay = Weekday(Me.txtUserDate)

Select Case tempDay
Case 1 ' Sunday
        Me.txtWeekBeginning = Me.txtUserDate + 1 'Assuming your week beginning falls on Monday
        Me.txtWeekEnding = Me.txtUserDate + 5
Case 2 ' Monday
         Me.txtWeekBeginning = Me.txtUserDate
Me.txtWeekEnding = Me.txtUserDate + 4
etc....

 

[vbaInet]

Hmm, don't quite understand you here. Do you mean the user enters a date and you want to output the week starting and week ending dates?

The user enters a couple of records ranging between 5 days (for example).

 

[jonathanchye]

The user enters a couple of records ranging between 5 days (for example).

Ah ok... in that case your solution will work better

 

[shabbaranks]

Im trying to use the expression builder to do this with no avail. Any chance of a hand please. Im clicking (for now)....

=Min ( [TimesheetTableTemp]![Task Date] )

shouldnt that show me the minimum date in the column?

Thanks and apologies for being a thicko

 

[vbaInet]

Still making the same mistake again!

 

[shabbaranks]

Do you mean not putting enough information? If thats it then ok, Im trying to create the discussed output of a minimum date from a set of data. I have tried using the expression builder to build my code as below

=Min ( [TimesheetTableTemp]![Task Date] )

But it says that the expression you entered is missing a closing parenthesis bracket but lookin at what I have for every [ I have a ].

Is this better?

 

[vbaInet]

Post #7.

 

[shabbaranks]

But Im not getting prompts??? Its complaining about parenthesis :s