ClaudeWalker
asked on
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.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 & "'"
If building number is Numeric (?) then:
rs.filter = "[BuildingNumber]=" & Me.BuildingNumber
rs.filter = "[BuildingNumber]=" & Me.BuildingNumber
"The datatype for BuildingNumber is a text (despite the field name)."
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
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
ASKER
"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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try my first post ?
mx
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
@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.
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.
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.
rs.filter = "[BuildingNumber]=" & Chr(34) & Me.BuildingNumber & Chr(34)
mx