IIF Function Assistance

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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
Click to expand...

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].
 
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" <[email protected]> wrote in message
Click to expand...
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
Click to expand...

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].
Click to expand...
 
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.
Click to expand...

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.
 
Thanks to everyone. My problem was different data types. I had the
CaseMngTypeID as a number and the FK Type as text.
 
Back
Top