Access WildCard for Date

[TBotNik]

All,

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

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

[timothybard]

You are taking the [cboxMon] value and [cboxYer] value and trying to create a generic date, or a date range, and comparing the result with [tim_ted].

I would recommend breaking up [tim_ted] into a month and year and comparing those to [cboxMon] and [cboxYer].

For example, use ([cboxMon] = month([tim_ted]) AND [cboxYer]=Year([tim_ted])) in the WHERE statement.

[TBotNik]

All,

I tried several things but finally got this working with:

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:

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:

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