Passing criteria to parameter query

[Michelangelo]

Hi,

I want to create a parameter query for which the criteria are defined on the fly. To do this I've created a table called Parameters with several fields and only one record, and I use this one for all the queries. Then, depending on which query I want to use I fill the table with the correct values. The query has its fields with the criteria depending on a specific value in the table Parameters.
This works fine in case I have defined the corresponding Parameter field. However I want it to return all records if there is nothing in the Parameter field.

I've tried this one, in various variations

IIf(Len([Parameters]![EmployeeCode])>0,[Parameters]![EmployeeCode,"")

This works great if I supply a correct value in the Parameter table, when I leave it empty it returns no records.

Any ideas how to create a correct criteria???

M.

 

[pbaldy]

The structure would be:

[Parameters]![EmployeeCode] Or [Parameters]![EmployeeCode] Is Null

One warning, if this is a multiuser database. 2 people running reports at the same time can screw each other up, if this table is shared. Make it a local table in a FE/BE situation.

 

[Michelangelo]

I think I tried something like that, but couldn't get it to work. Solved it by using the criterium 'Like [Parameters].[EmployeeCode]

In the Parameter table I enter a *

Now I'm working on a way to enter a between criterium, so I can enter three criteria

< date
> date
between dates

I have two fields in the Parameter table. I one is * then the < or > should apply.

Indeed, I put the Parameter table in the FE, as all of the queries. Only the data-tables I keep in the BE.

M.