Original message is at bottom.
Here are two versions of the unfinished procedure. The first one attempts
to use the variable and just parses the literal string content of the
variable.
The second is hard coded for the first of the 14 fields. It works perfectly
in so far as it works on the field content and picks out the date every
time.
Any and all help appreciated.
Sub FindDatesinProj ect()
Dim DB As Database
Dim RS As Recordset
Dim strPSD As String
Dim strBuild As String
Dim strDateHold As String
Dim strChar As String
Dim strFieldID As String
Dim tfGoodDate As Boolean
Dim intMark As Integer
Dim intIndex As Integer
Set DB = CurrentDb
Set RS = DB.OpenRecordse t("Project List", dbOpenSnapshot)
tfGoodDate = False
For intIndex = 1 To 5
RS.MoveFirst
Do While Not RS.EOF
strFieldID = "RS![Project " & intIndex & " Sponsor & Date]"
' Debug.Print strFieldID
tfGoodDate = False
If Not IsNull(strField ID) Then
strPSD = strFieldID
If Len(strPSD) 0 Then
Debug.Print strPSD
intMark = 1
strBuild = ""
Do While intMark <= Len(strPSD) And tfGoodDate = False
strChar = Mid(strPSD, intMark, 1)
Do While Not strChar Like "[1,2,3,4,5,6,7,8 ,9,0,/]" And
intMark <= Len(strPSD)
intMark = intMark + 1
strChar = Mid(strPSD, intMark, 1)
Debug.Print strChar
Loop
Do While strChar Like "[1,2,3,4,5,6,7,8 ,9,0,/]" And intMark
<= Len(strPSD)
strBuild = strBuild + strChar
intMark = intMark + 1
strChar = Mid(strPSD, intMark, 1)
Loop
If Len(strBuild) >= 6 And InStr(strBuild, "/") <0 Then
tfGoodDate = True
Else
strBuild = ""
End If
Debug.Print strBuild
Loop
End If
End If
RS.MoveNext
Loop
Next intIndex
End Sub
Sub FindDatesinProj ectNoVariable()
Dim DB As Database
Dim RS As Recordset
Dim strPSD As String
Dim strBuild As String
Dim strDateHold As String
Dim strChar As String
Dim strFieldID As String
Dim tfGoodDate As Boolean
Dim intMark As Integer
Dim intIndex As Integer
Set DB = CurrentDb
Set RS = DB.OpenRecordse t("Project List", dbOpenSnapshot)
tfGoodDate = False
For intIndex = 1 To 5
RS.MoveFirst
Do While Not RS.EOF
strFieldID = RS![Project 1 Sponsor & Date]
tfGoodDate = False
If Not IsNull(RS![Project 1 Sponsor & Date] Then
strPSD = RS![Project 1 Sponsor & Date]
If Len(strPSD) 0 Then
'Debug.Print strPSD
intMark = 1
strBuild = ""
Do While intMark <= Len(strPSD) And tfGoodDate = False
strChar = Mid(strPSD, intMark, 1)
Do While Not strChar Like "[1,2,3,4,5,6,7,8 ,9,0,/]" And
intMark <= Len(strPSD)
intMark = intMark + 1
strChar = Mid(strPSD, intMark, 1)
Debug.Print strChar
Loop
Do While strChar Like "[1,2,3,4,5,6,7,8 ,9,0,/]" And intMark
<= Len(strPSD)
strBuild = strBuild + strChar
intMark = intMark + 1
strChar = Mid(strPSD, intMark, 1)
Loop
If Len(strBuild) >= 6 And InStr(strBuild, "/") <0 Then
tfGoodDate = True
Else
strBuild = ""
End If
Debug.Print strBuild
Loop
End If
End If
RS.MoveNext
Loop
Next intIndex
End Sub
have a flat file Access database that I am trying to normalize. It has
one collection of fields
named "Project 1 Sponsor & Date" thru "Project 14 Sponsor & Date". These
are essentially freeform fileds. Sometimes the date is in front sometimes
in the middle sometims in the end.
I have a routine done that walks through the up to 254 characters and picks
out the date.
What I am trying to do is walk through the variations of the fieldname and
extract the date piece to put into another file and field.
I have tried:
For index = 1 To 14
RS.MoveFirst
Do While Not RS.EOF
tfGoodDate = False
strFieldID = "RS![Project " & index & " Sponsor & Date]"
If Not IsNull(strField ID) Then
'At this point it I want it to test whether or not the fields value is
null
'but it instead test whether the value of the variable is null.
Any help appreciated.
Thx
Kevin C
<pi********@hot mail.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .
post the rest of the procedure. It's hard to figure out what's wrong
from here.
