i have a form that will serve as a search engine to filter through a query named Experiment.
it contains, amoung others, the fields Start Date and End Date. they are both formatted to date:general
i have three controls:
1. Dateselect: combobox with the field to filter by either Start Date or End Date
2. Datecompare: containing the values "Is", "Before", "After" and "Between"
3. Datecompared: textbox to enter the date value
i also wrote a vba code that weaves all this information into a SQL statement which is then sent to a query called Search
here's the code for the date filtering:
Dim dSelect As String
Dim dCompare As String
Dim dCompared As String
Dim strDate As String
strDate = "[" & dSelect & "]" & "=" & "#" & dCompared & "#"
'>=,=<, or =
Select Case dCompare
strDate = replace(strDate, "=", "<=")
strDate = replace(strDate, "=", ">=")
the result is very inconsistant. sometimes Search shows the correct results, sometimes it shows nothing at all. there are also times when it just shows all the records.
it always shows up empty if i use "=".
i made a message box popup before launching the SQL and i cant find any mistakes in it.
ex) for StartDate Before 05/07/2010
SELECT ID, Objective, Protocol, [Patch Soln], StartDate, EndDate, TotalTime, [Cause of Termination], Species, [Weight (g)], [Date of Registry], [Date Modified]
i think its a problem with the format..but thats only a hunch that led me to frustration and eventually here!
thanks for the link, now my "Before" and "After" are working but why is it that "=" still doesnt work?
Hi tehillati, I think probably because you might be recording a time rather than a date i.e. #08/20/2010 11:00# rather than #08/20/2010#. To eliminate this you'd have to compare CDate(Format([DateField], "mm/yy/yyyy")) with CDate(Format(dCompared, "mm/yy/yyyy")).