Combo Box Value into Query

[CodyMonster]

Don't know if this has been answered in the past.
But I'm trying to pass a value from a combo box into a query criteria.
There is one issue though, I'm trying to get the values of yes or no into a > 0 value into the criteria value.
For example, if someone chooses "yes" in the combo box it would be >0 in the criteria. If it's "No" then the criteria would be blank.

Seems simple enough but I'm not really finding what I need when I search for it.

Thanks for anyone's help.

 

[CodyMonster]

I'm trying

IIf([Forms]![FrontPage]![ReverseCombo]="No",>0,"*")

in the criteria but it's not working.
I have a feeling this is something small and dumb I'm not doing here. any help would be greatly appreciated.

 

[CodyMonster]

Is there something wrong with the question? Maybe I'm not being clear?

 

[Marbles]

What result do you want to see if the criteria is blank?

 

[CodyMonster]

Hey thanks or replying back. I thought I was yelling into a canyon for second.
If the criteria is blank, it should return both positive and negative numbers.
I already have other criteria in the query that limits the data.

I kinda found a solution, in the code below. It works. But not the way I was looking to do it.
I wanted to have an iif statement in the criteria of the query that would be based off of the form input.

Now I have to figure out why I'm getting duplicates in my report.
googling is slow sometimes but I find an answer eventually.

Private Sub Run_Button_Click()Dim StrSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef


Set db = Application.CurrentDb
Set qdf = db.QueryDefs("Report_Qry")


DoCmd.Close acReport, "ND- Form 10", acSaveNo


If Me.Reverse_Vol = "No" Then
StrSQL = "SELECT [Final10 Restatement History].Field, [Final10 Restatement History].Operator, [Final10 Restatement History].[Lease Number], [Final10 Restatement History].[Well Name and Number], [Final10 Restatement History].[NDIC CTB No], [Final10 Restatement History].[Quantity (bbls)] FROM [Final10 Restatement History] WHERE ((([Final10 Restatement History].[Quantity (bbls)])>0) AND (([Final10 Restatement History].Date)=[Forms]![FrontPage]![Avaliable_Dates]) AND (([Final10 Restatement History].Transporter)=[forms]![FrontPage]![Transporters]));"
 
Else
StrSQL = "SELECT [Final10 Restatement History].Field, [Final10 Restatement History].Operator, [Final10 Restatement History].[Lease Number], [Final10 Restatement History].[Well Name and Number], [Final10 Restatement History].[NDIC CTB No], [Final10 Restatement History].[Quantity (bbls)] FROM [Final10 Restatement History] WHERE ((([Final10 Restatement History].Date)=[Forms]![FrontPage]![Avaliable_Dates]) AND (([Final10 Restatement History].Transporter)=[forms]![FrontPage]![Transporters]));"




End If


qdf.SQL = StrSQL
DoCmd.OpenReport "ND- Form 10", acViewReport


End Sub

 

[Marbles]

If you add another text box in the form. It can be hidden. In the After Update Event of your Combo box make the value of the text box 2 for Yes, and 1 for No.
In the Query add a field with the expression: Expr1: IIf([Your Number Field]>0,2,1). In the Criteria of this expression put: >=[Forms]![Form1]![Your TextBox].
If the Combo is No, you get all the numbers, if the Combo is Yes, you only get the positives.