Wierd Date Between Query

[ThaBizness]

I have a backwards date between query. I have a table with Date1 and Date2 fields. On the form I have a calender control. I want the user to click a date on the calender control and then look at the rows in the table to see if that date is between the Date1 and Date2 fields.

So the query will be (in english) get all rows that CalenderDate1 is between Date1 and Date2

Im stuck. Someone give me direction.

 

[ScottGem]

Set the Criteria in the query to

Between Forms!formname!Date1 AND Forms!formname!date2

 

[RuralGuy]

Something like:

Where [MyTable].[Date1] >= [Forms].[MyForm].[MyDate]
  AND [MyTable].[Date2] <= [Forms].[MyForm].[MyDate]

 

[ThaBizness]

Scott, The date1 and date2 are not on the form. On the form is a listbox which will contain the results (the rows that are within the selected date) and the calendar control. I do have a text box that displays the selected date so I can use that. To rePhrase:

Get all rows that CalenderDate is between Date1 and Date2 from the dates table.

 

[ScottGem]

Scott, The date1 and date2 are not on the form. On the form is a listbox which will contain the results (the rows that are within the selected date) and the calendar control. I do have a text box that displays the selected date so I can use that. To rePhrase:

Get all rows that CalenderDate is between Date1 and Date2 from the dates table.

If you want get all records (spreadsheets have rows, database tables have records) from a table where a date falls within a range of dates then you NEED to supply the start and end dates as criteria to a query. Generally you use the BETWEEN operator to list the beginning and ending ranges.

 

[ThaBizness]

Didn't work. Here is my SQL. Where did I go wrong?

SELECT CheckOutTbl.checkId, CheckOutTbl.checkDate, CheckOutTbl.checkDateRet, teacherTbl.teacherFirst, teacherTbl.teacherLast, bookTbl.bookName
FROM teacherTbl INNER JOIN (bookTbl INNER JOIN CheckOutTbl ON bookTbl.bookId = CheckOutTbl.bookID) ON teacherTbl.teacherId = CheckOutTbl.teacherId
WHERE (((CheckOutTbl.checkDate)>=[Forms]![Calender]![txtDate]) AND ((CheckOutTbl.checkDateRet)<=[Forms]![Calender]![txtDate]));[CODE]

[/CODE]

 

[ScottGem]

What didn't work? You need to be more descriptive. However that query will only return records where the date is equal to the the txtDate control.

 

[ThaBizness]

Thanks for your help Scott. My database is to collect information about book check outs. In addition to the other fields tracking information I have a date1 and a date2 field. Date1 is checked out and Date2 is date of return. I have a List box and a calender control on the form. A textbox is mapped to show the date selected in the calender. When a user clicks a date I want to requery the listbox to only show records that the calender date falls between Date1 and Date2

 

[ScottGem]

Ahh, now that is a different story. I misunderstood. Then what RuralGuy gave you should work.

However, you structure is not properly normalized. What you should have is a transactions table like this:

TransactionID (PK Autonumber)
BookID (FK)
TeacherID (FK)
TransDate
TransType (In/Out)

 

[ThaBizness]

Ok but why is the query showing me nothing?

 

[ScottGem]

Don't know, can you post a stripped down copy of your data?

 

[Pat Hartman]

Where Forms!YourForm!YourDate Between YourTable.Date1 and YourTable.Date2