IIF Function Assistance

I have a very simple database that I just created with the following tables:

tblClient
ClientID
ClientFirstName
ClientLastName

tblCaseManagement
CaseMngID
ClientID
EmloyeID
Date
Time
Type
CaseMngNotes

tblCaseMngType
CaseMngTypeID
CaseMngType

tblEmployee
EmployeeID
EmployeeName

I am trying to create a simple report that shows the client name and the
dates they received case management. I'd like for it to show the clients
name at the top and in the detail section I'd like the Date, Employee, Time,
Type, and Notes. I have based my report on a query that includes fields
from tblClient, tblCaseManagement, and tblEmployee. It shows everything I
need except the Type is the CaseMngID. How can I get it to show the
CaseMngType instead of the CaseMngID? I think I need to create a text box
that contains an IIF statement but I'm not sure. Can anyone help me. I
just want it to show Office instead of 1 or Home instead of 2.

TIA
Steve

On Mon, 17 May 2004 15:59:58 -0700, Steve wrote:

I have a very simple database that I just created with the following tables:

tblClient
ClientID
ClientFirstName
ClientLastName

tblCaseManagement
CaseMngID
ClientID
EmloyeID
Date
Time
Type
CaseMngNotes

tblCaseMngType
CaseMngTypeID
CaseMngType

tblEmployee
EmployeeID
EmployeeName

I am trying to create a simple report that shows the client name and the
dates they received case management. I'd like for it to show the clients
name at the top and in the detail section I'd like the Date, Employee, Time,
Type, and Notes. I have based my report on a query that includes fields
from tblClient, tblCaseManagement, and tblEmployee. It shows everything I
need except the Type is the CaseMngID. How can I get it to show the
CaseMngType instead of the CaseMngID? I think I need to create a text box
that contains an IIF statement but I'm not sure. Can anyone help me. I
just want it to show Office instead of 1 or Home instead of 2.

TIA
Steve

Create a query that combines all the tables and fields you intend to
use in the report.
** Make sure the Relationships between the tables are correct. **
Then, instead of dragging the [CaseMngTypeID] field onto the grid (or
in addition to it), drag the [CaseMngType] field.

Now create your Report using this query as it's record source. Drag
the [CaseMngType] field onto the report. It will show the correct text
value instead of the number value of the [CaseMngTypeID].

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Everytime I try and add the tblCaseMngType to the query and then drag
CaseMngType to the grid I get error Type mismatch in Expression message.

CaseMngTypeID
CaseMngType"fredg" wrote in message
.. .
On Mon, 17 May 2004 15:59:58 -0700, Steve wrote:

I have a very simple database that I just created with the following
tables:

tblClient
ClientID
ClientFirstName
ClientLastName

tblCaseManagement
CaseMngID
ClientID
EmloyeID
Date
Time
Type
CaseMngNotes

tblCaseMngType
CaseMngTypeID
CaseMngType

tblEmployee
EmployeeID
EmployeeName

I am trying to create a simple report that shows the client name and the
dates they received case management. I'd like for it to show the
clients
name at the top and in the detail section I'd like the Date, Employee,
Time,
Type, and Notes. I have based my report on a query that includes fields
from tblClient, tblCaseManagement, and tblEmployee. It shows everything
I
need except the Type is the CaseMngID. How can I get it to show the
CaseMngType instead of the CaseMngID? I think I need to create a text
box
that contains an IIF statement but I'm not sure. Can anyone help me. I
just want it to show Office instead of 1 or Home instead of 2.

TIA
Steve

Create a query that combines all the tables and fields you intend to
use in the report.
** Make sure the Relationships between the tables are correct. **
Then, instead of dragging the [CaseMngTypeID] field onto the grid (or
in addition to it), drag the [CaseMngType] field.

Now create your Report using this query as it's record source. Drag
the [CaseMngType] field onto the report. It will show the correct text
value instead of the number value of the [CaseMngTypeID].

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

On Mon, 17 May 2004 17:08:04 -0700, "Steve"
wrote:

Everytime I try and add the tblCaseMngType to the query and then drag
CaseMngType to the grid I get error Type mismatch in Expression message.

I suspect you're yet another victim of the obnoxious Lookup Wizard
misfeature. Is CaseMngType a Lookup Field in your main table? If so -
that's your problem. It's trying to match the text Type to the numeric
TypeID. Your query should join the numeric TypeID to the numeric
TypeID.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thanks to everyone. My problem was different data types. I had the
CaseMngTypeID as a number and the FK Type as text.

"Steve" wrote in message
...
I have a very simple database that I just created with the following
tables:

tblClient
ClientID
ClientFirstName
ClientLastName

tblCaseManagement
CaseMngID
ClientID
EmloyeID
Date
Time
Type
CaseMngNotes

tblCaseMngType
CaseMngTypeID
CaseMngType

tblEmployee
EmployeeID
EmployeeName

I am trying to create a simple report that shows the client name and the
dates they received case management. I'd like for it to show the clients
name at the top and in the detail section I'd like the Date, Employee,
Time,
Type, and Notes. I have based my report on a query that includes fields
from tblClient, tblCaseManagement, and tblEmployee. It shows everything I
need except the Type is the CaseMngID. How can I get it to show the
CaseMngType instead of the CaseMngID? I think I need to create a text box
that contains an IIF statement but I'm not sure. Can anyone help me. I
just want it to show Office instead of 1 or Home instead of 2.

TIA
Steve