Is there a way to have a query perfom a search using an "or" criteria? For instance, the user enters in a range of dates to search between, i.e. 01/01/2001 and 12/31/2001, and the query would then search across 5 date fields for all records with dates in that range.

I see where I can perform the "or" under one field, but can it be applied to fields?

If you need more info, let me know and I'll be happy to provide what you need.

Thanks!

Bluez

Bluez,


Yes you can by putting the query parameters in the query grid in each field. You will need to put the 1st one in the first 'or' box, the second in the second row etc.

eg.
..........field 1.....field 2.....field 3 etc

Criteria..between..
or....................between..
..................................between..

I an not sure if you will need to add or is null to each of these if you have an empty field though.

This hasn't quite come out as I wanted. Ignore the dots!

[This message has been edited by Fizzio (edited 11-05-2001).]

[This message has been edited by Fizzio (edited 11-05-2001).]

I do not think this can be done except to put that criteria under all 5 fields.

I hope that you are using criteria from a form to give you the flexibily to enter the critieria just once at least.

Yep, I agrre and that is what I poorly tried to show, putting the criteria under each field but my diagram was a bit naff. I Definatey agree with a criteria form and a good example using the calendar control is in the solutions database.

Ok, I tried to put the criteria of "between ([begin date]) and ([end date]) on each of the fields, and it acts like an AND command, bringing back 0 records. If I put that same criteria under each individual field (one at time) I get multiple records each time I run it.

As far as using a form, I'm not quite sure what you mean. Do you mean to create form based the query and having unbound combo boxes for each field, one for begin date and one for end date? If you could show me a sample it might make more sense to me.

One last thought, is there perhaps VBA code that might help here?

Bluez

Put the first Criteria in the first line. Place the next Criteria in the second line down. The third criteria in the third line down... this will give you OR rather than AND. Look at Fizzio's example again...

Ok. Let's address the flexibility problem here first. The way your query is set up now, the user would have to enter the parameters of the date 5 times! I know you would like to improve upon this.

This is how to base your query off a form:

1) Create an unbound form -- frmSearch

2) In design view, put two controls on the form (they will be Text0 and Text1 for demo purposes)

These will be where the user enters the parameters for the dates.

3) Put a command button that runs your query (for demo purposes the qry is qryLastName)

Now open your query in design view:

Take out all the criteria you had in there previously.

Under Date1 and in the Criteria Row, click the build button and create a formula like so...

>(Forms!frmSearch![Text0]) And <(Forms!frmSearch![Text1])

When you run the query, records where Date1 are present that are within the parameters of what is entered on the frmSearch form will be returned.

Try this with one field first. Then on the next try, put the same formula on the OR lines, you must drop down a line under OR for each field.

Sent you a demo. Take a look. It is just a quick throw together, but will demonstrate the way to make this query based off of a form.

Aha!! Draw me a picture and I begin to undertand. Thank you jwindon and all the rest for helping me to see this. It all makes sense now.

Will try out this new found knowledge and report back :-)

Bluez