saving spaces on a table

[ryetee]

I want to be but to save a space in a field on a table but every time I do it appears to be a NULL and therefore fails a validation rule that I wish to preserve

 

[MarkK]

Yes, I believe that a textbox automatically trims trailing spaces. The problem with using a space as a critical piece of data is that it is invisible. It looks exactly like a Null in a textbox. Whatever a single space indicates in your system, I would use some other means to signify its presence.

 

[gemma-the-husky]

how is your table/field set up. (allow zls/required)

you probably do not need an actual space. You can have a zero length string. a zero length string is not considered the same as a null by access

eg

https://599cd.com/tips/access/null-zls/

 

[ryetee]

Yes, I believe that a textbox automatically trims trailing spaces. The problem with using a space as a critical piece of data is that it is invisible. It looks exactly like a Null in a textbox. Whatever a single space indicates in your system, I would use some other means to signify its presence.

I'm using a "." at the moment but a space would be more elegant.
The trimming actually takes place on the table. As soon as I put a space in the field it gets trimmed.

 

[ryetee]

how is your table/field set up. (allow zls/required)

you probably do not need an actual space. You can have a zero length string. a zero length string is not considered the same as a null by access

eg

https://599cd.com/tips/access/null-zls/

I have Allow zero length set to null.If I put a space in the field it seems to 'disappear'. The table is used in a combo box and it does look like there is a blank/space line in there. But if it is selected it's failing a piece of code -

If IsNull(Me.Organisation) Or Me.Organisation = "" Then
MsgBox "You must enter an Organisation or use the Cancel option to exit the form!", vbOKOnly

I'm not sure why Me.Organisation = "" is in there in the 1st place but I was presuming that putting a space in the field would make this statement FALSE.

I'll take a look at the link when I get 5 mins...

 

[namliam]

a null value is distinctly different from a Zero Length String (ZLS or "")

Only a space is not any real information is it? The database obviously wants "real" data there?

 

[CJ_London]

if you require a user to enter an organisation - why are you happy to allow them to enter a space? You might as well not have the requirement.

Clearly you can find records with a space but you can just as easily find them with a null or ""

 

[smig]

I'm using a "." at the moment but a space would be more elegant

Space is not elegant at all.
As MarkK said, it looks like Null.
Leaving it Null is elegant.

 

[gemma-the-husky]

I have Allow zero length set to null.If I put a space in the field it seems to 'disappear'. The table is used in a combo box and it does look like there is a blank/space line in there. But if it is selected it's failing a piece of code -

If IsNull(Me.Organisation) Or Me.Organisation = "" Then
MsgBox "You must enter an Organisation or use the Cancel option to exit the form!", vbOKOnly

I'm not sure why Me.Organisation = "" is in there in the 1st place but I was presuming that putting a space in the field would make this statement FALSE.

I'll take a look at the link when I get 5 mins...

first bit in bold. what do you mean, allow zero length set to NULL. It's either set to yes or no.

if your field is set to "required = true", then null is not allowed
if allow ZLS is true, then you can still have a value of "", otherwise you are forced to enter a specific value.

if you want to force an organisation to be entered simply set "required=true" and "allow ZLS=false". However if you have blanks at the moment, you won't be able to set this rule, as it will be broken by existing data.

you can combine this more simply

If IsNull(Me.Organisation) Or Me.Organisation = "" Then
MsgBox "You must enter an Organisation or use the Cancel option to exit the form!", vbOKOnly

using the nz function, as

If nz(Me.Organisation,"") = "" Then
etc


if you are just trying to prevent nulls and want to allow a "blank" value, simply do this in the forms beforeupdate event

organisation = nz(organisation,"")

now you won't have any nulls at all, but you will have blanks. So your field must be set to "required=true" (to prevent the null) and "allow zls=true"

I don't understand the point about the combo box really. That's just a natural result of your "business requirements".

I hope all this makes sense

 

[smig]

If IsNull(Me.Organisation) Or Me.Organisation = "" Then
MsgBox "You must enter an Organisation or use the Cancel option to exit the form!", vbOKOnly

using the nz function, as

If nz(Me.Organisation,"") = "" Then
etc

I more like:
If Trim(Me.Organisation & "") = ""


Many ways to get the same goal.

 

[ryetee]

first bit in bold. what do you mean, allow zero length set to NULL. It's either set to yes or no.

if your field is set to "required = true", then null is not allowed
if allow ZLS is true, then you can still have a value of "", otherwise you are forced to enter a specific value.

if you want to force an organisation to be entered simply set "required=true" and "allow ZLS=false". However if you have blanks at the moment, you won't be able to set this rule, as it will be broken by existing data.

you can combine this more simply

If IsNull(Me.Organisation) Or Me.Organisation = "" Then
MsgBox "You must enter an Organisation or use the Cancel option to exit the form!", vbOKOnly

using the nz function, as

If nz(Me.Organisation,"") = "" Then
etc


if you are just trying to prevent nulls and want to allow a "blank" value, simply do this in the forms beforeupdate event

organisation = nz(organisation,"")

now you won't have any nulls at all, but you will have blanks. So your field must be set to "required=true" (to prevent the null) and "allow zls=true"

I don't understand the point about the combo box really. That's just a natural result of your "business requirements".

I hope all this makes sense

Apologies wrote this is a hurry! I meant I have Allow zero length set to yes.
Thanks for
the rest of the post - I'll need to read it a few more times!

Just to clarify why I want to do this. In the main organisation is a required field. Occasionally though it can be 'blank'. I want to force the user to put something in (eg blank) as otherwise they will just get lazy nd not put anything in at all. Now I could get them to put something else in such as "." but then I've got the headache (albeit a small one) of changing the "." to " " in things such as reports etc etc and therefore (Smig!!) more elegant (probably the wrong word!).

 

[namliam]

why force your users to put nonsense into a field that should just hold NOTHING aka NULL?

 

[ryetee]

why force your users to put nonsense into a field that should just hold NOTHING aka NULL?

See above!!!
If I allow them NOT to put anything in then they won't. I want to force them to put nothing in!!!

The only way I can see in doing this is to make it a compulsory field but allow them top select blank. If they have to select something they will select the right thing. If it's not compulsory then they won't/

 

[CJ_London]

Playing devils advocate

If I allow them NOT to put anything in then they won't. I want to force them to put nothing in!!!

So why not force them to put something a bit more 'meaningful' like 'N/A' or 'None'

 

[jdraw]

A slightly different view of this. What is the associated business rule?
If it is a required entry, and you have set Required = True, then have the user enter data.
As others have said, perhaps N/A is appropriate. But a stored space???? If you could store it, how would display it????

What does this all mean in "business terms"? Database should support the business.

 

[gemma-the-husky]

See above!!!
If I allow them NOT to put anything in then they won't. I want to force them to put nothing in!!!

The only way I can see in doing this is to make it a compulsory field but allow them top select blank. If they have to select something they will select the right thing. If it's not compulsory then they won't/

bit in bold. That just sounds crazy. you want to force them to indicate the selection really is blank?

so maybe instead of free text, give them a combo box, with one of the options being "no item selected"

 

[GinaWhipp]

Thinking out loud here...

Why not just put a message in the On_Exit or After_Update of the Text Box. Something like...

*Did you mean to leave this empty?*

If I knew I was going to get that message every time I was too lazy to fill in a field believe me if I knew the data it wanted I would enter it.

Side note, if this is a Combo Box you can set the first value as Blank as long as it's not the PK and then your Users can select something. You could then use validation to insure they selected something. And that would be easy enough to work with for reports.

 

[ryetee]

Playing devils advocate

So why not force them to put something a bit more 'meaningful' like 'N/A' or 'None'

I can but then I have to change all the reports and forms to convert to a blank so allowing them to choose that in the first place is a Brucie bonus.

 

[ryetee]

bit in bold. That just sounds crazy. you want to force them to indicate the selection really is blank?

so maybe instead of free text, give them a combo box, with one of the options being "no item selected"

I know it sends crazy but we're talking about people who want to get on with the job and filling out the accompanying data is a distraction. You'll always get people taking the easy route if it's there.

I can do but see my other post reply to CJ

 

[ryetee]

Thinking out loud here...

Why not just put a message in the On_Exit or After_Update of the Text Box. Something like...

*Did you mean to leave this empty?*

If I knew I was going to get that message every time I was too lazy to fill in a field believe me if I knew the data it wanted I would enter it.

Side note, if this is a Combo Box you can set the first value as Blank as long as it's not the PK and then your Users can select something. You could then use validation to insure they selected something. And that would be easy enough to work with for reports.

I have and am considering this. I was going to make it more of a pain by putting "are you sure you want to leave this empty?" I'll not put "are you really sure......" though!!