Parameterised/Unparameterised Query (1 Viewer)

[gazsharpe101]

Hi, I was wondering if anyone can help me.

I am running a parameterised query on which a report is based. The report shows all invoices raised for a particular amount, which the user specifies before the report is run. What I would like is that if they wanted to show all invoices raised regardless of the amount then they would enter nothing or perhaps "all" in this box i.e. remove the parameterised part from the query.

Is this possible to do?

Thanks for any help.
Gareth.

 

[tehNellie]

On your parametised field a quick, dirty way to do this is to amend

[enter a value]

to be

like "*" & [enter a value] & "*"

If they enter nothing at all they will get all records, they can also enter partial values so using 1 would return anything with
1, 100, 101,111, etc

 

[gazsharpe101]

Thanks for your reply.

What if I wanted them to enter for example 300 and only show the results including 300 exactly and not 3000 because wouldn't that show 3000 or 30000 etc?

 

[neileg]

I think you're going to have to build the query in code and test each parameter in turn.

 

[gazsharpe101]

Ok, how do I go about that? I have basic knowledge of VBA but nothing more than that.

Thanks again for your help.

 

[Simon_MT]

I would use between, if launching off a form. I would put default values Start as Minimum Invoice value and End Maximum Invoice value. On Update of Start set End to Start Value

If nothing input 0 - 999999

Otherwise whatever the user inputs.

You could but in your Query Invoice Value Between [Please enter Minimum Value] and [Please Enter Maximum Value]

Simon

Simon

 

[gazsharpe101]

I'm not sure I understand what you mean. I am not launching it from a form it's just a report based on the query.

 

[EMP]

See Jon K's method in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

You can replace his [Forms]![FormName]![ControlName] with your parameter.

^

 

[gemma-the-husky]

yuor query can have two "or" rows specifying the selection criteria.

Rather than take the parameter at run time within the query, as you are, enter it in a text box in a form.

then your query can specify two different "or" criteria rows

row 1 where invoice value = form.textbox value
row 2 where form.textboxvalue = 0

you can save the textbox value into a variable, and read it from the variable in the query if that is easier.

note that access may be able to simplify the query design into a single criteria row, after you save it.