Find the Contents in a 250 Field Record

[Guest]

I have to see if the contents of 250 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 250 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

Thank Yo

Len

 

[Andrew Smith]

OMG 250 Fields! Any message on this newsgroup that mentions even a fifth of
this number of fields in one table will immediately get the response that
you need to normalise your data. 20 Fields in a table is a lot. 50 Is
virtually unheard of, and 250 is just ridiculous.

Your "Tag" value is presumably to be stored in this table too, so that makes
251 fields. Storing calculated values in a table is also a "BAD IDEA". I
guess you would also need at least one other field to identify what the
record is about - so at least 252 fields then. Do you know the field limit
for an Access table? (It's only very slightly higher than the number you've
already reached).

What you are trying to do here would be relatively trivial with a properly
designed database. It would also work a lot faster than the speed that this
would run if you could get the code correct. I can guarantee that there will
be other things you will need to do that will also be made much more
difficult by this design.

I was going to show you how to correct your code, but I honestly don't think
this is the right thing to do, so I won't. Unless you have a very good
reason not to, then please consider designing your tables properly before
you go any further. I'm sure you'll get some good advice on this here if you
don't know how to do it.

I have to see if the contents of 250 fields are all zero. I just cannot

find the correct syntax. What I have so far is shown below.

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
WNWN = (rst!(= HoldIt)) ' This is where my problem

is . WNWN should be 0 or a number from 1 to 129.