Solved Show all Records on zero/null etc (1 Viewer)

[Tastech]

Hi all, having a mental block on this one. It should be easy but I can't get it.

I have a query take returns results based on several combos. Standard stuff =[forms]![frmMain]![cboWorkSlips] etc.

However on two of combo critieria selections I would like to have and "ALL" option. Once again easy enough with a union.

What I having problem with is this. Two of the combos use numbers 1,2,3,4... etc. So I can report 1 or 2 etc. I would like the "ALL" to show all.

The iif function is ok IIf([forms]![frmMain]![cboWorkSlipsSect]>0,[forms]![frmMain]![cboWorkSlipsSect],#) but it is the False part I'm having problems with. I need it to show all records.

Can someone please prod me with a stick...

 

[CJ_London]

Describing what you have is vague- show us. What is the sql to the query that references your combo and what is the sql to your combo?

 

[Gasman]

Gnerally you would have an OR field is Null OR Field is 0 I believe.

 

[cheekybuddha]

First, what is the actual SQL RowSource of the combo's in question?

If it is just a single field then your criteria would be:

WHERE YourField = [forms]![frmMain]![cboWorkSlips] OR [forms]![frmMain]![cboWorkSlips] = 'ALL'

If you are using two fields with the first field hidden and 'ALL' = Null then it would be:

WHERE YourField = [forms]![frmMain]![cboWorkSlips] OR [forms]![frmMain]![cboWorkSlips] IS NULL

 

[CJ_London]

another alternative if the combo 'All' value is null

WHERE YourField = nz([forms]![frmMain]![cboWorkSlips],YourField)

 

[Tastech]

This is the SQL. I didn't include it as it is a bit long winded. Basically it selects which records to print on a report.

SELECT DISTINCT tblShowDogs.ExhNo, tblShowRingJudges.JudgeID, tblDogDetails.Group, tblShowRings.RingNO, tblShowDogs.Adult, tblBreedType.Breed, tblTitleType.Title, tblClasses.ClassDescription, tblBreedColours.Colour, tblShowDogs.ShowID, tblShowDogs.DogID, tblShows.ClubID, tblShows.ShowDate, tblDogDetails.DateofBirth, tblClubs.Abreviation, tblClasses.Abbreviation, tblClasses.ClassCodeID, tblClasses.ClassID, tblBreedType.Agouti, tblBreedType.ColourCompete, tblSexType.Sex, tblClasses.ClassSex, tblBreedType.JudgingOrder, tblBreedColours.ColourID, tblClasses.ChampClass, tblShowDogs.WithDrawn, tblDogDetails.BreedID, tblShowDogs.SectionID, tblShowRings.RingID
FROM (((tblShows LEFT JOIN tblClubs ON tblShows.ClubID = tblClubs.ClubID) LEFT JOIN tblClubRep ON tblShows.ClubRepID = tblClubRep.ClubRepID) RIGHT JOIN ((tblSexType RIGHT JOIN (tblOwnerDetails RIGHT JOIN (tblBreedColours RIGHT JOIN (tblBreedType RIGHT JOIN ((tblTitleType RIGHT JOIN (tblShowDogs LEFT JOIN tblDogDetails ON tblShowDogs.DogID = tblDogDetails.DogID) ON tblTitleType.TitleID = tblDogDetails.TitleID) LEFT JOIN tblClasses ON tblShowDogs.ClassID = tblClasses.ClassID) ON tblBreedType.BreedID = tblDogDetails.BreedID) ON tblBreedColours.ColourID = tblDogDetails.ColourID) ON tblOwnerDetails.OwnerID = tblDogDetails.OwnerID) ON tblSexType.SexID = tblDogDetails.SexID) INNER JOIN tblShowRingJudges ON (tblShowDogs.ShowID = tblShowRingJudges.ShowID) AND (tblShowDogs.Group = tblShowRingJudges.Group)) ON tblShows.ShowID = tblShowRingJudges.ShowID) INNER JOIN tblShowRings ON tblShowRingJudges.RingID = tblShowRings.RingID
WHERE (((tblShowRingJudges.JudgeID)=[forms]![frmMain]![cboJudgeSlips]) AND ((tblShowDogs.ShowID)=[forms]![frmMain]![sfrmShows]![showid]) AND ((tblShowDogs.SectionID)=IIf([forms]![frmMain]![cboJudgeSlipsSect]>0,[forms]![frmMain]![cboJudgeSlipsSect],[#])) AND ((tblShowRings.RingID)=[forms]![frmMain]![cboRingNo]))
ORDER BY tblShowDogs.ExhNo, tblShowDogs.Adult;

The part that doesn't work is ((tblShowDogs.SectionID)=IIf([forms]![frmMain]![cboJudgeSlipsSect]>0,[forms]![frmMain]![cboJudgeSlipsSect],)) An Astrick doesn't work in the False part of the formulae. The values for the combo 1,2,3 or 4. I want to be able to select 1 or 2 or 3 or 4 or all of them.

 

[CJ_London]

You now have 3 options to try

 

[Tastech]

I should say that I can make the "ALL" option anything a null or 0. So Null,1,2,3,4 or 0,1,2,3,4

 

[Tastech]

WHERE YourField = nz([forms]![frmMain]![cboWorkSlips],YourField) won't work because there are no records that have a zero or null. They will have only 1,2,3 or 4.

 

[CJ_London]

Either add one in or use a union query as you mentioned in your first post

 

[Tastech]

This is the combo source

SELECT DISTINCT sqryResultJudges_MultiJudge.SectionID, ucase(sqryResultJudges_MultiJudge.Section) FROM sqryResultJudges_MultiJudge " & _
    "WHERE ((sqryResultJudges_MultiJudge.JudgeID = " & Me!cboJudgeSlips.Column(0) & ") AND (sqryResultJudges_MultiJudge.RingID = " & Me!cboRingNo.Column(0) & ")) " & _
    "UNION SELECT '0',' < ALL > ' FROM sqryResultJudges_MultiJudge " & _
    "ORDER BY sqryResultJudges_MultiJudge.SectionID;"

I can make the UNION SELECT '0' or a NULL or anything really but the combo is getting the data from a seperate summarized query. The results of that query will only be 1 or 2 or 3 or 4 and with the Union added (say) 0. However the report query doesn't have the 0 or NULL (or whatever) value in that Field it will only have 1,2,3,4.

Hmmm. I thought this would be a simple answer. The Iif statement in the critieria would work fine with an "*" in the False part if the field was Text but it doesn't work with a numeric field. In the data itself there are no zeros or NULLs only 1-4s

 

[CJ_London]

the nz function only works with null, not 0. Your union query uses 0 so discard the null approach

However the report query doesn't have the 0 or NULL (or whatever) value in that Field it will only have 1,2,3,4.

it doesn't matter - your criteria would be

tblShowDogs.SectionID=IIf([forms]![frmMain]![cboJudgeSlipsSect]>0,[forms]![frmMain]![cboJudgeSlipsSect],tblShowDogs.SectionID)

So if forms]![frmMain]![cboJudgeSlipsSect]=0 then tblShowDogs.SectionID=tblShowDogs.SectionID

 

[Tastech]

THAT'S what I had the blank on. Needed to add the Field name into the false part.

Thank you so much CJ. Told you I needed a hit with a stick. Couldn't see the birds for the trees.

 

[Isaac]

Hi all, having a mental block on this one. It should be easy but I can't get it.

I have a query take returns results based on several combos. Standard stuff =[forms]![frmMain]![cboWorkSlips] etc.

However on two of combo critieria selections I would like to have and "ALL" option. Once again easy enough with a union.

What I having problem with is this. Two of the combos use numbers 1,2,3,4... etc. So I can report 1 or 2 etc. I would like the "ALL" to show all.

The iif function is ok IIf([forms]![frmMain]![cboWorkSlipsSect]>0,[forms]![frmMain]![cboWorkSlipsSect],#) but it is the False part I'm having problems with. I need it to show all records.

Can someone please prod me with a stick...

Personally, I've found that referring directly to form controls in a query is like a dead end street. It may be pretty and drivable, but it comes to an end pretty quick - it is VERY limited. You constantly run into things that just can't be solved using a one-liner expression, OR can be, but at the expense of readability and the next guy to be able to edit, document, troubleshoot, and maintain.

You might as well write a vba function that evaluates the situation and reacts accordingly, and embed the function in the query parameter, or write the whole thing in vba/sql.

You'll not only have something that works, but something that can be more easily troubleshot, documented, edited, and maintained.