Delete blank fields in a table

Posted on 2011-09-02
Last Modified: 2012-05-12
I have data in tables that I want to push out to Excel.  I am sure there could be a better way of doing this so if there is, I am open to suggestions.  Saying this, what I have so far is the following:

TABLE 1                                             TABLE 2                                TABLE 3
Column A  Column B                           Column C                             Column D Column E
Record 1    Record1                            Record 3                              Record 6  Record 6  
Record 2    Record 2                           Record 4
                                                           Record 5  

I have a form where the end user  can select which records from which tables they want to export to Excel and I build a temporary table.  So, if the end user choose Table 1 and Table 3

The table would create

Column A                         Column B                 Column D               Column E

All this is fine until Access populates the table it does

Column A                         Column B                 Column D               Column E
Record 1                          Record 2                  Null                         Null
Record 2                          Record 2                  Null                         Null
Null                                  Null                          Record 6                 Record 6

What I want is

Column A                         Column B                 Column D               Column E
Record 1                          Record 1                  Record 6                Record 6
Record 2                          Record 2                  Null                         Null

How would I do this.  I want the records to be column header and data on the Excel worksheet.

Any ideas would be appreciated.

Question by:mtrussell
LVL 51

Expert Comment

ID: 36474188
what query are you using to get first result? and what is the logic of the result are you looking for? I could not see the logic...
LVL 51

Expert Comment

by:Bill Prew
ID: 36474317
This seems like it would be related to the join logic you are using to access the tables, what does that look like?


Expert Comment

ID: 36475281
Just break your logic in 2 steps -
Step1: Create the table with the selected columns (Which I believe you already did)
Step2: Now, get the data from original Table1 & Table3 in lets say dataset and then using loop populate the data into the newly created table. Here when the loop counter is 1, you will populate the 1st row of Table1 & Table3. And so  on...

Author Comment

ID: 36510211
sorry for the delay in responding.

gopaltayde - the logic is broken into the two steps.  the issue is when the table is populated it is adding records from each table (which is logical) what I want is to delete the null values from the records so the columns of data line up properly in Excel.
LVL 51

Expert Comment

by:Bill Prew
ID: 36510557
Still need to see the Access query that returns the data...

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


Author Comment

ID: 36510768
here you go:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fldName As DAO.Field
Dim fldDate As DAO.Field
Dim xVariable As Long
Dim a As Long

DoCmd.DeleteObject acTable, "tbl_variableexport"

' Step 1 is to name the fields in the table.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("tbl_VariableExport")

xVariable = (DLookup("[A]", "tbl_variablesbytransaction"))

If IsNull(xVariable) Then GoTo thebottomofallthis

If DLookup("[variabletype]", "tbl_variablenames", "[id] =" & xVariableA) = "Single Entry" Then

a = 1

Set fldNameA = tdf.CreateField(DLookup("[variablename]", "tbl_variablenames", "[id] =" & (DLookup("[A]", "tbl_variablesbytransaction"))), dbText, 25)
tdf.Fields.Append fldNameA


Set fldName = tdf.CreateField((DLookup("[variablename]", "tbl_variablenames", "[id] =" & xVariable) & " Rate"), dbText, 25)
Set fldDate = tdf.CreateField((DLookup("[variablename]", "tbl_variablenames", "[id] =" & xVariable) & " Date"), dbText, 25)

tdf.Fields.Append fldName
tdf.Fields.Append fldDate

End If

dbs.TableDefs.Append tdf

' the second step is to fill in the data of the fields

If a = 1 Then

copyfld = "INSERT INTO tbl_VariableExport ( [" & fldName.Name & "] ) SELECT Tbl_Variables_SingleEntry.VariableValue FROM Tbl_VariablesByTransaction INNER JOIN Tbl_Variables_SingleEntry ON Tbl_VariablesByTransaction.A = Tbl_Variables_SingleEntry.Variable_ID;"


copyfld = "INSERT INTO tbl_VariableExport ( [" & fldName.Name & "], [" & fldDate.Name & "] ) SELECT Tbl_PercentageRates1.Rate, Tbl_PercentageRates1.EffectiveDate FROM Tbl_PercentageRates1;"

End If

DoCmd.RunSQL copyfld
LVL 51

Expert Comment

by:Bill Prew
ID: 36510961
Okay, the results you are getting make sense based on this.  Since you insert the columns from table 1 first, those records are added to the result set.  Then you process the next table, and insert those as additional rows below the Table 1 rows.

What you really need to do is a single insert which is a JOIN of the tables involved, but I don't see any easy way to do that in this case either, given the dynamic selection of the tales and fields, etc.

Might it make sense that after you load the data into Excel, to just delete the cells with null values?


Author Comment

ID: 36513503
I might have to go this direction but I would prefer to stage the data in Access so I can push the data through more methodically which is easier to test.

I'll continue to think about it and see if there is another option; otherwise I 'll go this direction.  

LVL 51

Accepted Solution

Bill Prew earned 500 total points
ID: 36514866
Conceptually what you sort of want to do is an outer join based on the row number of the results from each of the tables that you are trying to merge together.  But I'm not sure there is anyway to do that.


Author Closing Comment

ID: 36566039
Thanks for your help on this.  I am going to have to go another route on this one.  Looks like I hit a dead-end on this one...

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now