Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Access WildCard for Date

    All,

    Trying to get a date WHERE statement to run to pull all records for the month. What I have is:

    Code:
        Dim SQL_Str, DatStr, MonVal
        MonVal = Get_Month(TargetForm![cboxMON])
        DatStr = MonVal & "/*/" & TargetForm![cboxYER]
        SQL_Str = "SELECT * FROM qryREPrev WHERE ([tim_ted]= like #" & DatStr & "#)"
        Set Wspace = DBEngine.Workspaces(0)
        Set dbs = CurrentDb
        Set rsS = dbs.OpenRecordset(SQL_Str, dbReadOnly)
    and I get error when the OpenRecordSet executes.

    Oh Date in the field "tim_ted" is in format "00/00/0000" and values coming from the screen are "TargetForm![cboxMON]" = long month name like "October" and "TargetForm![cboxYER]" = long year like "2009". Would also like to make my code generic enough to handle field of "00/00/00".

    Is there a good Wildcard way to do this or is there another way?

    OMR

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wildcard work on string data types in all falvours of SQL, not date datatypes
    try instead somethign like

    where month(mydatecolumn) = amonth and year(mydatecolumn)=ayear

    or

    where format(mydatecolumn,"mm/yyyy") like amontheyarvalue

    or
    do a date between clause
    where mydatecolumn between (startdate and enddate)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Finally Working

    All,

    I tried several things but finally got this working with:
    Code:
        Dim SQL_Str, MonStr, MonNxt, YerStr, RevSht
        MonStr = Get_Month(TargetForm![cboxMON])
        MonNxt = MonStr + 1
        YerStr = TargetForm![cboxYER]
        SQL_Str = "SELECT * FROM qryREPrev WHERE (([thd_ted] >= DateSerial(" & YerStr & _
                  ", " & MonStr & ", 1)) AND ([thd_ted] < DateSerial(" & YerStr & ", " & _
                  MonNxt & ", 1))) ORDER BY [thd_ted];"
        Set Wspace = DBEngine.Workspaces(0)
        Set dbs = CurrentDb
        Set rsS = dbs.OpenRecordset(SQL_Str, dbReadOnly)
    Had to create the function "Get_Month" which is:
    Code:
    Function Get_Month(MyMon)
        Dim MonStr As String, CurMon, MonVal
        MonStr = "January; February; March; April; May; June; July; August; September; October; " & _
                 "November; December"
        For N = 1 To 12
            CurMon = Word(N, MonStr)
            If InStr(1, CurMon, MyMon) > 0 Then
               Get_Month = Right("00" & N, 2)
               Exit For
            End If
        Next N
    End Function
    And you will get error is you do not load the REXX runtime module for word or create this additional function:
    Code:
    Public Function Word(MyWrdPos, MyInStr As String) As String
        Dim SPos As Integer, Done As Integer, SLen As Integer, WrdCnt As Integer
        Dim StPos As Integer, EdPos As Integer, x%
        SPos = 0
        StPos = 0
        EdPos = 0
        WrdCnt = 0
        MyInStr = Trim(MyInStr)
        SLen = Len(MyInStr)
        Do While Done = 0
            For x% = 1 To SLen + 1
                SPos = InStr(x%, MyInStr, " ", 1)
                If SPos <> 0 Then
                    WrdCnt = WrdCnt + 1
                    If WrdCnt = MyWrdPos Then StPos = x%
                    If WrdCnt = MyWrdPos + 1 Then EdPos = x% - 1
                    x% = SPos
                    If StPos > 0 And EdPos > 0 Then
                        Done = 1
                        Exit For
                    End If
                Else
                    Done = 1
                    If StPos = 0 Then
                        StPos = x%
                        x% = SLen
                        EdPos = SLen + 1
                    End If
                    If x% < SLen Then EdPos = x% - 1
                    If EdPos = 0 Then EdPos = SLen
                    Exit For
                End If
            Next x%
        Loop
        If StPos > 0 And EdPos > 0 Then Word = Mid(MyInStr, StPos, (EdPos - StPos))
    End Function
    Thanks All for your ideas and help!!

    OMR

Posting Permissions

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