Can you use multiple weeks in DatePart

[ptaylor-west]

I'm using the following in a query qwhich allows me to enter the week number as the criteria:

DatePart('ww',[Visitdate],2,2)

This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.

 

[KenHigg]

DatePart('ww',[Visitdate],2,2) or DatePart('ww',[Visitdate],2,2) or DatePart('ww',[Visitdate],2,2)



???

 

[ptaylor-west]

Thought of that but it doesn't work, perhaps I should show you the query as per the attached screen shot - what I want to do is enter more than one week number when prompted by the criteria.

 

[KenHigg]

I've never done it but could you use something like:

in([Enter Week Number:1,2,3,.. not 01,02,03,...])

???

 

[ptaylor-west]

Tried that as well, but no go.

 

[Jon K]

Try this in a column in the query grid:-

Field: InStr("," & [Enter week number: 1,2,3,4 etc NOT 01,02] & ",", "," & DatePart('ww',[Visitdate],2,2) & ",")

Show: uncheck

Criteria: True
.

 

[ptaylor-west]

Absolutely spot on, however this presents a problem with the report as the heading uses the week numbers (previously entered in criteria) to identify the weeks the report refers to ie: ="Report for week : [Enter week number: 1,2,3,4 etc NOT 01,02]

I have treid chasnging it to the string but it doesn't work, do you know what I need to put in to make it work.

 

[Jon K]

You can use two text boxes on a form for the user to enter the parameters.

In the query (and the report heading), you can replace [Enter week number: 1,2,3,4 etc NOT 01,02] and the other parameter with an expression like this:-
[Forms]![FormName]![TextBoxName]

Then you can run the report from a command button on the form with the code:-
DoCmd.OpenReport "ReportName", acViewPreview


Using a form for the user to type the parameters is considered more user-friendly.
.

 

[ptaylor-west]

Thank you, that was all extremely helpful

 

[ptaylor-west]

The only thing with the last solution is that it involves an extra action by the user - currently the report is generated from the Switchboard - whereas this method means opening the form and then hitting the command button, it also shows the criteria boxes along the way.

Is there an alternative, if not what you have given me is still OK.

 

[Mile-O]

SELECT DatePart("ww",[Visitdate],2,2) As MyField
FROM MyTable
WHERE DatePart("ww",[Visitdate],2,2) In (14, 16, 21)
ORDER BY MyField;

Or am I missing something?

 

[ptaylor-west]

Now I'm lost!

 

[ScottGem]

I don't like (or use) parameter queries. I find them very limiting and this is an example. There really isn't an extra action here if its done right.

First, I would use a Multi-select Listbox to allow the selection of multiple weeks. You can find an example at http://www.mvps.org/access/forms/frm0007.htm in using such a listbox to supply criteria to a query.

You then use the After Update event of the control to kick off the report. In that way there is no real difference to the user between entering data into a parameter box or selecting values in a listbox. Once they hit Enter to finish the selection the report runs.