Parameter Queries that query more than one field

[JaysinM]

I am setting up a database that has 5 seperate date fields per record.  I need to set up a parameter query that will allow the user to input a beginning & ending date that will retrieve all records that fall between the dates.  I have it set up to where it will do this with just one field but I need to know how to set it up where it will search all 5 fields that have dates.  Can anyone help??  Thank you

 

[jgarnick]

Have you tried using the between statement in the criteria line of the first date and use the same statement on the OR line in the query for each date field?&nbsp;&nbsp;I haven't tried that but just a thought--- <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[Cornerstone]

Simply create your query. Add all of the date fields. Then add your parameter to each field using the or rows.<br><br>For example the criteria for Date1 would be:<br>between [Enter start date] and [Enter end date]<br><br>Then put the exact same criteria in for the or rows of the other date fields. Your query will return all of the records where any one of those dates falls within the parameter.

 

[JaysinM]

Ive tried doing that and it just gives me an error stating that the expression was too complex....any other ways I can accomplish this or any suggestions??

 

[elizabeth]

paste your SQL here...

 

[JaysinM]

Ive never used any SQL so I quite honestly cant say Im too familiar with what you're talking about hehehe. sorry.<br><br>Jaysin

 

[elizabeth]

Look at your query in the query grid. Then click on View/SQL. This is the SQL Access creates for you behind the scenes. Copy and paste that here.

 

[JaysinM]

SELECT [Father's Business Database].SSN, [Father's Business Database].[First Name], [Father's Business Database].[Last Name], [Father's Business Database].Food1, [Father's Business Database].Food2, [Father's Business Database].Food3, [Father's Business Database].Food4, [Father's Business Database].Clothing, [Father's Business Database].CoatsDate, [Father's Business Database].CoatsNumber<br>FROM [Father's Business Database]<br>WHERE [Father's Business Database].Food1 Between [Type the beginning date:] And [Type the ending date:] OR [Father's Business Database].Food2 Between [Type the beginning date:] And [Type the ending date:] OR [Father's Business Database].Food3 Between [Type the beginning date:] And [Type the ending date:] OR [Father's Business Database].Food4 Between [Type the beginning date:] And [Type the ending date:];

 

[JaysinM]

I got part of it fixed and it works alright when I have it checking only the first 2 fields (Food1 & Food2) but when i added on Food3 and Food4 Im getting errors again.&nbsp;&nbsp;Any ideas?&nbsp;&nbsp;Would it be because Food3 and Food4 are blank in all the records right now or something and if so what can i do to fix this??

 

[elizabeth]

I guess that is too complex for Access. Try this: Create a form Form1. Add two unbound textboxes named Begin and End. Set format to short date. Enter dates and while form is open, run your query modifield in this way:<br><br>SELECT [Father's Business Database].[SSN], [Father's Business Database].[First Name] AS Expr1, [Father's Business Database].[Last Name] AS Expr2, [Father's Business Database].[Food1] AS Expr3, [Father's Business Database].[Food2], [Father's Business Database].[Food3] AS Expr4, [Father's Business Database].[Food4], [Father's Business Database].[Clothing] AS Expr5, [Father's Business Database].[CoatsDate], [Father's Business Database].[CoatsNumber]<br>FROM [Father's Business Database]<br>WHERE ((([Father's Business Database].[Food1]) Between forms!Form1!Begin And forms!Form1!End)) Or ((([Father's Business Database].[Food2]) Between forms!Form1!Begin And forms!Form1!End)) Or ((([Father's Business Database].[Food3]) Between forms!Form1!Begin And forms!Form1!End)) Or ((([Father's Business Database].[Food4]) Between forms!Form1!Begin And forms!Form1!End));<br><br>This will work, but if each Food field needs a separate set of dates you'll have to add more to the form to accomodate. If you are creating a report based on this query you can check to make sure the form is open before you launch the report.

 

[dvenkat]

Hi

I am setting up a parameter query with 5 list boxes. I need help with the following-

How to set up the parm query so that user can choose any combo he / she wants and the query will retrieve records based on user options.

How do I allow multiple selections in each list box.

How do I show all records. I am unable to use * since one of the list boxes is a Date field.

I will appreciate any input. I am totally new to Access and greatly need your help.