Multiple Entry Date Range Search

[setnaffa]

I have a lot of IT experience; but I'm almost a complete newbie on actually using databases.

Assume a table something like this:

Serial, DateIn, DateOut, Reason, OrgName, Location, Notes




Assume I want to look up by a date range and location

QDate1, QDate2, QLocation


How would I list the whole record for all records that match.


I assume doing it by OrgName instead of Location would be a simple replacement.


This is sort of my first real use of Access, other than an alternate sort of spreadsheet. Please be gentle.

[setnaffa]

Looking through other threads, I cam across a potentially similar question (Please reply if you have a better or more elegant solution, thanks!!):

WHERE SCCODBC_ORDERS.COLLECT_DATE>#8/3/2014# AND (SCCODBC_ORDERS.TEST0 In ("LUAN2","MPSS","PRELE") OR SCCODBC_ORDERS.TEST1 In ("LUAN2","MPSS","PRELE") OR ...)



Which maybe I can modify to

SELECT Serial, DateIn, DateOut, Reason, OrgName, Location, Notes

FROM Table2

WHERE Location = QLocation AND ((DateIn BETWEEN QDate1 and Qdate2) OR (DateOut BETWEEN QDate1 and Qdate2))


Or for Organization:

SELECT Serial, DateIn, DateOut, Reason, OrgName, Location, Notes

FROM Table2

WHERE OrgName= QOrgName AND ((DateIn BETWEEN QDate1 and Qdate2) OR (DateOut BETWEEN QDate1 and Qdate2))

[SMC]

In the form header create four text boxes and a command button. The four textboxes will be used to enter dates so format appropriately.

Call the first textbox "DateStart1" and the second "DateStart2"
The third will be, "DateEnd1" and the fourth, "DateEnd2"
Call the command button, "Datecmd"
Assume the form is called, "FFFFF", the query, "QQQQQ" and the table, "TTTTT".

Create "QQQQQ" from "TTTTT" and link in the columns you want to look at, QDate1, QDate2, and QLocation.

Under QDate1, paste this under criteria:

Between IIf(IsNull([Forms]![FFFFF]![DateStart1]),#1/1/0001#,[Forms]![FFFFF]![DateStart1]) And IIf(IsNull([Forms]![FFFFF]![DateStart2]),Date(),[Forms]![FFFFF]![DateStart2])

Under QDate2, paste this under criteria:

Between IIf(IsNull([Forms]![TFFFFF![DateEnd1]),#1/1/0001#,[Forms]![FFFFF![DateEnd1]) And IIf(IsNull([Forms]![TFFFFF![DateEnd2]),Date(),[Forms]![TFFFFF![DateEnd2])

These two codes will allow you to leave any of the fields blank to filter results even further based on your needs. Play around with them and you will surely understand their logic quickly.

Now back to the Form, FFFFF. go to the properties for the button we called, "Datecmd" and create a macro for the simply opening the query, QQQQQ.

You can also create a button to reset all four fields quickly by just writing code to set all the values for each box to an empty string, for example; DateStart1="".

[setnaffa]

In the form header create four text boxes and a command button. The four textboxes will be used to enter dates so format appropriately.

...

Thank you, I will try that tonight. But I may not have been completely clear. What I am looking to create may end up looking like this: