Don wrote:
strMySQL = strMySQL & "And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)" & vbCrLf
strMySQL = strMySQL & "Or ([" & InventorySource & "].COLLDATE BETWEEN
Forms.frmMainSearch.STARTDATE AND
IIf(NZ(Forms.frmMainSearch.ENDDATE),#12/31/2999#))" & vbCrLf
If you are using code to construct your SQL, why bother with query by form?
What I do is this.
I have two text boxes on my form, one txtFrom, the other txtTo. The
idea being users can enter two dates for a range, or leave txtTo empty
and have all results after and on the date in txtFrom, or leave txtFrom
empty and have all results upt to and on the date in txtTo.
Your section of your SQL construction that deals with the date would
simply be (this is air code and I assume there is more stuff in the
where clause before we get to the dates - I also have lots of line
continuations here so we don't wrap too much - chr(35) is the # sign):
strMySql = "SELECT <whatever"
strMySql = strMySql & "FROM <whatever"
strMySql = strMySql & "WHERE <whatever"
with Forms.frmMainSearch
if not isnull(.txtFrom) and not isnull(.txtTo) then
'Both date boxes are filled, so it's a range
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE >= " & _
chr(35) & .txtFrom & chr(35)
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE <= " & _
chr(35) & .txtTo & chr(35)
Elseif not isnull(.txtFrom) and isnull(.txtTo) then
'Only from date specified
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE >= " & _
chr(35) & .txtFrom & chr(35)
elseif isnull(.txtFrom) and not isnull(.txtTo) then
'Only To date is specified
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE <= " & _
chr(35) & .txtTo & chr(35)
end if
end with
This also allows you to run your query for all dates, ie, the user does
not put any date range into it.
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me