Access WildCard for Date

  • Thread starter OldManRiver via AccessMonster.com
  • Start date
O

OldManRiver via AccessMonster.com

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
 
R

Rick Brandt

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
Click to expand...

Dates are not strings and only strings can use wild cards. Use something
like...

SELECT *
FROM TableName
WHERE DateField >= DateSerial(YearValue, MonthValue, 1)
AND DateField < DateSerial(YearValue, MonthValue + 1, 1)

With the example above use your form reference or function to supply
YearValue and MonthValue. They both need to be integers so month value
will be 1- 12, not January - December.
 
O

OldManRiver via AccessMonster.com

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
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of using the Get_Month() and Word() functions why don't you just
make the ComboBox "cboxMON" Properties like this:

Row Source Type: Value List
Row Source: 1;January;2;February;3;March;4;April; ... etc.
Column Count: 2
Column Widths: 0";1"
Bound Column: 1

Now when the user selects a month name the month number will be returned
by the ComboBox (Bound Column = 1). Setting the first column's width to
0" hides the month number - the user only sees the month name (the
second column with a width of 1").

Now you don't need the Get_Month() and Word() functions. All you have
to do to get the month number is

MonStr = Format(Me!cboxMON,"00")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZjRxIechKqOuFEgEQKNYACgrPRIW2QkHGZ+eF39L/u+ejbQoJsAni4d
UGTtMzo58rQ1a57yvy58Mlcy
=Jwwu
-----END PGP SIGNATURE-----

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
Click to expand...
 
Top