Parameter queries using Multiple Between....And

[JANET COOK]

I have created a query so that two fields have a range in them i.e.

One Field called "Price"
There are two text boxes on the seach form called "price1" and "price2"
The query reads

Between [price1] And [price2]

A second field called "Date Ordered"
There are two text boxes on the search form called "date1" and "date2"
Field called "Date Ordered"
Between [date1] And date2]

I have used the AND operator to join the first and second range. The user will fill in the criteria from a form with text boxes. This form is unbound but as soon as they click on the Search button, it will open up the query with the parameters in as above.

However, if the user were to type in a price but not a date the query shows no results. Results are only shown if a price and a date are inputted.

The OR operator will work but it is not what we need. We need to show results if a price is entered but not a date and vice versa. The fields are coming from one table.

Please can anyone advise? The database is a library management system so the price and date are the price of any books and the date that the book was ordered.

Thank you in advance for any help or advise.

Janet

 

[Dennisk]

If you need to use multiple criteria in a query and some fields may be blank then enter this in the criteria field. When the query is next opened you will see that access has rearranged it for you.

Forms!YourForm!YourField OR Forms!YourForm!YourField is null

 

[JANET COOK]

Thank you for your reply. I probably did not explain the problem correctly but the fields will have data in them on the database for both Price and Date Ordered.

However, the librarian may only want to search for a price range at any one time without inputting a date range or vice versa. Sometimes she may want to use both criteria. As I have used the And for the two ranges you have to insert criteria for both Price and Date otherwise it does not show any results. If I insert criteria for both Price and Date the query shows the relevant results.

Thank you replying so quickly.

Regards

Janet

 

[EMP]

You can enter the criteria for the [Price] field in a new column in the query grid like the following (using the correct form name):-
.................................................
Field: [Price] Between [Forms]![FormName]![Price1] And [Forms]![FormName]![Price2] Or [Forms]![FormName]![Price1] Is Null

Show: uncheck

Criteria: True
..................................................
Now when [Price1] on the form is left blank, all the records will be returned by the query.

Similarly you can do the same for the [Date Ordered] field in another new column.


The above are derived from Jon K's basic criteria format in the thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

See also his Note 1 in the thread for his reason for not using something like the following in the Criteria row:-
Criteria: .............. OR Forms!FormName!ControlName is null

^

 

[Brianwarnock]

Using the query grid you need 3 lines in the criteria

Between [price1] and [price2] Between [Date1] And [Date2]
Between [price1] and [price2] [Date1] is null And [Date2] is null
[price1] is null and [price2] is null Between [Date1] And [Date2]

As you will be prompted for each occurrence of each parameter this should be run from a form.

Brian

 

[boblarson]

Field: [Price] Between [Forms]![FormName]![Price1] And [Forms]![FormName]![Price2] Or [Forms]![FormName]![Price1] Is Null

Not totally sure about this, but I THINK you would want to include parenthesis similarly the way a mathematical operation would so that the operations are checked in the correct order:

Field: [Price] (Between [Forms]![FormName]![Price1] And [Forms]![FormName]![Price2]) Or [Forms]![FormName]![Price1] Is Null

 

[boblarson]

Never mind, I don't think that would help. I think you get a syntax error.

 

[gemma-the-husky]

i would have a single column called includeme(field1, field2 etc), with a criteria of true

then you need a module function

function includeme(field1, field2 etc) as boolean

in the query criteria pass the fields you need testing. if you want to type them (probably best) then cast then as nz(fieldx) to get rid of the nulls

now in the includeme function you can test to any level of complexity you desire, and simply return true or false if you want to include or exclude the record. You can do a load more in a function than you can in a line of sql - including using various vba functions

 

[Jon K]

EMP's expressions work. There are no syntax errors. See the attached database.

And BETWEEN ... AND ... doesn't need to be put inside a pair of parenthesis.
.

 

[boblarson]

EMP's expressions work. There are no syntax errors. See the attached database.

And BETWEEN ... AND ... doesn't need to be put inside a pair of parenthesis.
.

Good to know...