Nulls giving a lot of trouble

[sonaljain123]

Hi All,

I have this huge table that I run queries off to get desired results. Now in that table of course there are some values and some nulls. So if I am trying to run a query which sums three of the rows with one null value, the result is a zero.

Now I am looking for a query that can help me update this table to keep all the records as it is and only changing the null values to zero so that adding the other values to zero can give me the desired result.

Please help soon!

Thanks in advance.

 

[pbaldy]

If you want to leave the table alone, use the Nz() function in your query.

Nz(FieldName, 0)

 

[sonaljain123]

Also, it would be great if someone can tell me how to use a calculated column in a query as a part of another calculation in the same query.

So what I mean is lets say you have columns A, B and C. Then you are trying to run a query which has Sum:B+C and then can I use Sum in the same query to compute Sum_Final:Sum+A? Is that possible?

May be the example is a little trivial but I am facing issues here in my table.

Thanks in advance!

 

[sonaljain123]

Thanks for the quick reply, is there a way to modify the table because my queries are extremely long?? there is just tons of data so it might be easier for me to modify the table and create an updated table....

thanks again!

 

[sonaljain123]

Okay the bigger picture is that I have daily spread sheets coming in which I am importing in a big table in MS access using VB Macro and my macro has the following line:

iQuery3 = "FROM Temp_Data WHERE (((Temp_Data.F1) Is Not Null) AND ((InStr([F1]," & """" & "-" & """" & "))<>0)); "

which means that it does check for null before importing. But then why would the table still have nulls?

I am not entirely sure as to what is going on here...

 

[pbaldy]

It checks F1 for Nulls, not any other field. Are you saying F1 still contains Nulls?

Sometimes you can use the alias like that (Sum + A), sometimes you can't. You definitely can't use them in the WHERE or GROUP BY clauses. I can't give you a rule of thumb on when they don't work in the SELECT clause. It just seems like at some point Access can't keep track of them anymore.

 

[sonaljain123]

Yes I think you are right, so I need to check the same for nulls in the other fields as well. so my entire query looks like this:

iQuery1 = "INSERT INTO HD_GL_ACCT_BALANCE ( [DATE], GL_ACCOUNT_NO, GL_BALANCE ) "
iQuery2 = "SELECT distinct #" & iDate & "# AS [DATE], Mid([F1],1,InStr([F1]," & """" & "-" & """" & ")-1) AS GL_ACCOUNT_NO, Temp_Data.[UPDATED thru###] AS GL_BALANCE "
iQuery3 = "FROM Temp_Data WHERE (((Temp_Data.F1) Is Not Null) AND ((Temp_Data.UPDATED thru###) IS NOT NULL) AND ((InStr([F1]," & """" & "-" & """" & "))<>0)); "
iQuery4 = iQuery1 & iQuery2 & iQuery3
DoCmd.RunSQL iQuery4

But I dont want the part where it sets ((Temp_Data.UPDATED thru###) IS NOT NULL), because that would mean it wont insert that part in the table. I want to set those nulls to zero. Can you help me modify the query a little here please?

Thank you!

 

[pbaldy]

This type of thing:

Nz(Temp_Data.[UPDATED thru###], 0) AS GL_BALANCE

 

[sonaljain123]

Okay, I am going to try this now, might take long...

thanks!!!

 

[pbaldy]

No problem, post back if you get stuck.

 

[sonaljain123]

Hi, I dont think that change is working still. I deleted all the records and I tried inserting new data in the table using the changed macro but still my queries of adding some records is giving me problems.

 

[pbaldy]

What problems? Can you post the db?

 

[sonaljain123]

Actually I cant post the data base. So I will have to figure something.

So when a field is a zero and NOT NULL, does a zero appear in the record or is it a - or is it just blank or can be anything?

 

[pbaldy]

In my experience, a zero will appear. It's possible you have a zero length string ("") there rather than a Null. That will cause the Nz() function to miss it. A common test for both is:

Len(FieldName & "") = 0

 

[sonaljain123]

Okay. I think I have lots of blanks that are getting imported after making Nz a part of the query as well. So what can be done so that all blanks and nulls are set to zero? My excel sheet from where the data is being imported surely has zeros.

So if it is importing from that excel sheet, should it not just copy the zero? Why would it convert it into Null?

 

[pbaldy]

"Surely"?!? I wouldn't make that assumption. Try this in the import:

IIf(Len(Temp_Data.[UPDATED thru###] & "") = 0, 0, Temp_Data.[UPDATED thru###] ) AS GL_BALANCE

 

[sonaljain123]

Hi, I tried the line of code you provided but it is giving me a syntax error for some reason. I have pasted the code below here. Do you see any thing wrong in the code?

Thanks!

iQuery1 = "INSERT INTO HD_GL_ACCT_BALANCE ( [DATE], GL_ACCOUNT_NO, GL_BALANCE ) "
iQuery2 = "SELECT distinct #" & iDate & "# AS [DATE], Mid([F1],1,InStr([F1]," & """" & "-" & """" & ")-1) AS GL_ACCOUNT_NO, IIf(Len(Temp_Data.[UPDATED thru###] & "") = 0, 0, Temp_Data.[UPDATED thru###] ) AS GL_BALANCE"
iQuery3 = "FROM Temp_Data WHERE (((Temp_Data.F1) Is Not Null) AND ((InStr([F1]," & """" & "-" & """" & "))<>0)); "
iQuery4 = iQuery1 & iQuery2 & iQuery3

 

[pbaldy]

When building SQL in code you have to watch out for spaces between lines. In your case, there will be no space between GL_BALANCE and FROM, so the SQL will be:

...AS GL_BALANCEFROM Temp_Data...

Access won't be able to understand what you want. A good debugging tool is to add

Debug.Print iQuery4

after the SQL is built, which will print out the finished string to the VBA Immediate window. You can examine it there and spot problems, or copy/paste into a blank query and attempt to run it, where you'll often get a more descriptive error.

 

[sonaljain123]

Hmm, I dint know that, but i did try to change the same and run it to see if it can fix the error but again it is showing some error in syntax only.

Is there a way to create a new table by name lets say HD_GL_ACCT_BALANCE_New which would have all the same fields as my original table HD_GL_ACCT_BALANCE except that this one would check for Nulls?

I think this might just make it easier.

 

[pbaldy]

What was the result of the Debug.Print, and what happened when you tried to run that in a blank query?

You can copy the table and set all the fields to required and not allow zero length strings and see what happens.