change field in report using vba?? 1

[jbryan]

hi
my my access report is linked to a query which selects 3 text fields and one of 50 other identical(in terms of datatypes, but have different names) numerical fields from a table having 53 fields. So in total there are 4 fields in the query. The first 3 fields are constant, but the 4th field changes dynamically from my vb code. I update the query from the code to choose one of the 50 fields from the table. But the report does not dynamically accept this change in field and in the output does not show the 4th field at all. Could someone please help me with this? I am attaching the code that I have written.



strSQL = "SELECT [rrg dpw by state 2003 tbl].NAIC, [rrg dpw by state 2003 tbl].[Risk Retention Group Name], " & _
"[rrg dpw by state 2003 tbl].[Tot DPW 0Y], [rrg dpw by state 2003 tbl]." & _
Trim(Me.lsbState.Value) & " " & _
"FROM [rrg dpw by state 2003 tbl] WHERE ([rrg dpw by state 2003 tbl]." & _
Trim(Me.lsbState.Value) & " <> 0) " & _
"ORDER BY [rrg dpw by state 2003 tbl].[Risk Retention Group Name]"

MsgBox (strSQL)

For i = 0 To MyDB.QueryDefs.Count - 1
If Trim(MyDB.QueryDefs(i).Name) = "queryRRGListByStateDPW" Then
MyDB.QueryDefs.Delete "queryRRGListByStateDPW"
Exit For
End If
Next i

Set qdef = MyDB.CreateQueryDef("queryRRGListByStateDPW", strSQL)

stDocName = "reportRRGListByStateDPW"
DoCmd.OpenReport stDocName, acPreview

 

[dhookom]

Alias your dynamic column with a name:

strSQL = "SELECT NAIC, [Risk Retention Group Name], [Tot DPW 0Y], " & _
             Trim(Me.lsbState.Value) & " as TheField " & _
             "FROM [rrg dpw by state 2003 tbl] WHERE " & _
             Trim(Me.lsbState.Value) & " <> 0 " & _
             "ORDER BY [Risk Retention Group Name]"

I somehow expect that this table might be un-normalized.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jbryan]

Thanks for the answer. And you are correct about the table not being normalised.