Using Concatenated fields as Query Criteria

[state90]

I am trying to combine the results of a combo box and a text box from a form and use the result as criteria in a query that feeds a report. The combo box is a list of operators (=, >, <, etc) and the text box is a user entered amount (i.e., 5000). The combination would be "> 5000" and should appear as criteria on the query. In other words, the concatenated result would drive the query to show Capital Need >5000 (or whatever the user selects.

I tried using [Forms]![Frm_REPORT_Parameter_01]![Combo11]&[Forms]![Frm_REPORT_Parameter_01]![Text9] in the query criteria area but the result seems to ignore the operator (In other words, if the result should be ">5000" the query will produce those records equal to 5000.

I know the concatenation is working in structure because I then created a text box directly on the form that shows the concatenation result and it shgwos it as "> 5000". Naturally, I tried to have the query criteria simply take the result of that text box (Forms![Frm_REPORT_Parameter_01]![Text11]) but it comes back with null results.

I sincerely appreciate any help in getting this fixed!

Thank you.

 

[state90]

Thank you Uncle Gizmo!

To be clear, I just copy and paste that into the Query criteria for the field in question (Capital Need)?

Kurt

 

[state90]

What does Chr(34) mean and do?

 

[state90]

Uncle Gizmo, Thank you for your help. I would be curious to see it work the way you suggested but as a heads-up, I was able to get it working via VBA as follows if you would be interested in seeing this other approach offered to me on another board:

Private Sub Command22_Click()
Dim strCrit As String
strCrit = "[CapitalNeed] "
If IsNull(Me!Combo11) Then
MsgBox "Please select =, <, >= etc."
Me. Combo11.SetFocus
Exit Sub
Else
strCrit = strCrit & Me!Combo11
End If
If IsNull(Me!text9) Then
MsgBox "Please put a value in the textbox"
Me!text9.SetFocus
Exit Sub
Else
strCrit = strCrit & " " & Me!text9
End If
DoCmd.OpenReport " FacilityIdentifyReportDR", acViewReport, WhereCondition:=strCrit
End Sub

Kurt

 

[state90]

Sorry...I didn't realize that it was in bad form to post a question to two different forums. I apologize. I have relied on these forums for answers many times but had never posted before. I wasn't sure how quickly responses came back and definitely needed answers quickly but I do apologize.

I actually do want to figure out how to make the method you suggested work because, to me, it is a learning process. I think I may have to slim down my database into just a sample as it is a pretty hefty database. I will circle back. Again, I appreciate your help.