I have an access database (2010) that I feed quite often with a saved import excel spreadsheet (a lot of confidential data). The import contains over 98 columns. The data is appended to previous data. The excel spreadsheet contains a lot of formulas that pull data together for a final import sheet. Because of the original source for the data I have 6 different reports I have to pull and I place the results on 6 different worksheets that I then combine into a sheet and a final import sheet. So the combine sheet contains formulas like =IF(VLOOKUP($A3,'MDD3'!$A:$AD,AY$1,FALSE)="","",(VLOOKUP($A3,'MDD3'!$A:$AD,AY$1,FALSE))) then the final sheet (IMPORT) has =combined!AY3 - both actual formulas pointing to the same info.
Now the problem. I found that when I go to pull a field from my access database where the column is not null I get all the data returned - nulls and not null. When I paste the results into excel nothing appears in those "blank" cells, no spaces or anything i can find. If I point Code to the cell that appears blank it returns #VALUE. If I put a space in it I get 32 so I know it appears empty. I need to make these that appear blank to be truly blank. I know that I could create a table in either excel or access where I remove those that appear blank and create a version where they are truly blank then link them to the event number and do an update query but this would require that I have to do something like this every time I upload another data set and would need something like this for many of the columns.
Is there an upate query I could use that would find the blanks and actually make them null?
I have attached the results of one of the access output in excel. Maybe this will help.
Bookmarks