Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

text box contains ">5" for use in query, does not work 1

Status
Not open for further replies.

DSburgh

MIS
May 19, 2005
26
US
I want the user to be able to type in < > or = and a number into a text box and use that expression in the where clause of a query. However, it seems that whenever there is an operator in the text box it doesn’t return any values. Is there any way to have a user select between < > or = and type in a number? Should I just make one text box for each operator?

SQL:
SELECT Distributor_Stats.HD_DIST_NAME, Distributor_Stats.DIST_ID, Distributor_Stats.PROMO_COUNT,
FROM Distributor_Stats
WHERE (((Distributor_Stats.PROMO_COUNT) Like IIf([forms]![frm_resub_report]![txtpromo]="ALL","*",[forms]![frm_resub_report]![txtpromo].[value])));
 


Hi,

You want
[tt]
WHERE (((Distributor_Stats.PROMO_COUNT) Like '>5'
[/tt]
???

I doubt it.

Rather something like...
[tt]
WHERE (((Distributor_Stats.PROMO_COUNT) iif(.....," Like ""*""",[forms]![frm_resub_report]![txtpromo].[value])))
[/tt]


Skip,
[sub]
[glasses] [red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![tongue][/sub]
 
I changed the where statement to:
IIf([forms]![frm_resub_report]![txtpromo]="",Like "*",[forms]![frm_resub_report]![txtpromo].[value])
and now it says "The expression is typed incorrectly or is too complex to evaluate". Do you have to have the LIKE outside of the iif, i dont see why it wouldnt work with just an iif.
 


I assume that the two textbox results are...

ALL

>5

Please type out the two possible expressions that would result from the iif() statement.
[tt]
WHERE (((Distributor_Stats.PROMO_COUNT)

WHERE (((Distributor_Stats.PROMO_COUNT)
[/tt]


Skip,
[sub]
[glasses] [red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![tongue][/sub]
 
There are an infinate number of possibilities. I want it to return all values if blank or '<' any number, '>' any number, or '=' any number.
 


common, work with me!

In the IIF statement.......

if your textbox has All, what WHERE statement should occur?

if your textbox has >5, what WHERE statement should occur?

Skip,
[sub]
[glasses] [red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![tongue][/sub]
 
text box = "ALL", where should be *
text box = ">5", where should be >5
text box = "<5", where should be <5
text box = "=5", where should be =5
 
The problem look slike you need a different operator depending on the case, and this operator has to be in the string.

I would write a function to erturn the completed string, and use that in your query's criteria row.


function foo() as string

select case forms!MyForm!MyField
case "ALL"
foo = " Like '*';"
case else
foo = forms!MyForm!MyField.Value
end select


 


Which is exactly what I was attempting to have you discover...

SkipVought said:
Rather something like...

WHERE (((Distributor_Stats.PROMO_COUNT) iif(.....," Like ""*""",[forms]![frm_resub_report]![txtpromo].[value])))



Skip,
[sub]
[glasses] [red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![tongue][/sub]
 
SKIP,

I put what you typed as where clause (without the .....) and it give me "the expression is mistype or is too complex to evaluate" error. I am trying to save this as a query, not in VB.
 
In a standard code module create the following function:
Code:
Public Function myEval(myField, myTest) As Boolean
If Trim(myTest & "") = "" Or myTest = "ALL" Then
  myEval = True
Else
  myEval = CBool(Eval(myField & myTest))
End If
End Function

And now your WHERE clause:
WHERE myEval(Distributor_Stats.PROMO_COUNT,[Forms]![frm_resub_report]![txtpromo])=True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top