Query Criteria - built up from Option group and Txt Box

[ianward]

I have a listbox which is populated by a query, however i have been asked by our Commissioning department if it would be possible to search contract values, with more than, less then etc.

I added an option group to my form with the following afterupdate

Select Case Me.opt_FinanceOption

Case 1
Me.txt_FinancialFilter = "*"
Case 2
Me.txt_FinancialFilter = "=" & txt_FinancialValue
Case 3
Me.txt_FinancialFilter = "<" & txt_FinancialValue
Case 4
Me.txt_FinancialFilter = ">" & txt_FinancialValue
  
End Select

Which puts builds up my desired function ie: <100000 for contract with a value of less than £100,000

I then have a button which refeshes the listbox,

however when i try to reference this textbox 'txt_FinancialFilter' in the criteria in the query i get an error stating the expression is typed incorrectly, or is too complex to be evaluated etc...

Can anyone please point me in the right direction?

Many Thanks - Ian

 

[Atomic Shrimp]

Can you paste a copy of the expression it's building? (i.e. the value of Me.txt_FinancialFilter after the select statement group)

I'm guessing this is going to be a problem with missing spaces or something.

 

[DCrake]

Try changing your first case to "<>0" for starters

However if you want to impress your bosses what you can do is to get them to enter a value into a text box then in another textbox use a +/- value to say with a range either side of the value.

Do if they enter 10,000 anf then enter 1000 in the other

You would first check to see if a value hase been entered then if so you would biuld up your criteria string

"Where [YourField] Between " & (Me.InitValue - Me.RangeValue) & " AND " & (Me.InitValue + Me.RangeValue)

So it would look for all values between 9000 and 11000


David

 

[ianward]

Thanks David for the range suggestion, but we are looking really to filter contracts with a value less then or more than.

Mike - the value of the textbox Me.txt_FinancialFilter is "<1000000" without the quotes.

The criteria I am entering into the query is:

Like [Forms]![frm_ContractSearch]![txt_FinancialFilter]

The top option for select all works, but the remaining = < > dont. Is this because it is trying to filter a field based on currency but is using "<1000000" as text? If so is there a way to pass the < > or = to the query without it being recognised as text.

 

[Atomic Shrimp]

Please could you post the SQL of the query? - it sounds like maybe it's just not correctly pointing at the text box.

 

[ianward]

Hi Mike,

The SQL for the query is as follows:

SELECT tbl_Contracts.ContractID, tbl_Organisations_And_Sites.[Organisation Name], tbl_ContractType.ContractType, tbl_Contracts.StartDate, Sum(tbl_ServiceLine.Activity) AS Activity, Sum(tbl_ServiceLine.ServiceValue) AS [Value]
FROM tbl_Organisations_And_Sites INNER JOIN (tbl_ContractType RIGHT JOIN (tbl_Contracts LEFT JOIN tbl_ServiceLine ON tbl_Contracts.ContractID = tbl_ServiceLine.ContractID) ON tbl_ContractType.ContractTypeID = tbl_Contracts.ContractType) ON tbl_Organisations_And_Sites.[Organisation Code] = tbl_Contracts.Organisation
GROUP BY tbl_Contracts.ContractID, tbl_Organisations_And_Sites.[Organisation Name], tbl_ContractType.ContractType, tbl_Contracts.StartDate
HAVING (((Sum(tbl_ServiceLine.ServiceValue)) Like [Forms]![frm_ContractSearch]![txt_FinancialFilter]));

I am however now thinking along the lines of what David suggested, but using Min & Max textbox values in a between clause. If I set the default values at 0 and 99999999999 then it should pick up all contracts, The only downside is that contracts would be excluded where contract values had not been agreed and had NULL fields.

 

[ianward]

Thanks all who have contributed - I have now decided to go with the Min and Max values in a between clause and using NZ(ContractValue,0) to show zero value so all contracts are now shown.

Would be interesting though to see if there is a way of passing < > = and a numeric value to a query.

Thanks again - Ian

 

[Atomic Shrimp]

The problem is that you're asking your query to return rows with criteria something like:

Like >10000

Obviously you can't use the Like operator in that way.

I think what you probably need to do is build the whole SQL expression in your code, then push it into your form or report as the record source.

So you'd have a bit of code that does something like this:

StrSQL = "SELECT tbl_Contracts.ContractID, tbl_Organisations_And_Sites.[Organisation Name], tbl_ContractType.ContractType, tbl_Contracts.StartDate, Sum(tbl_ServiceLine.Activity) AS Activity, Sum(tbl_ServiceLine.ServiceValue) AS [Value]
FROM tbl_Organisations_And_Sites INNER JOIN (tbl_ContractType RIGHT JOIN (tbl_Contracts LEFT JOIN tbl_ServiceLine ON tbl_Contracts.ContractID = tbl_ServiceLine.ContractID) ON tbl_ContractType.ContractTypeID = tbl_Contracts.ContractType) ON tbl_Organisations_And_Sites.[Organisation Code] = tbl_Contracts.Organisation
GROUP BY tbl_Contracts.ContractID, tbl_Organisations_And_Sites.[Organisation Name], tbl_ContractType.ContractType, tbl_Contracts.StartDate
HAVING (((Sum(tbl_ServiceLine.ServiceValue)) " & Me.txt_FinancialFilter & txt_FinancialValue & "));"

'then code to push strSQL into the recordsource property of the object where you want it to run - so something like:
Me.RecordSource = strSQL

'then code to requery the object - something like:
Me.Requery