IIF statement in a query

[Guest]

I have a table with two text fields named "NOV" and "DEC". I have two
records in my table. Record 1 has a "P" in NOV and DEC. Record 2 has a "K"
NOV and nothing in DEC. In my query under criteria of DEC, I am typing:
IIF(Month(Now())=9, is not null or is null, is not null)

Because it is currently September, I want the query to return both records.
I can make this query work with NOV and DEC are yes/no fields, but I can't
make it work when they are text fields.

Can anyone help?

Heidi

 

[Guest]

If you have a field for each month of the year in the table, your data is not
normalized properly. You need a another table with a foriegn key to link to
the main table then another field where you can put in the month. For example
you could have a Reports table that would look something like:

Report_PK ReportName
1 P
2 K
etc.

Then you'd have a ReportsDue table.

ReportDue_PK Report_FK DueMonth
1 1 11
2 1 12
3 2 11

The SQL for the query would look something like:

SELECT reports.ReportName, ReportsDue.DueMonth
FROM Reports, ReportsDue
WHERE Reports.Report_PK = ReportsDue.Report_FK
AND ReportsDue.DueMonth = Month(Now()) ;


???? Because it is currently September, I want the query to return both
records.

Now I'm confused! You want NOV and DEC returned in September? What do you
want returned if it is NOT September????

 

[Rob]

If all you really want to do is what you describe, then you can use an
OR condition.
On the design view, put "Is not Null" in the criteria row for the DEC
field column, and add a new field column for Month(Now), and for it, a
"=9" in a different criteria row.

In SQL view, it's
WHERE (Month(Now())=9) OR (tbl.DEC Is Not Null);

My guess however is that you are really trying to do something more
than what you described....


Rob