Parameter order

[jmriddic]

I built a query that in turn is used in a report. The users of the database would like the option to run the report either by name or employee number along with a date range. So I added the field for Employee number to the query using Where in the total row and placing [number:] in the or row. When I run the report it asks for the employee. It then asks for the date range. It then asks for the number. if I do not put in a name but do put in the number and the date range for employee I know is in the database it does return a query result. The problem is I need to order the parameter boxes in a way so it first brings up the name box, then the number box and then the boxes for the dates.

 

[KenHigg]

I know this is simplistic and may not work but can't you just move the columns in the query to the appropriate position, left to right?

 

[jmriddic]

I did that

I did that and saved the query. Ran it and it still did what I described. Went back in the query and it had reorder itself with the name,date range, and the number in that order. I am thinking it has something to do with that the number is on the "or" line.

 

[KenHigg]

You could do two queries. The first is simply a select with all of the fields you need and you put the criteria stuff there (in the appropriate order). Then build the totals query on that one. Kind of clunky but it should work...

Would that work?

 

[Banana]

Instead of using the default parameter box, use a form to manage the query then fill in parameter with VBA.

For an example, use an unbound form with three textbox labeled appropriately, then have a button with this code:

Docmd.OpenReport "NameOfReport", OpenArgs:=Name;Number;Date"

Then on report's event, use that OpenArgs to pass the parameter to the report's recordsource.

Search a bit on OpenArg to get idea of how this is implemented.

 

[jmriddic]

Example

Hi Banana,
Do you have an example of how to implement this from beginning to end?Would help expedite the development process. Also its a date range so would I not need two textboxes for that?