Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010

    Unanswered: using a date as criteria for query

    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

    dSelect = Me.DateSelect.Value
    dCompare = Me.DateCompare.Value
    dCompared = Me.DateCompared.Value

    Dim strDate As String
    strDate = "[" & dSelect & "]" & "=" & "#" & dCompared & "#"

    '>=,=<, or =
    Select Case dCompare
    Case "Before"
    strDate = replace(strDate, "=", "<=")
    Case "After"
    strDate = replace(strDate, "=", ">=")
    Case "Is"
    End Select

    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]
    FROM Experiment
    WHERE StartDate<=#05/07/2010#;

    i think its a problem with the format..but thats only a hunch that led me to frustration and eventually here!

    any ideas?

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6

  3. #3
    Join Date
    Aug 2010
    thanks for the link, now my "Before" and "After" are working but why is it that "=" still doesnt work?

  4. #4
    Join Date
    Oct 2004
    Oxfordshire, UK
    Quote Originally Posted by tehillati View Post
    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")).

  5. #5
    Join Date
    Aug 2010
    ok its working fine now. thanks a lot for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts