Date search

[John Bull]

I am trying to do a search for dates in the three letter format. That is I need the database to look through a memo field and find strings such as "NOV", "DEC". It needs to locate the positions ( using instr function) of these strings on the condition that these months are close to the current month.
For example, we are in November now, so it would search for OCT, DEC & JAN.
Any ideas would be appreciated.

 

[kaspi]

I suppose you could use variables for each month you want to search for. Eg.

dim strMonth1 as string
dim strMonth2 as string
dim strMonth3 as string

use the now() function to format the search strings,

strMonth1 = format(now,"mm")
strMonth2 = format(now,"mm")
strMonth3 = format(now,"mm")

Now you should be able to search for your three months using instr.

 

[Robin]

Hello John

Try these functions, they need to be modified but you can get the idea.

Public Function FindPos()

On Error GoTo FindPos_Err

Dim db As Database
Dim rs As Recordset
Dim SearchString As String
Dim SearchChar1 As String
Dim SearchChar2 As String
Dim MonthPos1 As Long
Dim MonthPos2 As Long
Dim mnth

'Load a variable with the month value of today's date
mnth = Month(Date)

'Set a variable with the current database handle
Set db = CurrentDb()

'Set up a recordset
Set rs = db.OpenRecordset("tblData", DB_OPEN_DYNASET)

'Define the string to be searched
'from the field in the table
SearchString = rs("fldData")

'Select the string representation of the month
'by sending the month value 'mnth' and the direction
'and distance to move, in this case the direction is
'minus and the movement is one (lower value)
SearchChar1 = MonthName(mnth, -1)

'Select the string representation of the month
'by sending the month value 'mnth' and the direction
'and distance to move, in this case the direction is
'plus and the movement is one (upper value)
SearchChar2 = MonthName(mnth, 1)

'Find position of string value for last month
MonthPos1 = InStr(1, SearchString, SearchChar1, 0)

'Find position of string value for next month
MonthPos2 = InStr(1, SearchString, SearchChar2, 0)

'Here is where you have to use the info
MsgBox "First position is " & MonthPos1 & " and is " & SearchChar1 & vbCrLf & "Next position is " & MonthPos2 & " and is " & SearchChar2

Exit Function

FindPos_Err:
MsgBox Error$, 16, "Error"
Exit Function
End Function

Public Function MonthName(mnth As Variant, direct As Integer) As String

On Error GoTo MonthName_Err

'Here we find the string value for months
'by using a select case. Other cases need to
'be entered to handle December and January
'as these two are unique cases, but you get the idea.

Dim ans As Integer

ans = mnth + direct

Select Case ans
Case 1
MonthName = "Jan"
Case 2
MonthName = "Feb"
Case 3
MonthName = "Mar"
Case 4
MonthName = "Apr"
Case 5
MonthName = "May"
Case 6
MonthName = "Jun"
Case 7
MonthName = "Jul"
Case 8
MonthName = "Aug"
Case 9
MonthName = "Sep"
Case 10
MonthName = "Oct"
Case 11
MonthName = "Nov"
Case 12
MonthName = "Dec"
End Select

Exit Function

MonthName_Err:
MsgBox Error$, 16, "Error"
Exit Function
End Function

Good Luck
Robin

 

[John Bull]

thanks for the suggestions

I have managed to come up with a way of doing it which is short and sweet,

dim strabbmonths as String *10

for j=-1 to 2
strabbmonths(j+1)=DateAdd("m", j, Now)
strabbmonths(j+1)=Format$(j+1), "MMM")
next j

I tried it out, and lo and behold it worked !