iif([pet] in ("dog","cat"),text,Null) doesn't work ???

[Guest_imported]

Hi:

My table has a field I'll call "pet" which is populated via
a VALUE LIST. In my REPORT, I want to print the value if
it is a DOG or CAT, but print nothing (NULL) if it is one
of the other values in the value list.

Originally, the control in the report was a combo box,
and the report was not printing the field at all, no
matter what the value was. Then I changed it to a TEXT
BOX in the report, and have the following expression
set in the control source:

=iif([pet] in ("dog","cat&quot,[pet],Null)

My result in the report is #ERROR

I don't know if its just a SYNTAX problem, or if for
some reason you cant use a calculated cotrol or iif statement
when dealing with data that was in a value list in the table.

I have varied the syntax ("" instead of Null, etc) but it
looks like whenever the fieldname "pet" is used with the =
sign, it fails. Only "pet" alone WORKS.

I am clueless. Acces Help and the Access 200 Bible doesn't
say anything about this.

Help?

Thanks,
Lou

 

[AWithers]

you may want to try the full reference to the table

e.g. something like this

=iif([tablename]![pet] in("dog","cat&quot,([tablename]![pet],"&quot

Andy

 

[sko]

If you want to restrict your report to Dogs & Cats, you should base your report on a query and place your restrictions there.

In query design view:

Field: Pet
Criteria: "Dog" or "Cat"

Create your report using the query as your Data Source.

It sounds like you created the list box (or combo box)in the design view of your table. When you do this, the field in your report (or form) will automatically be a list box (or combo box)control. I'm guessing that's not what you want in your report. In report design view, right-click on the control then select > change to > text box


Hope that helps

 

[Guest_imported]

Thanks, Sko.

My report is to have all records, but I only want to print "dog" or "Cat" next to a person's name if the pet is one of those two.

So, I can't write a query to select fewer records based on pet for this list.

This is actually a test database I am trying things with. The REAL database is a CLUB MEMBERSHIP database. Each club member has a ROLE: regular member, president, secretary, etc. I only want to print the ROLE next to a name if that role is one of the OFFICERS.

This is simply a report to print the club roster.

So, I want:
--------------------------------
Bob Smith
123 Main St.


Fred Jones PRESIDENT
234 East St.

--------------------------------


Right now, I get:


--------------------------------
Bob Smith #ERROR
123 Main St.

Fred Jones #ERROR
234 East St.
--------------------------------

Actually, what I'd LIKE to do is BOLDFACE the entire record of an officer in the report. Fred Jones' entire name, address, etc would be bold, AND have his Role next to his name.

Can't figure it out.


And you are right: I DID create the combo box in design view of the table. BUT, I also already changed it to TEXT BOX in the report design view. Now I am getting those #ERRORs.

Argh!


Regards,
Lou

 

[hmm2]

Lou,

I'm thinking of two options. The simplest I can think of is to use the VISIBLE property setting.

In your report, go to: Detail Section, Properties, Events, OnFormat. Build code for the OnFormat event as follows:

Select Case Me.Role
    Case "President", "Vice President"
        Me.Role.Visible = True
    Case Else
        Me.Role.Visible = False
End Select

Me.Role is referring to your field on the report containing the "Role" data. You could also use If/Then, but to me it's easier to use (and read) the Select statement when you have multiple cases.

See if that works. If not, I've got a couple more possible solutions.

hmm

 

[hmm2]

Lou,

I missed your last entry about bolding the record...

You can do that within that same Select statement, as follows:

Select Case Me.Role
    Case "President", "Vice President"
        Me.Role.Visible = True
        Me.Role.FontBold = True
        Me.MemberNameField.FontBold = True
    Case Else
        Me.Role.Visible = False
        Me.Role.FontBold = False
        Me.MemberNameField.FontBold = False
End Select

Of course, you'd have to list each of the report's detail records' fields in the Select Statement so that each one will/won't be bold. There may be another way around it, but I ain't there yet.

Hope that helps.

hmm