Need help to solve this

I created a vba function to batch import text files. The text file is a csv
text file and it has number of columns to capture the voting result of an
election. Depending on the number of the candidates it can have number of
columns plus additional few columns to capture the answer of the survey
questions in the ballot paper and the data type for this survey questions is
always text either "Yes" or "No" for the answer. The format of the text file
(from left to right), the left most columns are for the voting result then
right after the voting result are the answer for the survey question(s). It
could have 1 to x questions. Each person can vote UP to MAXIMUM three
candidates. The following is an example of the text file

for example 7 candidates and 3 survey questions

1, , ,4, ,6, ,YES,No,Yes...................Valid
,2, ,4, , ,7,Yes,Yes,Yes....................Valid
, ,3,4, ,6, ,No,Yes,Yes.....................Valid
,2, , , , , ,Yes,No,No........................Valid
1,2, ,4,5, , ,No,Yes,No.....................Spoiled

I use the transfertext method to import the text file to an Access table
with the default field name created on each of the column on the text file
such as F1, F2, F3 and so on.
I need to create a function to determine if the vote is VALID or SPOILED and
to update each row of the record in the table with a new field called
"status". It could have x number of candidates so that the function need to
decide how many fields(columns) left to the very first survey answer column
which has the data type "text" and the voting result is in numeric with 1
for first candidate at column F1, 2 for second candidate at column F2 and so
on. Need to have a function to loop through the columns on each row od data
from left to right. If it is a numeric then make it equal to 1, the looping
stop when the data type is not numeric, then sum it up if the value is less
then or equal to 3 then the status is Valid else it is Spoiled. Thanks.

"Paul" wrote:

I created a vba function to batch import text files. The text file is a csv
text file and it has number of columns to capture the voting result of an
election. Depending on the number of the candidates it can have number of
columns plus additional few columns to capture the answer of the survey
questions in the ballot paper and the data type for this survey questions is
always text either "Yes" or "No" for the answer. The format of the text file
(from left to right), the left most columns are for the voting result then
right after the voting result are the answer for the survey question(s). It
could have 1 to x questions. Each person can vote UP to MAXIMUM three
candidates. The following is an example of the text file

for example 7 candidates and 3 survey questions

1, , ,4, ,6, ,YES,No,Yes...................Valid
,2, ,4, , ,7,Yes,Yes,Yes....................Valid
, ,3,4, ,6, ,No,Yes,Yes.....................Valid
,2, , , , , ,Yes,No,No........................Valid
1,2, ,4,5, , ,No,Yes,No.....................Spoiled

I use the transfertext method to import the text file to an Access table
with the default field name created on each of the column on the text file
such as F1, F2, F3 and so on.
I need to create a function to determine if the vote is VALID or SPOILED and
to update each row of the record in the table with a new field called
"status". It could have x number of candidates so that the function need to
decide how many fields(columns) left to the very first survey answer column
which has the data type "text" and the voting result is in numeric with 1
for first candidate at column F1, 2 for second candidate at column F2 and so
on. Need to have a function to loop through the columns on each row od data
from left to right. If it is a numeric then make it equal to 1, the looping
stop when the data type is not numeric, then sum it up if the value is less
then or equal to 3 then the status is Valid else it is Spoiled. Thanks.





Hi Paul,

OK, here are the assumptions I used:

The first thru the xx fields are type integer
The names of the fields begin with "F" and are numbered sequentially.
If there are 10 integer fields, they must be numbered 1-10. (can not skip 9
and use 11)

The fields do not have to be in order ie F1, F2, F3,... (F1,F20, F2, F4,
...., is OK) but there cannot be a non-integer field between the integer
fields (can not be F1, F2, Status, F3 ,...)

The field that holds "Valid" or "Spoiled" is a text field.

The function should be put in a standard module. The module name cannot be
the same as the function.

I create a new form, in continous forms with all of the field in the form
(record source is a query). Then I added a button to call the function.
To call the function I created a button and used the Click event with the
following code:

'---
Dim temp As Variant

'saves any changes to data if on a form
If Me.Dirty Then
Me.Dirty = False
End If

'temp is a throw away value
temp = IsValidVote("Voting", 3)
^^^^^
'table name goes here

'if on a form
Me.Refresh
'---


Chagne the table name to your table. You might want to use a text box on a
form to enter the table name.



Here is the function. I named it "IsValidVote"

'----beg code-----------
Public Function IsValidVote(pstrTableName As String, Optional pintMaxVotes
As Integer)
On Error GoTo IsValidVoteErr

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset

Dim strSQL As String

Dim i As Integer
Dim vNumColumns As Integer
Dim vNumVotes As Integer

If IsNull(pintMaxVotes) Then
pintMaxVotes = 3
End If

Set db = CurrentDb()
Set tdf = db.TableDefs(pstrTableName)

vNumColumns = 0

For Each fld In tdf.Fields
' field type 3 = Integer
If fld.Type = 3 Then
strSQL = strSQL & fld.Name & ", "
vNumColumns = vNumColumns + 1
End If
Next

' create SQL string
If Len(strSQL) 0 Then
strSQL = "Select " & strSQL & "Status FROM " & pstrTableName
End If

'open recordset
Set rst = db.OpenRecordset(strSQL)

With rst
If Not (rst.BOF And rst.EOF) Then
.MoveFirst
Do While Not rst.EOF
vNumVotes = 0
'count number of non null columns
For i = 1 To vNumColumns
'field names MUST begin with 'F'
If .Fields("F" & i) 0 Then
vNumVotes = vNumVotes + 1
End If
Next
'update the Status column
.Edit
If vNumVotes = pintMaxVotes Then
.Fields("Status") = "Valid"
Else
.Fields("Status") = "Spoiled"
End If
.Update
.MoveNext
Loop
Else
MsgBox "No records found in table " & pstrTableName
End If
End With

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

IsValidVoteExit:

Set db = Nothing
Exit Function

IsValidVoteErr:

Select Case Err
Case 3265& 'Table name invalid
If Err.Source = "DAO.Fields" Then
MsgBox "Invalid field name"
ElseIf Err.Source = "DAO.TableDefs" Then
MsgBox pstrTableName & " table doesn't exist"
End If
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume IsValidVoteExit

End Function

'------end code ------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)