Deleting Columns with no data while querying in Access

[AccessMan]

Hello -

I need some help with querying in Access. I have columns with data and some with out based on what gets filled out in a form. My problem is that I do not want all of these fields in my report if they have no data. I would like to have a program delete the columns as I query and the report gets generated. Is this possible?

Any help would be greatly appreciated.

Thank you,

AccessMan

 

[nickjar2]

Do you mean 'Not include the columns in your report'? or delete the columns completely from your table?

Nick

 

[AccessMan]

What I mean is if the data is queried and it has nothing in the column I do not want to show that colum in the query and/or my report. I have some columns that may not have data based on certain circumstances. Does this help?

 

[rewdee]

I've done something similiar. I don't know if this is the most efficient way but here's one way you can do it. I haven't written out all the code but if you need it I will write the entire module.

1) Iterate through your columns of your query.
2) Check to see if the column has data.
3) If it has data either add to a tempTable or an Array
4) Build your query based on the Array or tempTable.

I will give you a sample skeleton on how I would do it. The code is not complete and my have syntax errors. You (or I will later if need be) will have to flesh it out.

Step 1
Dim qdf as queryDef
for each qdf in currentdb.querydefs
CheckForData(tblName,qdf.name)
next qdf

Step 2
Dim sSQL As String
Dim rsIfActive As Recordset
Dim Result As Boolean

On Error GoTo errHandler

Result = True
sSQL = "SELECT count(" & aField.Name & &quot as x" _
& " FROM [" & TableName & "]"
If aField.Type = dbText Or aField.Type = dbMemo Then
sSQL = sSQL + " WHERE " & aField.Name & " Is Not Null"
Else
sSQL = sSQL + " WHERE " & aField.Name & ">0 And " & aField.Name & " Is Not Null;"
End If


Set rsIfActive = Currentdb.OpenRecordset(sSQL, dbOpenSnapshot)
Result = rsIfActive!x > 0
IsActiveField = Result
rsIfActive.Close

errCleanup:
Set db = Nothing
Set rsIfActive = Nothing
Exit Function
errHandler:
On Error Resume Next
Debug.Print sSQL
IsActiveField = True
Resume errCleanup
End Function

Step 3
dim rs as recordset
set rs=currentdb.openrecordset("tempTable&quot

with rs
.addnew
![ColumnName]=qdf.name
.update
.close
end with

set rs=nothing

Step 4
dim sSQL as string
dim rs as recordset
dim rsTempTable as recordset

sSQL="SELECT "
set rsTempTable=currentdb.openrecordset("tempTable)
with rsTempTable
If Not (.BOF And .EOF) Then .MoveLast
If .RecordCount = 0 Then Exit
while not .EOF
sSQL=sSQL + .fields(0).value + ","
loop
end with

sSQL=sSQL + " FROM " & SomeTable

set rs=currentdb.openrecordset(sSQL)
...Do stuff or use sSQL as RecordSource etc.

Remenber this code needs fleshing out. Also another level of complexity will be finding the individual tables involved. I have simplified things by assuming the recordsource is only from one table.

Hope this helps,
Rewdee

 

[AccessMan]

Thank you Rewdee. I will see how it works and let you know if I need help. Thank you so much!! - AccessMan

 

[AccessMan]

I have been trying this code and I fixed a few errors and I keep getting some more as I keep playing around with the code. I was wondering if you could help me out. Also, I have two tables I need to link. One table is the one with the columns I would like to delete if they have no data in them and the other one is a table that is linked through a relationship. Any help that you could give me would be great. Thank you.

Regards,

AccessMan