Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Query All Dates? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following lookup code in a form and it works OK

    If I enter 5/14/2004 it will return all the records for 5/14/2004
    If I enter 5/14 it will return all the records for 5/14/2004

    Is there a wild card like * I can enter to return all dates?

    How can I change the following so that it will return all dates?

    Thanks, John

    <pre>strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " = #" & strLookupCriteria & "# " & _
    " ORDER BY " & strSort</pre>


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How to Query All Dates? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You need to dynamically change the SQL to exclude the WHERE clause completely if that is the case, or you could change it to be "> #1/1/1900#" or something similar.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How to Query All Dates? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You can use Like criteria with a Date field, with date formatted as a String, only be advised that the results in some cases will be dependent on the user's Windows Regional Date/Time settings, as set in Control Panel. For example, on the PC I'm using, the Short Date format is defined as M/d/yyyy. Running query on date field, this expression:

    WHERE (((TABLE1.DATE_SER) Like "1/8/*"))

    correctly returned 4 records where date was Jan 8th (any year), but this expression:

    WHERE (((TABLE1.DATE_SER) Like "1/08/*"))

    returned no records. This caused me problems on more than one occasion till I figured out that different users had different Windows settings. In most cases, the expression:

    WHERE (((TABLE1.DATE_SER) Like "*/*/*"))

    will return all records where Date field is not Null. If you want to be safe, when using wild cards for Date criteria in a string with the Like operator, you can get the current user's Short Date format by querying the Registry and formatting the Date criteria string accordingly. See attached text file for sample code that can be used for this purpose. The GetShortDateFormat function will return the current user's Short Date format as stored in Registry under HKCU. Example:

    ? GetShortDateFormat
    M/d/yyyy

    The GetShortDateWildcard function replaces the formatting characters (m, d, y) with the specified wildcard character:

    ? GetShortDateWildcard("*")
    */*/****

    This function looks up the Date Separator character from Registry (usually an "/", at least in US) and replaces any other character with the wildcard. So if you wanted to be safe, you could use this function to determine what expression to use with Like to return all dates. But most of the time, "*/*/*" should work. It is only when using Like with partial date wildcard criteria that it is important to take into account the Windows Short Date format. The simpler approach (that I normally use) is to use Like criteria not with Date field directly, but using Format function in expression like this example:

    SELECT TABLE1.TaskID, Format([DATE_FLD],"mm/dd/yyyy") AS Expr1
    FROM TABLE1
    WHERE (((Format([DATE_FLD],"mm/dd/yyyy")) Like "01/*/*"))
    ORDER BY [DATE_FLD];

    This returned all records where month was January, although the format specified does not match the Short Date format specified for my system. Note you would sort results by actual date field, the formatted field will sort in alpha, not date, order.

    HTH
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Query All Dates? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks Wendell

    This rounds out general purpose lookup routine

    John

    <pre> If Left(cboLookupSequence, 3) = "dtm" Then
    Dim strOperator As String
    strOperator = "="
    If strLookupCriteria = "*" Then
    strLookupCriteria = DMin(cboLookupSequence, strTable)
    strOperator = ">="
    End If
    strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " " & strOperator & "#" & strLookupCriteria & "# " & _
    " ORDER BY " & strSort
    Else
    If Left(cboLookupSequence, 3) = "lng" Or Left(cboLookupSequence, 3) = "int" Then
    strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " = " & strLookupCriteria & " " & _
    " ORDER BY " & strSort
    Else
    strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " LIKE '" & strLookupCriteria & "*' " & _
    " ORDER BY " & strSort
    End If
    End If
    </pre>


  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Query All Dates? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks Mark

    I will keep this on file as a future solution.

    John

Posting Permissions

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