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

[DSburgh]

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])));

 

[SkipVought]

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]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[DSburgh]

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.

 

[SkipVought]

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]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[DSburgh]

There are an infinate number of possibilities. I want it to return all values if blank or '<' any number, '>' any number, or '=' any number.

 

[SkipVought]

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]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[DSburgh]

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

 

[markgrizzle]

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

 

[SkipVought]

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

Rather something like...

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

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

 

[Ashank4vba]

would something like a toggle button be simpler?

 

[DSburgh]

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.

 

[PHV]

In a standard code module create the following function:

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

 

[DSburgh]

PHV, that works beautifully. Thanks.