I have a query that has 4 fields that are text fields in an external database. I have text boxes set up on a form which contain either null or >0 (depending on an option button selected). I want to pass that >0 to one field in the query and null to the other three. I have the following as criteria in my query:
[Forms]![frmSelectPayment]![txt2ndPayment] (same one for each of the 4 fields except the form field name changes)

Below is the code that sets the text boxes that are sent to the query:
Select Case [FraSelectPayment] 'depending on which pmt being pulled
Case 1 '6 payment plan only
stDocName = "qryGetPlusARImport"
Select Case [FraChoose]
Case 1
Me.txt2ndPayment = ">0"
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 2
Me.txt2ndPayment = Null
Me.txt3rdPayment = ">0"
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 3
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = ">0"
Me.txt6thPayment = Null
Case 4
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = ">0"
Case Else
MsgBox "Please select a payment"
FraChoose.SetFocus
Exit Sub
End Select

DoCmd.OpenQuery stDocName, acNormal, acEdit


If I run the query alone I leave all fields blank and put >0 on the one I want to search by it works fine (695 records returned) but when I run it through code, nothing is returned. I really hope this makes sense to someone! Can anyone see what I am doing wrong?
Thanks

Parameter queries can take values at run time but cannot otherwise be changed. You are attempting to change the structure of the query by changing what is currently an "=" to a ">".

You can build the the SQL string in VBA and run that rather than the saved querydef. It looks to me as though your table is not normalized. Why do you have 4 payment fields in each record? In a normal structure, you would have 4 records with one payment field each and would be able to create your query without having to worry about which row was returned.

In addition to what Pat said, when you have a normalised database, you have less lines of code to execute and it is easier to read.

Thanks so much for responding. I changed it to a SQL string, eliminated all unnecessary '=null' and now it does what I want. This is, unfortunately, querying an external oracle database over which I have no control. This table that I am querying does only have payment information, i.e. each row has a patronid, a plan number and then the 4 payments. If a person does not have a plan, they are not in that table. All other information about the patron is in numerous other tables. Not sure why it was created that way but that's how it goes.
At any rate, thanks again!