Need to change NULL to Space in Form

[Brad]

We have an Access 2007 table that has a field in which NULLS are not allowed.

In the table definition, we have set up a Validation Rule “Is Not Nullâ€.

This works nicely.

The catch is that in some situations, we want to store a space in this field.

We are using a FORM that is based on a Query that is in turned based on the
Table with the validation rule.

We have tried to change the field from NULL to spaces in the BeforeUpdate
event for
this field on the form. We can detect when the field is NULL, but Access
will not
let us plug in a space at this point in time.

What is the best way to do this?

Thanks!
Brad

 

[Mike Painter]

I've not tried it but see no reason why a space cxould not be the default
value.
You could also default to "N/A" or some other value.
The problem with storing a space is that somebody could make a mistake and
leave a space in teh field when one is not uspposed to be there.


Sadly today's BASIC does not have near the functions that we did in the Good
Old Day's of Table Top BASIC.

Back then we had the IF statement as today but we also had the
IF I REALLY MEANT TO DO THAT statement.

Ther was also the IN CASE statement with ELSE DO WHAT I MEANT.

UTRV(x) (Use The Right Variable) returned the variable that was supposed to
be used. A big help when A-Z were the only variable names available.

 

[Dirk Goldgar]

We have an Access 2007 table that has a field in which NULLS are not
allowed.

In the table definition, we have set up a Validation Rule “Is Not Nullâ€.

Why didn't you just set the field's Required property to Yes? Was it
because you want to be able to specify the ValidationText message?

The catch is that in some situations, we want to store a space in this
field.

We are using a FORM that is based on a Query that is in turned based on
the
Table with the validation rule.

We have tried to change the field from NULL to spaces in the BeforeUpdate
event for
this field on the form. We can detect when the field is NULL, but Access
will not
let us plug in a space at this point in time.

What is the best way to do this?

A text box will strip off trailing spaces in the entry, so you can't just
enter a space. And you can't change the value of a control in its own
BeforeUpdate event, and if the field is being validated not to be Null, you
can't fix it up in its AfterUpdate event, either.

If this is an unusual occurrence, you could have a command button, or a less
obvious user action like double-clicking the control, that sets its value;
for example,

Private Sub cmdMakeSpace_Click()

Me!Desc = " "

End Sub

or

Private Sub Desc_DblClick(Cancel As Integer)

Me!Desc = " "

End Sub

Those will work, because you aren't making the change in the text box
itself.

 

[Jeff Boyce]

It would appear that you are trying to solve some business need by stuffing
a "space" in a field.

If you'll describe that business need in a bit more detail, folks here may
be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Gina Whipp]

Brad,

Perhaps this will give you what you want. If you know the length of the
field you can insert a Space *but* only if you know the length of the
field...

"D=" & Left([tDentalEffectiveDate] & Space(8),8) & ":" &
Left([tDentalTermDate] & Space(8),8) & ":" & Left([tRiderContractType] &
Space(1),1) & ":" & Left([tDentalGroupSectionID] & Space(18),18)

The above will result in...

D= : : :

....if the fields are blank or...

D=20080101: :3:9999956999999ZZZ1

....or any combination there about. To see more about the Space() function
see...

http://msdn.microsoft.com/en-us/library/k6ethaxs(VS.80).aspx

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

We have an Access 2007 table that has a field in which NULLS are not
allowed.

In the table definition, we have set up a Validation Rule “Is Not Nullâ€.

This works nicely.

The catch is that in some situations, we want to store a space in this
field.

We are using a FORM that is based on a Query that is in turned based on the
Table with the validation rule.

We have tried to change the field from NULL to spaces in the BeforeUpdate
event for
this field on the form. We can detect when the field is NULL, but Access
will not
let us plug in a space at this point in time.

What is the best way to do this?

Thanks!
Brad

 

[Brad]

Thanks to everyone for your help and ideas!

Here are more details of what we are trying to do.

We have used Access 2007 to build a "bridge" between two existing Third
Party systems (not built with Access).

Our Access system pulls the data from "System A" and formats records to feed
into "System B". In addition, our Access system enables our users to add
additional information into the records before the records are fed into
System B.

For example, in some cases, our users will need to add data for a customer's
PO Nbr.

If there is no data in PO Nbr as a record is imported from System A, PO Nbr
will be blanks (not null).

If our users don't touch a record, PO Nbr will remain blanks. (This is fine)
If our users add a valid PO Nbr everything works nicely.

The problem arises when a user starts to change a PO Nbr in our Access
system (via a Form), and then changes their mind, and uses the space bar on
the keyboard to space out their mistake. When this occurs, the PO Nbr field
is set to NULL, not spaces.

When this occurs, ugly things happen downstream (records are sorted
improperly, etc.)

We would like to have a way to easily plug spaces into the field in this
situation.

 

[John W. Vinson]

The problem arises when a user starts to change a PO Nbr in our Access
system (via a Form), and then changes their mind, and uses the space bar on
the keyboard to space out their mistake. When this occurs, the PO Nbr field
is set to NULL, not spaces.

Set the "Allow Zero Length String" of the field to Yes, and its Required
property to Yes as well.

 

[Brad]

John,

Thanks a million.

I owe ya a beer!

Setting the "Allow Zero Length String" of the field to Yes,
and its Required property to Yes as well did the trick.

I really appreciate your assistance.

Brad