Empty Cells apparently have spaces

[Sam Capricci]

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.

[alansidman]

Why paste the results into Excel. Why not export the results to an excel file.

[MarvinP]

Hey Sambo Kid,

The problem in Access between Null, Blanks and Missing data always confused me. I finally realized this is confusing for lots of people. That is why they have in access the

NZ() function that changes Null to Zero.

Read these two sites for more help:
http://allenbrowne.com/vba-NothingEmpty.html
http://www.techonthenet.com/access/f...dvanced/nz.php

[Sam Capricci]

So to answer Alan's issue, see attached. (not sure what difference it makes but since you asked and BTW, I never did it that way before).

[Sam Capricci]

So Marvin, I read both links, the first seems to clarify differences between empty, missing, nothing or null but it doesn't really help me get to the results I need for my output. And I have no experience with VBA. The second link seems like it holds out the most promise but when I follow the query example at the bottom it still produces the same results that I showed in post #4.
here is the SQL code...
SELECT [New PRISM].[File ID], Nz([New PRISM]![Equipment Brand Name],'not found') AS Expr1
FROM [New PRISM];
And, please don't confuse me showing the SQL code with the possibility I could write SQL code.
If I can get that or something like it to work, I believe I will be using it quite a lot as it seems very promising.

[alansidman]

Sambo
I am confused. When I apply formulas to Column B of your examples, I am unable to get #VALUE returned as you indicated in post 1. It acts as if it is an empty cell. Perhaps I am not understanding your issue.

[Sam Capricci]

Alan, when in access I apply the query, give me all the equipment brand names for all the events that are not blank (not null) I get back the whole list that I showed you in the excel export from access (SQL below for query)...
SELECT [New PRISM].[File ID], [New PRISM].[Equipment Brand Name]
FROM [New PRISM]
WHERE ((([New PRISM].[Equipment Brand Name]) Is Not Null));

and if i rewrite it to be...
SELECT [New PRISM].[File ID], [New PRISM].[Equipment Brand Name]
FROM [New PRISM]
WHERE ((([New PRISM].[Equipment Brand Name]) Is Null));

it returns nothing.
If you click in cell B1 of the query1 spreadsheet export and (as I do a lot) hold down control/shift and the down arrow, it acts like it is all filled with data instead of jumping down to row 271 for example where the first value shows up (across from file ID 507).
So I am trying to get a query that will treat the blanks as null and when I want to pull out the equipment that is tagged to an event (file id) I only want the list with something in it and not all 12812 rows most of which have no data in them.

AND, if you click on any cell in col B that seems to have nothing in it, hit delete, then go back to the top of that column and repeat the shift control down arrow trick it will jump to that cell then jump past it acting like something was actually in it.

[alansidman]

Much clearer Sambo. Can you sanitize your data base and upload it for analysis and experimentation. We will only need a sample of the data and not the whole database. Make sure to dummy up any confidential data. Run a Compact and Repair before uploading. You will have to zip the file as this forum does not allow uploading of Access files.

[Sam Capricci]

Alan, that would take a while to do. I'm not at work now so I will have to work on it Monday . Thanks for sticking with this.

[alansidman]

No issues. Will look for it on Monday. Have a great weekend.

[Sam Capricci]

I'm getting an error trying to upload the file. It says that it is an invalid file. It is a .accdb access database 932kb in size. Thoughts?

[Sam Capricci]

trying again, this time zipping it...

[MarvinP]

Hi Sambo kid,

After re-reading your posts, there is an easy way to convert the SQL statement to what we like in Excel.
Here is my sql statement

Please Login or Register  to view this content.

Copy the above SQL statement into the window buffer using Ctrl C.
Then create a new query and right click on the new Query TAB and select "SQL View"

Paste the above string into the upper portion of the Design Query box.

Right Click on the Query Tab again and select "Design View".

I think this is the Update Query you are looking to run to make all those "blanks" Nulls.

[Sam Capricci]

WOW! That did it. Thanks Marvin, you deserve the forum guru title.