iif statement as a criteria

[SiGill]

I am trying to use an iif statement in a criteria of a query.

Basically I want to tell it that if the count of fields in a certain table is equal to a certain number then the criteria should be between 1 and 3 if not then the criteria should be between 1 and 4

is this possible?

 

[DCrake]

I know what you are asking but I don't understand your logic. Are there likley to be more than one table that it needs to look at? Surely if you know the table name you should know the number of fields that have been defined in it.

More info please.

 

[SiGill]

Hi David
Lets see if I can explain this.

Basically I am setting up a database to run a tournament. What I want to tell it to do is if the amount of entrants we get is, lets say 14, then insert 2 Byes to make it up to 16.

I have a list of Byes in a table labelled 1 to 8. So my query relates to that table. I want to tell it that if my entrants is 14 the my criteria should be "between 1 and 2" so 2 Byes append to my Player Entry table, if there are 13 entrants then criteria will be "between 1 and 3" so 3 byes are appended.

Hope this makes sense

 

[SiGill]

I have managed to find a work around which wors really well, I am using an iif statement in a form and then getting my query to use the result in the form.

Basically in my query I am saying this as the criteria.

Between 1 and [Forms]![Frm_Player Entry]![Count Players]

 

[DCrake]

You may be answer a question of mine. I run a local snooker league and have 4 divisions, each division has between 18 and 22 teams. What I want is to find an alorythmn that I can enter the number of participants and get it to throw back the fixture list working on the principle that there is a home and an away fixture. And no team can play itself. If you know of or have such an alorythmn then I would be pleased to look at it.