filter using option group (1 Viewer)

B

britesynth

Registered User.

Local time

Today, 18:01

Joined

Mar 11, 2013

Messages

88

• Aug 15, 2018

• #1

good day!

I want to use an option group as criteria for my query

Titled = 1
Tax Dec = 2
CLOA = 3
All = 4

options 1-3 are from a table with fields:

[typeID] [typeNum] and [type]
1 1 Titled
2 2 Tax Dec
3 3 CLOA


question is what to put on the criteria of my [RegType] field on my query

will an iif statement work?

how do I say that I want to show all my records?

thank you in advance

 

Cronk

Registered User.

Local time

Today, 20:01

Joined

Jul 4, 2013

Messages

2,772

• Aug 15, 2018

• #2

Presumably, your option group is on a form.

If so the criteria in your query criteria would be

Code:Copy to clipboard

iif(forms!YourForm!YourOptionGroup<4,True, forms!YourForm!YourOptionGroup)

 

B

britesynth

Registered User.

Local time

Today, 18:01

Joined

Mar 11, 2013

Messages

88

• Aug 15, 2018

• #3

Cronk said:

Presumably, your option group is on a form.

If so the criteria in your query criteria would be

Code:Copy to clipboard

iif(forms!YourForm!YourOptionGroup<4,True, forms!YourForm!YourOptionGroup)

Click to expand...

Hello!

Thank you for your suggestion but when I open my form and source query nothing is displayed, I've tried a couple of other things like leaving the false statement blank or (forms!YourForm!YourOptionGroup <>4) and still i get a blank form and query

 

Cronk

Registered User.

Local time

Today, 20:01

Joined

Jul 4, 2013

Messages

2,772

• Aug 15, 2018

• #4

First of all, apologies. I thought I had edited my post, but obviously not.


Try
iif(forms!YourForm!YourOptionGroup<4, forms!YourForm!YourOptionGroup,<4)


By the way, you are substituting your actual form name and control name, no?


If you are still having problems, supply the SQL

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 06:01

Joined

Feb 19, 2002

Messages

43,263

• Aug 16, 2018

• #5

Select ...
From ...
Where TypeID = Forms!yourform!frmTypeID OR Forms!yourformm!frmTypeID = 4;

 

B

britesynth

Registered User.

Local time

Today, 18:01

Joined

Mar 11, 2013

Messages

88

• Aug 16, 2018

• #6

I think I'm doing something wrong, I'm doing this in a split form, should I make a form with the query as a subform for the option group to work?

I am able to filter my query with ease using multiple combo boxes but this time around I want to try using option group

 

B

britesynth

Registered User.

Local time

Today, 18:01

Joined

Mar 11, 2013

Messages

88

• Aug 16, 2018

• #7

Cronk said:

First of all, apologies. I thought I had edited my post, but obviously not.


Try
iif(forms!YourForm!YourOptionGroup<4, forms!YourForm!YourOptionGroup,<4)


By the way, you are substituting your actual form name and control name, no?


If you are still having problems, supply the SQL

Click to expand...

Code:Copy to clipboard

SELECT tblPropertyDetails.PropID, tblPropertyDetails.RegisteredOwner, tblPropertyDetails.Location, tblPropertyDetails.RegType, tblPropertyDetails.[TCT / TaxDecNo], tblPropertyDetails.LotArea, tblPropertyDetails.[Included?]
FROM tblPropertyDetails
GROUP BY tblPropertyDetails.PropID, tblPropertyDetails.RegisteredOwner, tblPropertyDetails.Location, tblPropertyDetails.RegType, tblPropertyDetails.[TCT / TaxDecNo], tblPropertyDetails.LotArea, tblPropertyDetails.[Included?]
HAVING (((tblPropertyDetails.RegType)=IIf([Forms]![Query1]![RegType]<4,[Forms]![Query1]![RegType],([tblPropertyDetails].[RegType])<4)));

Form still displays nothing
thanks!