Parameter query

[emmakate1300]

We are wanting to run a query that lets users input either (eg >10) or two
numbers using the between/and function.

However when we use between/and, it doesn't exlude negative numbers for some
reason, and when we use a simple parameter, and type in >10, it doesn't
return any results.

Does anyone have any ideas?

 

[Smartin]

We are wanting to run a query that lets users input either (eg >10) or two
numbers using the between/and function.

However when we use between/and, it doesn't exlude negative numbers for some
reason, and when we use a simple parameter, and type in >10, it doesn't
return any results.

Does anyone have any ideas?

A parameter query can't parse logic (e.g., ">" means greater than or
two numbers means "between") in the input. If you want this kind of
flexibility I suggest you create a form that can handle the scenarios
and build a query in VBA.

 

[Ken Sheridan]

Try:

SELECT *
FROM YourTable
WHERE YourNumber >= [Enter start number:]
AND (YourNumber <= [Enter end number:]
OR [Enter end number:] IS NULL);

The user can enter just the start number and leave the end number blank to
get all rows with values equal to or greater than the start number, or enter
both and get rows with values between the two.

Ken Sheridan
Stafford, England