fields empty are not empty??? (1 Viewer)

[mloucel]

Hello Gurus:

I have a weird issue [not life and dead], I have a database that when I EDIT the record on a form, the user has to press a button to save, I do that so that I have the opportunity to check some fields that the end user MUST fill so I do a quick : "If isnull(XYZ) ", which works many times, but somehow, sometimes it doesn't see the field empty, even though it is empty, just for fun I did a test, I entered a new record and on purpose I left 3 fields empty, my routine detected perfectly 2 of them and somehow the 3rd one, just simply ignore and saved the record, so I went to my EDIT FORM, recall the record and without doing anything I clicked SAVE, and it did, so I recall the record again, I add spaces, saved again, recall and hit the delete key a few times [40 or 50 in a 15 chr field], then hit save, and it did it again, then I add 1 letter, save no problem as it should, then open again and delete the letter, this time gave me the warning..
So I added " If isnull(XYZ) or XYZ="" ", and somehow the error never happened again, coincidence ?, I removed the = "", and after 4 or 5 new records it happened again, I have tried with other fields and get the same issue, so is not just 1 field I have detected at least 2 more.
This only happens when there is a new record, using "DoCmd.GoToRecord , , acNewRec" something is placed in a field and is invisible, but when I use the XYZ="" then the problem is solved, i am forced to use that, but I do not understand why isnull() does not work.

Any Ideas?

 

[MajP]

Null and zero length string are not the same thing that is why.

https://599cd.com/tips/access/null-zls/#:~:text=Q%3A%20What%20is%20the%20difference,and%20a%20Zero%20Length%20String%3F&text=A%3A%20The%20difference%20between%20a,or%20%22no%20value%20YET.%22

A better check is
If somecontrol & "" = ""
That catches both

 

[MajP]

Set this to NO

AllowZeroLength property

Office VBA reference topic

learn.microsoft.com

 

[Gasman]

Hello Gurus:

I have a weird issue [not life and dead], I have a database that when I EDIT the record on a form, the user has to press a button to save, I do that so that I have the opportunity to check some fields that the end user MUST fill so I do a quick : "If isnull(XYZ) ", which works many times, but somehow, sometimes it doesn't see the field empty, even though it is empty, just for fun I did a test, I entered a new record and on purpose I left 3 fields empty, my routine detected perfectly 2 of them and somehow the 3rd one, just simply ignore and saved the record, so I went to my EDIT FORM, recall the record and without doing anything I clicked SAVE, and it did, so I recall the record again, I add spaces, saved again, recall and hit the delete key a few times [40 or 50 in a 15 chr field], then hit save, and it did it again, then I add 1 letter, save no problem as it should, then open again and delete the letter, this time gave me the warning..
So I added " If isnull(XYZ) or XYZ="" ", and somehow the error never happened again, coincidence ?, I removed the = "", and after 4 or 5 new records it happened again, I have tried with other fields and get the same issue, so is not just 1 field I have detected at least 2 more.
This only happens when there is a new record, using "DoCmd.GoToRecord , , acNewRec" something is placed in a field and is invisible, but when I use the XYZ="" then the problem is solved, i am forced to use that, but I do not understand why isnull() does not work.

Any Ideas?

That is what the Form's Before_Update event is for?

 

[Mike Krailo]

Absolutely what Gasman said. Use the forms before update to prevent user from saving records with bad data. Then use that property that MajP suggested to disallow zero length strings. The point though is not to put your validation code in the "on click" event of your save button. Don't do that, otherwise Pat Hartman will be on you like white on rice.

 

[Pat Hartman]

Here's two videos along with a sample database to help you to learn about form events. The views are about why you should use the BeforeUpdate event for validation.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[mloucel]

Null and zero length string are not the same thing that is why.

https://599cd.com/tips/access/null-zls/#:~:text=Q%3A%20What%20is%20the%20difference,and%20a%20Zero%20Length%20String%3F&text=A%3A%20The%20difference%20between%20a,or%20%22no%20value%20YET.%22

A better check is
If somecontrol & "" = ""
That catches both

Good old Richard..., I

Null and zero length string are not the same thing that is why.

https://599cd.com/tips/access/null-zls/#:~:text=Q%3A%20What%20is%20the%20difference,and%20a%20Zero%20Length%20String%3F&text=A%3A%20The%20difference%20between%20a,or%20%22no%20value%20YET.%22

A better check is
If somecontrol & "" = ""
That catches both

Can you elaborate [ If somecontrol & "" = "" ], sorry I am still learning and I do not have much experience.

 

[mloucel]

That is what the Form's Before_Update event is for?

Thanks, but I a work in progress, sorry but I understand the concept of the before update event, but in my head I don't know how to apply it, my LOGIC says that when the user clicks the save button I can, check and recheck as many times as needed, but in the event, I'm lost.., really lost, I would love to make better code and use your suggestions, of course you guys know more than me, I imagine eventually I will find and example of wht you are suggesting and I can learn, in the meantime I will have to stick to my button.

Thank you @Gasman

 

[MajP]

If isnull(XYZ) or XYZ=""

This can be written as one check

if (XYZ & "") = ""

That is because if it is Null then Null & "" = ""
if it already is "" then "" & "" = ""
So that check works for both cases
A third option is

if trim(xyz & "") = ""

This checks for a a third case too. It is rare but possible to get a space, " " which is something different than a ZLS, "". So the above checks for Null, "", or " ".
That is because trim(" " & "") = ""

 

[Gasman]

Thanks, but I a work in progress, sorry but I understand the concept of the before update event, but in my head I don't know how to apply it, my LOGIC says that when the user clicks the save button I can, check and recheck as many times as needed, but in the event, I'm lost.., really lost, I would love to make better code and use your suggestions, of course you guys know more than me, I imagine eventually I will find and example of wht you are suggesting and I can learn, in the meantime I will have to stick to my button.

Thank you @Gasman

Watch the videos mentioned in post #6

 

[mloucel]

This can be written as one check

if (XYZ & "") = ""

That is because if it is Null then Null & "" = ""
if it already is "" then "" & "" = ""
So that check works for both cases
A third option is

if trim(xyz & "") = ""

This checks for a a third case too. It is rare but possible to get a space, " " which is something different than a ZLS, "". So the above checks for Null, "", or " ".
That is because trim(" " & "") = ""

Darn... best explain ever.. thanks man, I use the TRIM option seems to be the most sensible, working like a charm.

THANKS SO MUCH..