Find the Contents of Fields Using Variables.

[Guest]

I have to see if the contents of 240 fields are all zero. I just cannot find the correct syntax. What I have so far is shown below

Dim db As Databas
Dim rst As Recordse
Dim ChkByte As Byt
Dim wrkIndx As Byt
Dim HoldIt As Strin
Dim WNWN As Byt

Set db = CurrentD

Set rst = db.OpenRecordset("SELECT * FROM UNIVREC"

Do Until rst.EO
wrkIndx =
For wrkIndx = 1 To 2
If wrkIndx < 10 The
HoldIt = "WN0" & wrkInd
Els
HoldIt = "WN" & wrkInd
End I
WNWN = (rst!(= HoldIt)) ' This is where my problem is . WNWN should be 0 or a number from 1 to 129.
MsgBox WNW
Nex
Loo

' I have a table with records that contain 240 fields. I have to check to see if each field i
' a zero. If they all are 0 I update a one position field called Tag. In the Variable Name Holdi
' I want to put the name of the field to check the contents of in the variable Name Hold it. Eac
' field starts with the characters WN and the number follows. WN01, WN02, WN03, WN04, etc. I actuall
' have 250 fields but just trying to test 25 to get it to work. I am having trouble with the Synta
' on the above code. I know the syntax in VB6 ans ASP, but not VB
' I know this is a lot of fields, but this is the only way I can go. I cannot have a record for each field becaus
' the table exceeded the disk drive

Thank Yo

Le

 

[John Vinson]

I have to see if the contents of 240 fields are all zero. I just cannot find the correct syntax. What I have so far is shown below.

If you have 240 fields in your table it's very probably INCORRECTLY
STRUCTURED... let's see...

Dim db As Database
Dim rst As Recordset
Dim ChkByte As Byte
Dim wrkIndx As Byte
Dim HoldIt As String
Dim WNWN As Byte

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM UNIVREC")

Do Until rst.EOF
wrkIndx = 1
For wrkIndx = 1 To 25
If wrkIndx < 10 Then
HoldIt = "WN0" & wrkIndx
Else
HoldIt = "WN" & wrkIndx
End If

If you MUST do this, use HoldIt = Format(wrkIndex("WN00")

WNWN = (rst!(= HoldIt)) ' This is where my problem is . WNWN should be 0 or a number from 1 to 129.

and WNWN = rst.Fields(HoldIt)

MsgBox WNWN
Next
Loop

' I have a table with records that contain 240 fields. I have to check to see if each field is
' a zero. If they all are 0 I update a one position field called Tag. In the Variable Name Holdit
' I want to put the name of the field to check the contents of in the variable Name Hold it. Each
' field starts with the characters WN and the number follows. WN01, WN02, WN03, WN04, etc. I actually
' have 250 fields but just trying to test 25 to get it to work. I am having trouble with the Syntax
' on the above code. I know the syntax in VB6 ans ASP, but not VBA
' I know this is a lot of fields, but this is the only way I can go. I cannot have a record for each field because
' the table exceeded the disk drive.

Which is larger: a rectangle 240 x 2000, or a rectangle 2 x 240000?
The same size, of course. A wide-flat table holding 480000 fields and
a tall-thin table holding 480000 fields will be essentially the same
size, with tall-thin being MUCH smaller if many of the fields are
indexed.

The table exceeded the disk drive (or - I hope!! - the 2 GByte size
limit of Access databases) for SOME OTHER REASON. You're solving the
wrong problem by denormalizing this data!

What error message did you actually get? How many records are in this
table, and what other fields?