Access Query IIF statement with multiple conditions 3

[cimoli]

I have a query criteria combo box to use for choosing a Trip.

All works well except for one condition. I would like cboResTypeID 19 to use the trips
for codes 14 or 16. I get nothing if i use code 19.

IIf([Forms]![frmReservation]![cboResTypeID]=19,([tblTravelMain].[ResTypeID])=14 Or ([tblTravelMain].[ResTypeID])=16,[Forms]![frmReservation]![cboResTypeID])

ResTypeID is a number field.

Thanks you for your help. Glen.

 

[cimoli]

This is the part that gives me trouble.

([tblTravelMain].[ResTypeID])=14 Or ([tblTravelMain].[ResTypeID])=16,

 

[SkipVought]

Please write out exactly what your logic is like instead of trying to use code,
like...

[tt]
If cboResTypeID = 19 Then
what do you want to happen?

[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[cimoli]

Thanks Skip. I have combo field cboResTypeID on the main form called frmReservation.

I might select code 19 for the reservation type
Below this cboResTypeid field, I have a combo field called cboTripID on this same frmReservation.

I hit the pull down button on this cboTripID. I move my mouse to find the desired trip.
(ps i use a year and month to narrow the trips but that is OK.)

The cboRestypeID picked earlier is used on the criteria line of cboTripID.

Inside the cboTripID, on the row source line inside, I have the criteria to find
the desired trip.

IIf([Forms]![frmReservation]![cboResTypeID]=19,([tblTravelMain].[ResTypeID])=14 Or ([tblTravelMain].[ResTypeID])=16,[Forms]![frmReservation]![cboResTypeID])

RestypeID code 19 is the oddball. He has no trips assigned. We handle his trips differently.
But he will arrive at the same Hotel as the code 14 or 16 persons. We want the few code 19 people
to show on a report along with the normal 14 or 16 Restype people for a given trip.

So I figured to use an IIF criteria statement. I am trying to tell the criteria if the person is
a code 19, then pull up all the code 14 and 16 trips combined. The 19 code is the only exception.
I hope that helps. Thanks for helping me.

 

[dhookom]

You should provide the entire SQL view.

Also, I would seriously consider modeling this in your tables with a small table of ResTypeID and ResTypeAssociated. Then every type would have a duplicate RestTypeAssociated and 19 would have three rows for 19, 14, and 16.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

So I figured to use an IIf criteria statement. I am trying to tell the criteria if the person is
a code 19, then pull up all the code 14 and 16 trips combined.

In addition to Skip's and Duane posts. You may need to extend your formula, maybe nested IIf in second argument. Logical Or comparison returns [tt]True/False[/tt] ([tt]([tblTravelMain].[ResTypeID])=14 Or ([tblTravelMain].[ResTypeID])=16[/tt]). As it is a second argument in your IIf, you get [tt]True/False[/tt] (or [tt]-1/0[/tt]) if cboResTypeID=19.



combo

 

[cimoli]

Hold off. I see RestypeID in 2 tables. I think there is a design error. Trying to do too much.

Thanks for convincing me. Glen