Help with multiple query fields....including Between/And

[Kevo]

Hi,
I have read through many posts but still cant figure this out....

I made a search form that includes
-StartDate EndDate
-Machine#

(and would liek to add a few more)

I would like the user to be able to - Enter 2 dates and have it show all records between dates - and/or enter machine# and see records associated with that machine......and a few other things (which I will worry about once I get these two to work)

I have a query in design view that has

LoadDate
Between StartDate And EndDate

Machine#

Like Machine# &*

But this isn't working as expected.....what am I missing.

Here is the actual code I used....note: the field names I used above were for simplicity in explaining.....

SELECT tblLoad.LoadID, tblLoad.LoadDate, tblLoad.AutoclaveID, tblLoad.LoadNumber, tblLoad.BI, tblLoad.[Process Challenge Device], tblLoad.[Cycle Complete], tblLoad.[Cycle Aborted]
FROM tblLoad
WHERE (((tblLoad.LoadDate) Between [Forms]![frmSearchMenu].[qLoadDate1] And [Forms]![frmSearchMenu].[qLoadDate2]) AND ((tblLoad.AutoclaveID) Like [Forms]![frmSearchMenu].[qLoadAuto] & "*"));

[alansidman]

But this isn't working as expected.....what am I missing.

What happens when you run your query? Error message? Empty results? Unusual results?

Alan

[Kevo]

[/COLOR]
What happens when you run your query? Error message? Empty results? Unusual results?

Alan

If I put in a date range it comes back as expected....but it simply ignores the second query field.

[alansidman]

Instead of Like in the second half of the Where clause try equals and delete the asterisk. What happens when you try that?

[Kevo]

OK, I think the problem is something to do with putting the criteria in the "and"/ "or" spot............

I put the second criteria next to the first ("and" spot) and it ignores it.....put the second criteria in the "or" spot and it does either or but not both?????


I want it to query between the 2 dates IF someone enters dates.....OR go to the next criteria and look for the Autoclave number and search records with that autoclave......But if someone puts in a date range AND an autoclave number it should return all records between those dates with that autoclave number....

What am I missing?

[alansidman]

Is the autoclave number being entered manually or are you using a combo box that has a listing of the autoclave numbers?

[Kevo]

Is the autoclave number being entered manually or are you using a combo box that has a listing of the autoclave numbers?

Autoclave is a value already in a record.

[orange]

Even if autoClave number is in the record, someone has to enter something to find a record with a specific autoClave number.

You should be working with a Form. A form that gathers parameters, and that will execute the appropriate query based on the selections made on the Form.

see Martin Green's tutorials at http://www.fontstuff.com/access/acctut17.htm

[alansidman]

What I was referring to was the form where you are entering the parameters. Is the text box (qloadauto) input manually, ie. typed in? or is it selected using a combo box?