I received a question from Cesar today:

CESAR:

hi, i need help with the calendar seminar, i created a textbox for a query like a startdate but, i want to show only register that i need for this i put in the properties: SELECT CalendarID, apptime, Description, Hours FROM CAlendarQ WHERE Description = Forms!DatePickerF!Class AND DateTime = Forms!DatePickerF!StartDate and DateTime Forms!DatePickerF!StartDate+1 ORDER BY apptime;
But i want a restriction if i leave the text box empty i want to show them all. How can i do that?
thank you so much

MY REPLY:

First remember that date/time values in an SQL statement need to be enclosed in # signs, so you have:

S = "SELECT * FROM CalendarT WHERE StartDate>#" & Date1 & " AND EndDate<=#" & Date2 & "#"

Now, if you want to show ALL calendar entries are the Date1 and Date2 fields are left blank, you'll need to check for them:

If IsNull(Date1) AND IsNull(Date2) Then
  S = "SELECT * FROM CalendarT"
Else
  S = 'above statement
End if

Easy enough? For more tricks like this, I recommend my SQL Seminar and/or my Search Seminar which both go into a LOT of custom SQL writing.