ACCESS 2007 update query: blank fields

[MarkGardner]

I am attempting to update a field in which I have blanks. I get no update for those fields, but I do get updates for fields that have values. I know that it has to do with blanks and zero fields are not treated the same. I recall reading something about treating them as null, but cannot find anything about it now and set it up.

Is there a property for that field that I set, so that the query will treat the field as a null value and do the update? If so, which property is it? And what is the proper setting?

Thanks very much.

 

[ajetrumpet]

to UPDATE a field that is NULL with Access SQL, use this:

WHERE field = NULL

to UPDATE a field that is of a zero-length string, use this:

WHERE field = ""

 

[ajetrumpet]

Mark,

Basically, SQL works different than VBA in that the syntax order, or "flow" if you will, is very different. Could you post the database...or maybe a JPEG to show us what the issue is? If you can, mark it up as much as possible. That would be great. Honestly, I cannot give you an answer here that is much more detailed than what I have already given. That is simply the SQL syntax to use in query statements...

 

[MarkGardner]

Adam,
Thanks again. I was hoping that it was something like a simple property setting in the table design mode that would solve it. I have put together a fairly large database with many tables, queries, macros etc. and this null thing has been a big challenge for me.

I have programmed, using various versions of dBase for twenty years and have tried to convert that knowledge into learing VBA, but have been frustrated with that as well, so when you talk VBA or SQL I get lost. I have also had a very difficult time trying to talk with anyone about using the ACCESS designer without using SQL to fix problems. In short this conversion has been very frustrating for me. I keep hoping that I can find someone that can give me answers, using the design mode of ACCESS and I have yet to find them. I guess that I was hoping you could have. As far as posting the database as you suggested, I am clueless about that.

I do want to thank you for your efforts in trying to help me anyway. It was really appreciated.

I will take what you sent me and see if I can somehow incorporate it into the designer. I think that it is just a matter of formatting what you sent me in the properties or the criteria in some way. It will be just a matter of trying it in several ways.

If I am not mistaken, WHERE field = NULL or WHERE field = " ", field represents the actual name of the field that I am update. Is that right?

Mark

 

[ajetrumpet]

This is how to post a DB to this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

If you want, I can just fix it for you when you upload it. Would that be OK? I also think I know what you mean by the ACCESS designer....the query builder grid you mean? I think that's it. The grid in Access has a CRITERIA line, and if you want NULL as the criteria for a field value, you just type NULL in that line under the field name. Conversely, if you want a zero-length string as the criteria, you put "" in the line. Hope that helps a bit so far...

 

[MarkGardner]

We are talking the same language now. Thanks. It is the the designer grid that I am so familar.

Here is an example of my table:

record # |Field 1 | Field 2 | Field 3
RECORD#1 | 10| 0| 10| ....... correct calc (F1 + F2 = F3)
RECORD#2 | 2| | | ........ incorrect calc(F1 + F2 does not equal anything in F3)

So, if I go into my query grid and place NULL on the Criteria line under Field 1 and Field 2, then I will get for REC2 a value of 20 in Field 3. Or if Field 1 is blank, and Field 2 has a value, then Field 3 will calc correctly. Did I pick up what you told me, correctly? Then I do not need to go in and force zeros in blank fields?

Now, what is the difference between Null and IsNull ?

Thanks again.

Mark

 

[ajetrumpet]

We are now talking the same language. Thanks. It is the the designer grid that I am so familar.

Here is an example of my table:
Record# Field 1

to records where the Field 1 is NULL type in:

NULL

in the "criteria" line under that field name. If you want zero length strings, type in:

""

let me know how it goes...

 

[Pat Hartman]

AJ - You cannot use the syntax - "something = Null" - the answer will always be Null!!! It will NEVER be True.

You can use - "IsNull(something)" or "Not IsNull(something)" in VBA or in SQL or in SQL only you can use - "something IS Null" or "something IS Not Null".

 

[ajetrumpet]

sorry about that. I guess it should be "IS NULL" (with NO equal sign!). Had a bad day i suppose. Thank you VERY much for reminding me of that Pat. My apologies to you Mark as well...

 

[MarkGardner]

Even with IsNull, I still could not get it to calc with the blank field. I must have done something wrong on the setup. However, in the mean time, I did get it to calc for me in a workaround, which I have done in the past.

What I did was in my macro and just before I ran the calc query, I ran a newly created query that would populate the blank fields with zeros for that menu choice. In the menu choice that needs real values in in it, I run a different query that calcs the needed values. Then I run the calc that I was trying to calc and I get the correct values. It is not the way I wanted to do it, because I do not like workarounds, but it got the job done. Thanks again for all of your help, especially in the understanding of my desire to use the design mode rather than SQL.

So, the IsNull is all that I need to put in the criteria line under the field name in the design mode of the query, is that what I am reading.

I always run into the same problem, so the next time, I will try again to get it to work with your suggestions.

Mark

 

[ajetrumpet]

as long as you got it Mark, everything should be cool. Good luck with it.

 

[Pat Hartman]

If for your purposes null and zero are the same, you should go back to the table and change the default value to 0. Then run an update query to change all the existing nulls to zeros.

However, for most applications null and zero have two different meanings. this is especially important if you are performing averages. The average of 3, null, 3 is 3 whereas the average of 3, 0, 3 is 2. When computing averages, (and other aggregates) nulls are ignored.

Your workaround would be unacceptable if you reported to me. Post your query and we'll help you figure out what is wrong.

 

[MarkGardner]

Pat,
Thank you for your suggestion. I have been asking the same question off and on for months and have not received an acceptable answer that I can use. Everyone always wants to give me a suggestion using SQL, which I do not understand. I admit I am not a guru, but I think that I have a fair understanding of the designer. I have taken classes. I have been all ears to anyone that can help me, but as I said, it normally comes in the form of SQL.

I have been trying to find someone that can give me solutions, using the designer. I have tried to find a source that can tell me what each of the properties in the designer mean and what effect they have when they are changed. I have found that the long list of properties are helpful when you know what the heck they are. Unfortunately, the ones I use are small in number. I would like to use more of them, but I have had a difficult time trying to discover what they mean. It has been frustrating to me. When I ask a question, I normally do not get satisfactory answers. I would love to find a source that would be able to explain all of these to me. All I am looking for is simple "Do this in the designer to fix the problem".

I hate doing workarounds, but when I can't get an answer, then I am going to do it to make it work. Normally the workarounds consist of running a query to update zeros into the fields so that they are no longer null.

Your suggestion has been very helpful. I will try it. If I understand you correctly, then when I first create a table I should set the values to default to 0. As far as the ones I already have already created, you said to run an update query. What type of query? I have tons of queries, but I am not sure what kind of query you mean. How about deleting and re-creating the tables with 0 as the default value?

Thanks again for your suggestion.

Mark

 

[georgedwilkinson]

It's a little difficult to share the design in a QBE and the content (internal representation to the machine) of the QBE and SQL are identical...just what you see is different and can be shared in SQL format. And since real-live programmers know how to share with each other using the compact SQL format and can't always recall what a complex screen looks like, you're more likely to get a simple SQL answer than a click here, type this, move your mouse here, hold your mouth a certain way answer.