Choosing dates that are between start and end dates!

[Danielle17]

I have a calendar control on one of my forms. I have the calendar programmed so that when the user clicks on a date and then clicks on a FindRecord command button all of the records that are on that day will pop up in subforms. It slipped my mind that I didn't program it so that if a 'job' record lasted longer then one day it wouldn't show up everyday that it was being done. EX. IF a job started on the 19th and it ran through until the 21st, and I clicked on the 19th that record would show up BUT if I clicked on the 20th or the 21st then the job would not show up. I know this does this because the calendar is programmed so that the date on the calendar has to equal the Start Date field in the query. I've been trying to use the Between and AND statement but I keep getting errors. I'll post the code and if anyone can help I'd really appreciate it. X-)

Private Sub cmdFindRecord_Click()

Dim tSQL As String
Dim eSQL As String
Dim zSQL As String

tSQL = "SELECT TechsInUse.ID, TechsInUse.[Technician Name], TechsInUse.JobID, TechsInUse.Technician, zJobTable.[Start Date], zJobTable.[End Date]" & _
" FROM TechsInUse INNER JOIN zJobTable ON TechsInUse.JobID = zJobTable.ID" & _
" WHERE (((zJobTable.[Start Date])=#" & cldMonth.Value & "#));"


Me.Technicians_Query_subform.Form.RecordSource = tSQL
Me.Technicians_Query_subform.Form.Requery

eSQL = "SELECT DISTINCTROW EquipmentUsed.JobID, EquipmentUsed.EquipmentID, zJobTable.[Start Date], zJobTable.[End Date]" & _
" FROM zJobTable INNER JOIN EquipmentUsed ON zJobTable.ID = EquipmentUsed.JobID" & _
" WHERE (((zJobTable.[Start Date])=#" & cldMonth.Value & "#));"

Me.EquipmentUsed_Query_subform.Form.RecordSource = eSQL
Me.EquipmentUsed_Query_subform.Form.Requery

zSQL = "SELECT zJobTable.ID, zJobTable.[Start Date], zJobTable.[End Date], zJobTable.Customer, zJobTable.[Phone #], zJobTable.Contact, zJobTable.[PO#], zJobTable.Description, zJobTable.Notes" & _
" FROM zJobTable" & _
" WHERE (((zJobTable.[Start Date])=#" & cldMonth.Value & "#));"

Me.zJobTable_Query_subform.Form.RecordSource = zSQL
Me.zJobTable_Query_subform.Form.Requery



End Sub

 

[Launcher6]

Don't know if this will help but I would use two text boxes one for BeginDate and one for EndDate, then use a label (lblNum) for a number identification.
Then use a if statement such as this:

Private Sub calendar_click()
If lblNum.Caption = 1 then
BeginDate.Value = Calendar.Value
lblNum.caption = 2
Elseif lblNum.Caption = 2 then
EndDate.Value = Calendar.Value
lblNum.value = 1
End if

End Sub

Simple setup but these actions work in my programs for a between beginning date and ending date.

 

[Danielle17]

I have the calendar setup so that the user chooses a date and then clicks the command button. Would I want to put the code that I have, on the Click event for the calendar? If I do use the text boxes would they affect the command button that operates the subforms?? Would these text boxes have to be visible?

 

[Launcher6]

No the text boxes do not have to be visible, yes you should use the click event for the calendar control to make the IF statement work.

As for the command button to show the subforms, I would suggest using query by example with the criteria for each query being "Between BeginDate and EndDate". Therefore you would have three subforms with three query by examples for each of these. If this does not make sense try make a query for each of these SQL and make the control source for the subforms the queries.

Hope I have been helpful, because until now I have been afraid my knowledge is not good enough to help others.

 

[Danielle17]

I understand what you're saying but I found an easier way to do it. I just rewrote the code a little different and then transfered all of it to the On Click event for the calendar. This way there are no text boxes and all the information pops up in the subforms. Thanks for your suggestions.