IIF Statements as criteria for Query with False value returning all records

[EmilyL]

I have a database I'm creating to track sheep breeding records. My end goal is to be able to pull up a form which will list all of the offspring of a particular buck or ewe. I would like to be able to get it so that the same query and form will pull the results of either a buck or a ewe. The columns that I'm including my criteria for are the lamb's records which have a columns for the ewe (mother) and the buck (father) which are both saved as text rather than numbers. The SheepRecord form is the form for either the buck or the ewe from which i want to open the Offspring form.
My current criteria in the ewe column is:
IIf([Forms]![SheepRecord]![Sex]="Female",[Forms]![SheepRecord]![Tag number], Is Not Null)

The formula works if the sheep is a ewe, but returns no records if the sheep is a buck and i need this particular criteria to then return all records. I think I understand the postings about why Is Not Null doesn't work and if this was the only criteria for the query could get it to work, but i also need a similar formula for the Buck column as well. Any step by step instructions would be helpful (i'm an access beginner with basically no programming knowledge). Thanks for any help that can be given.

[Stewart Ross]

Hmm. I suspect that underneath it all the design of your tables may need attention, given the self-reflexive nature of the relationship between a lamb and its mother or father, and what you mention about having a ewe column.

A sheep is either a male or a female, which is a value of a property such as 'gender' - it is not two properties implemented as separate columns ('Ewe' and 'Buck').

I don't know enough about your application to say how you would implement the other properties relating to identifying which male has sired which offspring, and which females were their mothers, but I reckon if we saw your current table designs there would be some compromises in the implementation which are leading to the use of IIFs in the way you describe.

Anyhow, if I understand your requirement properly you want to filter the records to match a particular tag number if the sheep concerned is a female, and if not to return all records.

Is the tag number property implemented as a number? If it is it makes it a little difficult to implement the 'return all' part of your requirement. The simplest way I can think of to do it would be to insert a calculated field into your query which compares the tag number as text using the 'Like' operator, instead of having the IIF directly on your Ewe field.

Insert a new column into your query. In the name part of the field type the following:

EweTag: Ewe

which is naming a new field called EweTag to be the same base field as your current one named Ewe.

In the criteria column add this:

Like IIf([Forms]![SheepRecord]![Sex]="Female",[Forms]![SheepRecord]![Tag number], "*")

The IIF is returning either the tag number or the wildcard character "*". The Like operator, which is used for text values normally, is then either working on a tag number or on the wildcard, matching all values:

Like "12345", say, or
Like "*"

You will need to remove the existing IIF from the ewe column of course.

You may find the following article on database normalisation and table structures useful for the future.

-Stewart