Arguments of Wrong Type ADO Filter

I have a recordset that I am trying to filter.  The query is an aggregate query where I am summing.  I want to filter by a building number but for some reason I get an "Arguments of Wrong Type, are out of acceptable range or are in conflict with one another" error when I try to filter

rs.filter = "[WX Jobs].[BuildingNumber]='" & Me.BuildingNumber & "'"

The datatype for BuildingNumber is a text (despite the field name).

Any ideas on why this is happening?

Thanks,
JOe K.

rs.Open "SELECT Sum(tblUnitMeasureDetails.ARRA_Hours) AS Hours, Sum(IIf([ExpendClass]='Mat Expense',[UnitPrice]*[MeasureQuantity],0)) AS Mat " & _
"FROM lstExpendClass INNER JOIN ([WX Jobs] INNER JOIN (tblUnitMeasureDetails INNER JOIN (tblPO_Details INNER JOIN tblMeasureItems ON tblPO_Details.PO_DetailsID = tblMeasureItems.fkPO_Details) ON tblUnitMeasureDetails.UnitMeasureDetailsID = tblMeasureItems.fkUnitMeasureDetails) ON [WX Jobs].FileNumber = tblUnitMeasureDetails.fkFileNumberUnit) ON lstExpendClass.ExpendClassID = tblPO_Details.ExpendClassID ", , adOpenStatic, adLockOptimistic
        
'this is where error happens

rs.filter = "[WX Jobs].[BuildingNumber]='" & Me.BuildingNumber & "'"

Open in new window

ClaudeWalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
maybe this:

rs.filter = "[BuildingNumber]=" & Chr(34)  & Me.BuildingNumber & Chr(34)

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If building number is Numeric (?) then:

rs.filter = "[BuildingNumber]=" &  Me.BuildingNumber  
0
Neil RussellTechnical Development LeadCommented:
"The datatype for BuildingNumber is a text (despite the field name)."
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you really need to filter "after" the query, and not filter during the query?
that is, most often, more effective all way long.
in 10 years, I never used the .Filter once I had created a VERY ineffective application, when I tried to use it in a multi-user application.
I can only recommend not to use it, in general
0
ClaudeWalkerAuthor Commented:
"do you really need to filter "after" the query, and not filter during the query"

perhaps it is impossible to filter after the fact.  In looking at my query it is summed and the number of records is one.  So I don't think I can filter that one record after the fact.  Perhaps the best way to attack this is to filter the recordset and then loop through the filtered record set.

"I never used the .Filter once I had created a VERY ineffective application, when I tried to use it in a multi-user application."

The reason I am using a filter is because I would be running the same query five times with different filters.  So instead of querying the backend 5 times I would only query it once and grab the recordset into memory and then can filter it in memory.

That's my logic.  I could be wrong (let me know if I am).  

In the interim I am going to try to do the loop/filter method and perhaps change it to "individual" (run and then closed) recordsets for totals.

Thanks,
JOe K.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The reason I am using a filter is because I would be running the same query five times with different filters.  So instead of
querying the backend 5 times I would only query it once and grab the recordset into memory and then can filter it in memory.

that's actually fine, this is the rare case where the "filter" on the local machine will be more effective.
however, still, using ADO's filter is usually no working too good.
I create a collection/dictionary of the results as needed ... 1 item per "filter" ...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Did you try my first post ?

mx
0
Rey Obrero (Capricorn1)Commented:
you are getting the error because the field [WX Jobs].[BuildingNumber] is not included in your recordset
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ClaudeWalkerAuthor Commented:
@everyone:  Sorry about the delayed response

@MX:  Yes I tried it but it did not work.  Thanks for trying though

@angelll:  "I create a collection/dictionary of the results as needed ... 1 item per filter"  Are you saying you do one field at a time?  

@capricorn1:  That was the reason why.

Thanks,
JOe K.

I'll award points in a bit I just want to see what angelll was referring too.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
good catch, capricorn1!

ClaudeWalker: the method I refer to cannot be used in any case, it depends on what you need the resulting data for.

apart from that, I create a helper class to load the row's data into, and add the class instance (object) to the collection.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.