Build an "or" clause in VBA and use as criteria in a query

[jmaluso]

I have the following code that puts a string together and throws it into a hidden textbox (txtreportcriteria) on a form (fmnubldrrebates) for me and it looks right but I don't get any records back from the query when using it. There are records to return I tried just typing in "Kent Bridell Home" in the criteria box of the query.

Dim BuilderList as String

If BuilderList <> "" Then
BuilderList = BuilderList & " or " & Chr$(34) & Me!lstBuilders.Column(0, BuilderSelected) & Chr$(34)
Else
BuilderList = Chr$(34) & Me!lstBuilders.Column(0, BuilderSelected) & Chr$(34)
End If

So the result looks something like this - "Kent Bridell Homes" or "J. A. Myers Homes" or "Cathy Calvin Homes"

And in the query I have the following in the criteria field - [forms]![fmnubldrrebates].[txtreportcriteria]

What am I doing wrong? Is this what the query is seeing - ""Kent Bridell Homes" or "J. A. Myers Homes" or "Cathy Calvin Homes"" and is confusing it? If so how do I get around this? I just need to be able to build the "or" statement on the fly and limit my query from it.

Please help,
~ Joe

 

[ecniv]

Try changing the reference in the Sql statement to :

In(forms!formname!txtbox)

Then change the filter code to:

If BuilderList <> "" Then
    BuilderList = BuilderList & "," & Chr$(34) & Me!lstBuilders.Column(0, BuilderSelected) & Chr$(34)
Else
    BuilderList = Chr$(34) & Me!lstBuilders.Column(0, BuilderSelected) & Chr$(34)
End If

It will produce a filter of those in the list, enclosed by speechmarks, on an exact match.. I think.


Vince

 

[jmaluso]

No dice. I changed the VBA as you instructed and here is the new SQL. Did i do it right?

SELECT Customers.MonthRyanPd, Customers.Appt_Date, Format([Lot_#],"> ") AS [LOT#], Customers.Subdivision, Customers.[CUST#1_LAST], IIf(Customers!Contract_Amount Is Null,0,Customers!Contract_Amount) AS CA, IIf([Consultant]='SP' Or [Consultant]='XX' Or [terms]=3 Or ([MonthCancelled] Is Not Null And ([CommissionMonth] Between forms!fmnubldrrebates.txtstartdate And forms!fmnubldrrebates.txtenddate)),0,IIf([MonthCancelled] Is Not Null,0-[Contract_Amount]*0.1,[Contract_Amount]*0.1)) AS RebateAmt, forms!fmnubldrrebates.txtstartdate AS StartDate, forms!fmnubldrrebates.txtenddate AS EndDate, Customers.Terms, Customers.Appt_Date, Customers.[CUST#2_LAST], Customers.[BLDR PKG-CPT], Customers.MonthCancelled, Customers.MonthRyanPd, Customers.Consultant, IIf([Terms]=3 And [MonthCancelled] Is Null,'BIM',IIf([MonthCancelled] Is Null,IIf(([CUST#1_LAST] Is Null) And ([CUST#2_LAST] Is Null),'Cancelled',''),Format([MonthCancelled],'mm/yy'))) AS BIM, Customers.DateReceived, Customers.MonthRyanPd, Customers.Model, tblHUC.Complete, Customers.Sidemark, Customers.Builder
FROM Customers INNER JOIN tblHUC ON Customers.[Lot_#] = tblHUC.RealLot
WHERE (((Customers.MonthRyanPd) Between [forms]![fmnubldrrebates].[txtstartdate] And [forms]![fmnubldrrebates].[txtenddate]) AND ((Customers.Builder) In ([forms]![fmnubldrrebates].[txtreportcriteria])))
ORDER BY Customers.MonthRyanPd, Customers.Sidemark;

 

[jmaluso]

Once again it works only if I type In ('J. A. Myers Homes','Capano Homes') in the criteria of the query. For some reason the code generated does't work.

This is what I assume the code is creating - In ("'J. A. Myers Homes','Capano Homes'") with that extra " because it is a string field. How can this extra " on the front and rear be stripped or supressed? Am I right in this assumption?

~ Joe

 

[ecniv]

Um... Not sure...

I don't usually do bound things.. I do the harder VBA coded Sql statement way...

You could change the rowsource... If you copied the main sql statement (the one that returnes records but doesn't have a where clause... or extra where clause parts) then add a where clause (if one doesn't exist) with the "[field] in (" & txthiddenbox & ")" to a string then put this string in as the rowsource and requery...

Example:

strSql = "select * from table"
strSql = strSql & " Where [field1] in (" & txtHiddenField & ")"

lstResults.rowsource = strSql
lstResults.requery

Another option...
I might try an example like you have tomorrow and see whether I can make another way to filter like that.

Vince

 

[jmaluso]

Ok, new approach.

This does not return any rows.
-------------
DoCmd.OpenReport "rptlanerebates", acViewPreview, , "((Customers.MonthRyanPd) Between #" & [Forms]![fmnubldrrebates].[txtStartDate] & "# And #" & [Forms]![fmnubldrrebates].[txtEndDate] & "# AND ((Customers.builder)='J. A. Myers Homes'))"
--------------

This does (and if i use this SQL and manually type 'J. A. Myers Homes' into the criteria of the query it works the way I wat it to. But why not through code?
---------------
DoCmd.OpenReport "rptlanerebates", acViewPreview, , "((Customers.MonthRyanPd) Between #" & [Forms]![fmnubldrrebates].[txtStartDate] & "# And #" & [Forms]![fmnubldrrebates].[txtEndDate] & "#)"
----------------

I need to add a statement to this that will allow me to filter [customers].[builder] by a string variable which i showed how i build a few posts up. Also, why doesn't my 1st SQL statement in this post work? Incorrect syntax?

Thanks,
~ Joe

 

[jmaluso]

Bump this to the top. I am stumped.