By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,153 Members | 2,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,153 IT Pros & Developers. It's quick & easy.

How do I get code to act on the Value of a field not the literal name

P: n/a
I 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(strFieldID) 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
Oct 12 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

Kc-Mass wrote:
I 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(strFieldID) 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
Dim intFieldNo as integer
Dim varFieldID as variant

For intFieldNo = 1 to 14
rs.MoveFirst
Do While Not rs.EOF
fGoodDate=False
varFieldID = rs.Fields("Project" & intFieldNo & "Sponsor &
Date")
If Not IsNull(varFieldID) Then
....
End If
rs.MoveNext
Loop

Nexr intFieldNo

Oct 12 '06 #2

P: n/a
Thanks for the suggestion but it is still working just with the variable
and the string assigned to it. It does not reference the field value that
is described by the string at all. Very frustrating - should be simple.
But again thx.

<pi********@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>
Kc-Mass wrote:
>I 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(strFieldID) 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

Dim intFieldNo as integer
Dim varFieldID as variant

For intFieldNo = 1 to 14
rs.MoveFirst
Do While Not rs.EOF
fGoodDate=False
varFieldID = rs.Fields("Project" & intFieldNo & "Sponsor &
Date")
If Not IsNull(varFieldID) Then
....
End If
rs.MoveNext
Loop

Nexr intFieldNo

Oct 12 '06 #3

P: n/a
post the rest of the procedure. It's hard to figure out what's wrong
from here.

Oct 12 '06 #4

P: n/a
"Kc-Mass" <co********@comcast.netwrote in
news:2s******************************@comcast.com:

Kevin, perhaps you could just loop through the Fields Collectionof your
table.

If you insert this ...

For indes = 0 to 13
Debug.Print index + 1,RS(index).Name, RS(index).Value
Next
Stop

.... then in the Immediate window after running you will see the result.

If the Project numbers match, then your code loop 1 to 14 could use just
this:

strFieldID = RS(index - 1).Value

Play with it. Do you see where you can go from there?

For what it's worth, I think your code was failing because Access was
evaluating the String, rather than the Field. I have memories of using
something like ...

Eval("RS![Project " & index & " Sponsor & Date]")

... in Access97, but using the actual Index position is much easier.

Cheers,
Alan

I 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(strFieldID) 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

Oct 12 '06 #5

P: n/a
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 FindDatesinProject()

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.OpenRecordset("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(strFieldID) 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 FindDatesinProjectNoVariable()

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.OpenRecordset("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(strFieldID) 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********@hotmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
post the rest of the procedure. It's hard to figure out what's wrong
from here.

Oct 13 '06 #6

P: n/a
I thought about the Fields collection and you're probably right that it
would be easier to get the value there. It's hard to believe that VBA
doesn't have the ability to use a variable. Certainly other, older
languages have had that ability.
Also you are right that the procedure is evaluating the the string stored in
the variable rather than the field contents refererred to by the string.
thx

"Alan Carpenter" <No*@iHome.nzwrote in message
news:Xn************************@yourdomain.com...
"Kc-Mass" <co********@comcast.netwrote in
news:2s******************************@comcast.com:

Kevin, perhaps you could just loop through the Fields Collectionof your
table.

If you insert this ...

For indes = 0 to 13
Debug.Print index + 1,RS(index).Name, RS(index).Value
Next
Stop

... then in the Immediate window after running you will see the result.

If the Project numbers match, then your code loop 1 to 14 could use just
this:

strFieldID = RS(index - 1).Value

Play with it. Do you see where you can go from there?

For what it's worth, I think your code was failing because Access was
evaluating the String, rather than the Field. I have memories of using
something like ...

Eval("RS![Project " & index & " Sponsor & Date]")

... in Access97, but using the actual Index position is much easier.

Cheers,
Alan

>I 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(strFieldID) 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


Oct 13 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.