Using NZ funtion with textboxes

[belsha]

I would like to use code to look at each field in my form (they are all textboxes) and if it is blank, fill it with "888". I have read enough to know I should use the NZ function, but can someone tell me how it should be written to look through every textbox on the form?

 

[RuralGuy]

Forms have controls and records have fields. TextBox controls have a DefaultValue on the Data tab of the properties sheet. I suspect if you set it to "888" it will achieve what you desire.

 

[belsha]

Actually, I have 2 things I want to accomplish. I don't want to use a default in the form, I want to wait until the person has finished entry, because we have found it is too easy to skip over fields that are already filled in. So from here on in, without writing an if statement for every field, when the person gets done with that page, I would like the blank fields to become 888. For the data I already have in tables that have blanks, I also need something that will go through every field in a table and when it finds a blank insert an 888. Thanks.

 

[RuralGuy]

Why "888"? Isn't the Null suficient? You are being kind to your users and letting them skip fields rather than insisting they fill in the missing fields. From a programmers viewpoint I see no difference between "888" and Null except the user could have actually put "888" in a field.

 

[directormac]

I understand why you don't want to use a default value, per RG's suggestion. However, if you want to set a bunch of values without using defaults, you're going to have to do that somehow, i.e., "an if statement for every field".

I would probably go with a statement in the BeforeUpdate event of each relevant control that goes iif(nz(me.value,"")="","888",me.value).

Alternately, you could put some code behind the BeforeUpdate event of the form itself that checks all the relevant controls and sets them to 888 if they're blank using a series of statements similar to the above. You still have to write the same number of statements, they're just all in one place.

EDIT: The second approach is the correct way. See Pat Hartman's insights below.

For more info on Nz (per your original question), check Access help and read this article.

--Iffy Mac

EDIT: I agree with RG, if you're using 888 to stand for "no data", then just use nulls.

Also, the change of 888 for blank values in existing records can be accomplished with an UPDATE query. Check Access help.

 

[belsha]

RG - We send this data somewhere where they require us to have all the fields filled in, and if there are any blanks or don't knows or N/A's, they want 888. It can either be done at the time of entry or before I send it, but somehow every month I have to go through all these records and make sure there are no blanks, and I'm looking for the most time expedient way to do it.

 

[RuralGuy]

belsha,

Must be some darn government thing. I understand. You are pretty much stuck using a bunch of If..Else..End If statements in the BeforeUpdate event of the Form. It is the only event you can be assured will fire when you save. The user can skip controls on the form and then their events will never fire.

If you are consistant in naming your TextBoxes (txtBox1, txtBox2, etc) then you could make a for...next loop:

Dim x As Integer
For x = 1 to n
  If Len(Me.Controls("txtBox" & x) & "") = 0 Then
    '-- This catches both Null and 0 length string fields.
    Me.Controls("txtBox" & x) = "888"
  End If
Next x

 

[directormac]

RG's code also gives you the important option of NOT setting certain controls to 888, such as the control that holds the Autonumber PK of your table, which you wouldn't be allowed to change (which is the factor that earlier stopped me from suggesting a for..next that loops through every control).

By choosing to name all the "888"-needed controls something consistent and ending in numbers ("txtNoBlanks_1", etc.) you can have your for...next effect only those while controls with other names ("txtMyKeyField") aren't touched.

You'd also have that selectivity if you did a long series of individual checks, but his way involves less code... Nice one, RG.

Out of curiosity, HOW are you sending these things to the external entity? The reason I ask is because you might be able to do these checks on the OUTPUT end (e.g. report) which would save you having to store all these useless 8's and keep your own data pristine.


--Minimalist Mac

 

[belsha]

We take the Access data tables, convert the files to comma delimited files, and export them that way. Thank you both for the suggestions, they sound perfect for this situation!

 

[directormac]

Seeing as how you can export queries just as easily as you can export tables, I would leave the "888" fields blank in YOUR db (just because someone else does something the wrong way doesn't mean you have to), then create queries that rework the empty fields into 888's and do whatever other manipulation you need, and export the query results.

--Clean Mac

 

[Pat Hartman]

Ditto the last response. Leave the data null in your tables and fix it up in the query as you export it.

Select Nz(fldANumeric, 888), Nz(fldBText, "888"), ....

I would like to comment on some of the other suggestions though:

I would probably go with a statement in the BeforeUpdate event of each relevant control that goes iif(nz(me.value,"")="","888",me.value).

- This is a common misconception. You CANNOT reliably use ANY control level event to check for nulls. Why, you might ask? Becuse if the user never tabs into a field, no control level event will be fired. Therefore, your code won't execute. The ONLY secure place to check for null values is the FORM's BeforeUpdate event.

And finally, maybe I missed something, but why would you all ever write all that code when you can set a table level default value? I've been doing this for over 30 years (and I've written my million lines of code so I don't need the practice) and I would NEVER write code that I could just use a property setting to handle.

Bottom line - leaving them null is best practice. Setting the table level default value to 888 is second best. Code in the Form's BeforeUpdate is a distant third. And an update query doesn't even get a single vote (except that if you go with the default, you'll need to run an update query ONCE to make sure that all existing records are filled).

 

[belsha]

Pat - Thanks for reminding us to use the BeforeUpdate event of the form, that makes the most sense vs. the fields. As far as using a default, as I mentioned in an earlier post, we have found that if someone is entering data, and gets sidetracked by a call or whatever, if fields are defaulted, they figured they answered it, and I find fields that have an answer on the form we collect on, that were left 888 in the database. (I have to verify all this stuff too!) So your suggestion on doing the update once in a query sounded like the best solution for me. Since there are up to 200 fields on a form, and they are not named Text1, Text2 (because they have validation coding behind them, done with names like txtPhys) and I don't want to have to change every name everywhere it is used, is there a way in a query to say if field in the table (no matter what the name) is empty, fill it in with 888? Thanks!