Option Group Null, Not Null, and All values 1

[blakebry]

Hello
I have found a what looks to be a solution from a previous thread on this forum, but my query returns no results.
Can someone assist.
I am trying to return results from a query based on field Tbl_Tracking. I have an option group of (1 In Transit 2 Arrived and 3 All) on form Frm_Global_Tracking
I have double and triple checked my spelling of the forms and frame to make sure all is correct.

Query
SELECT Tbl_Tracking.Destination, Tbl_Tracking.Origin, Tbl_Tracking.[Actual ETA]
FROM Tbl_Tracking
WHERE (((Tbl_Tracking.[Actual ETA])=IIf([Forms]![Frm_Global_Tracking]![fraTrans]=1,[Tbl_Tracking].[Actual ETA] Is Null,IIf([Forms]![Frm_Global_Tracking]![fraTrans]=2,[Tbl_Tracking].[Actual ETA] Is Not Null,1=1))));

Thanks

 

[lameid]

People do not read every thread. You should reference the thread if it is worth mentioning. You copy the word thread and the number following it on that thread and then you end up with a hyperlink to that thread in your own.

As for your where clause it seems ineffecient, how about:

WHERE Tbl_Tracking.[Actual ETA] = 
(([Forms]![Frm_Global_Tracking]![fraTrans] = 1 AND[Tbl_Tracking].[Actual ETA] Is Null)
 Or ([Forms]![Frm_Global_Tracking]![fraTrans] = 2 AND [Tbl_Tracking].[Actual ETA] Is Not Null)
 OR ([Forms]![Frm_Global_Tracking]![fraTrans] Not IN (1,2)))

I assume of course that Tbl_Tracking.[Actual ETA] is a yes/no field.

Also I didn't catch anything wrong with your syntax but I didn't look closely either. I am guessing the details of your problem may be with your original thread.

 

[TheAceMan1]

How are ya blakebry . . .

Perhaps if you explain what your trying to do would be more approriate! . . . Your IIf's are out to lunch as ytou return Nulls! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[blakebry]

I think out of frustration I could not explain yesterday.
thread705-1207666 I found some helpful information from this thread but cannot make it work. I am basically trying to do the same thing.
Here's what I have.

A table with a field called Actual Eta. This field has a date in it or is blank.

I have an option group on a form (1) In transit, want to return null values from the query. (2) Arrived, want to return not null values (rows with arrived dates) from the query. (3) All null and not null.

My query does not pull any results currently.

 

[lameid]

blakerbry I am seeing a double entendre in your post.

Are you wanting to conditionally display a value for a field (select statement) or are you wanting to display records with branching criteria (where statemtent)?

If it is criteria I am guessing my original post without the "Tbl_Tracking.[Actual ETA] = " is what you are after.

 

[blakebry]

Okay Finally got it to work. I was missing a () somewhere so the query was not responding.

Here is what i was trying to pull off. Thanks to all of you!!!!

SELECT Tbl_Tracking.Origin, Tbl_Tracking.Destination, Tbl_Tracking.[Actual ETA], Tbl_Tracking.[Schedule ETA], Tbl_Tracking.[Air/Ocean/Truck]
FROM Tbl_Tracking
WHERE (((Tbl_Tracking.Origin)=Forms!Frm_Global_Tracking!Cbrigin) And ((Tbl_Tracking.Destination)=Forms!Frm_Global_Tracking!Cbo_Destination) And ((((Tbl_Tracking.[Actual ETA]) Is Null) And ((Forms!Frm_Global_Tracking!optTransit)=1)) Or (((Tbl_Tracking.[Actual ETA]) Is Not Null) And ((Forms!Frm_Global_Tracking!optTransit)=2)) Or (((Forms!Frm_Global_Tracking!optTransit) Not In (1,2)))) And ((Tbl_Tracking.[Schedule ETA]) Between Forms!Frm_Global_Tracking!Txt_ETA_From And Forms!Frm_Global_Tracking!Txt_ETA_To) And ((Tbl_Tracking.[Air/Ocean/Truck]) Like Forms!Frm_Global_Tracking!txtMode)) Or ((Forms!Frm_Global_Tracking!Cbrigin Is Null) And ((Tbl_Tracking.Destination)=Forms!Frm_Global_Tracking!Cbo_Destination) And ((((Tbl_Tracking.[Actual ETA]) Is Null) And ((Forms!Frm_Global_Tracking!optTransit)=1)) Or (((Tbl_Tracking.[Actual ETA]) Is Not Null) And ((Forms!Frm_Global_Tracking!optTransit)=2)) Or (((Forms!Frm_Global_Tracking!optTransit) Not In (1,2)))) And ((Tbl_Tracking.[Schedule ETA]) Between Forms!Frm_Global_Tracking!Txt_ETA_From And Forms!Frm_Global_Tracking!Txt_ETA_To) And ((Tbl_Tracking.[Air/Ocean/Truck]) Like Forms!Frm_Global_Tracking!txtMode)) Or (((Tbl_Tracking.Origin)=Forms!Frm_Global_Tracking!Cbrigin) And (Forms!Frm_Global_Tracking!Cbo_Destination Is Null) And ((((Tbl_Tracking.[Actual ETA]) Is Null) And ((Forms!Frm_Global_Tracking!optTransit)=1)) Or (((Tbl_Tracking.[Actual ETA]) Is Not Null) And ((Forms!Frm_Global_Tracking!optTransit)=2)) Or (((Forms!Frm_Global_Tracking!optTransit) Not In (1,2)))) And ((Tbl_Tracking.[Schedule ETA]) Between Forms!Frm_Global_Tracking!Txt_ETA_From And Forms!Frm_Global_Tracking!Txt_ETA_To) And ((Tbl_Tracking.[Air/Ocean/Truck]) Like Forms!Frm_Global_Tracking!txtMode));