Field number as variable

[terryvanduzee]

Hello

I have a db table that contains 6 columns
I am trying to build an update query from the table

The first time strSQL runs, the data would be inserted into field(1)
The next time it would be inserted into field(2) etc...

When it runs, I get an error saying there is no destination field in Insert Into statement (tbldrawings.fields(1))

For x = 1 To 100
    DoCmd.SetWarnings False
For H = 1 To 6  'field 0 is an ID field that I don't need to display

strSQL = "Insert into tblrandnumbers Select Count(*) as Cnt, tblrandnumbers.fields(" & H & ")"
strSQL = strSQL & " From tbldrawings "
strSQL = strSQL & "Group by tabledefs.tbldrawings.fields(" & H & ") "
strSQL = strSQL & "Having ((tbldrawings.tabledefs.fields(" & H & ")"
DoCmd.RunSQL strSQL
Next H

Next x

Is this possible?
I need to run through this statement many times and append the value from it to the next field on the same record.

The reason I need to do this is because When I create the append query and run it in Access, I get no grouping; so I end up with 2000 records instead of 100 (or so). So I figure if I run through the SQL statement for each field, I can append the value to the field. Im not sure if I can append to the same record or not; maybe someone can answer that for me as well.

Thank you
Terry

 

[terryvanduzee]

I resolved it using dcount.

But now I get a syntax error in the SQL statement when Im trying to run it:

For x = 1 To 100
    DoCmd.SetWarnings False
i = DCount("[No1]", "tbldrawings", "[No1] =" & x)
j = DCount("[No2]", "tbldrawings", "[No2] =" & x)
k = DCount("[No3]", "tbldrawings", "[No3] =" & x)
L = DCount("[No4]", "tbldrawings", "[No4] =" & x)
m = DCount("[No5]", "tbldrawings", "[No5] =" & x)
n = i + j + k + L + m
strSQL = "INSERT INTO " & tablename & "(Cnt,Number) VALUES(" & x & "," & n & ");"
MsgBox strSQL
DoCmd.RunSQL strSQL
Next x

Any Suggestion?
Terry

 

[terryvanduzee]

Sorry folks
I guess I was having a brain dead moment. The field name Number is a reserved word.
I changed the fieldname and everything works fine.

Thanks
Terry