Memo field vs Text Field

[Secret Squirrel]

I was using a text field for quite some time and decided to change it to a
memo field to allow my users to have the ability to type more than 255 char.
I also have a checkbox that is set to "True" when there is data typed into
this text field. I had this code in the "AfterUpdate" event of the textbox to
put a check in the checkbox when the textfield is not null. Ever since I
changed that field from text to memo this code won't fire anymore. Is there
any reason why this won't work with a memo field? Should the code be
re-written or will it just not work with a memo field?

If IsNull(Me![CEOComments]) Then
Me!chkCEOComments = False
Else
Me!chkCEOComments = True
End If

 

[Jerry Whittle]

In a straight SQL statement the IsNull function works properly with a memo
field. It returns -1 for null fields.

Try this:
If IsNull(Me![CEOComments]) = -1 Then

HOWEVER (getting onto my soap box to preach the Word of Codd) you really
shouldn't have a CEOComments field. If data in one field is dependent upon
data in another within the same table, that table is not properly normalized.
You could easily just check if the field is null or not as needed (one you
get past this little memo problem). As Access doesn't have table level
triggers, you could easily run into inconsistant data if someone, for
example, inserts data into the memo field through a query or directly into
the table.

Also consider if you really need the data in a Memo field. Memo fields have
other weaknesses such as searching/sorting/grouping on them and an increased
potential for corruption issues. I only use them when there is not other
practial choice.

 

[Secret Squirrel]

Hi Jerry,

That didn't work. It will check the box if data is in the memo field but
when the data is removed the check stays in the checkbox.

This field can only be populated from my main form. Users don't have the
ability to access this field through queries or a table directly. This
comments field is on my second form and is used for additional comments. But
from the main screen users can't tell if there are comments on the second
page so I was using a checkbox on the first form to let them know if there
were comments on the second page instead of them having to open the second
form every time. I just found it easier to do it this way as a notifier of
sorts. But since I changed it from a text field to a memo field is where the
problem starting happening. The reason I chose to use a memo field is because
the length of characters you can use with a memo field. I'm open to
suggestions to an easier way or possibly some other code to trigger this
checkbox when data is entered, etc.

In a straight SQL statement the IsNull function works properly with a memo
field. It returns -1 for null fields.

Try this:
If IsNull(Me![CEOComments]) = -1 Then

HOWEVER (getting onto my soap box to preach the Word of Codd) you really
shouldn't have a CEOComments field. If data in one field is dependent upon
data in another within the same table, that table is not properly normalized.
You could easily just check if the field is null or not as needed (one you
get past this little memo problem). As Access doesn't have table level
triggers, you could easily run into inconsistant data if someone, for
example, inserts data into the memo field through a query or directly into
the table.

Also consider if you really need the data in a Memo field. Memo fields have
other weaknesses such as searching/sorting/grouping on them and an increased
potential for corruption issues. I only use them when there is not other
practial choice.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I was using a text field for quite some time and decided to change it to a
memo field to allow my users to have the ability to type more than 255 char.
I also have a checkbox that is set to "True" when there is data typed into
this text field. I had this code in the "AfterUpdate" event of the textbox to
put a check in the checkbox when the textfield is not null. Ever since I
changed that field from text to memo this code won't fire anymore. Is there
any reason why this won't work with a memo field? Should the code be
re-written or will it just not work with a memo field?

If IsNull(Me![CEOComments]) Then
Me!chkCEOComments = False
Else
Me!chkCEOComments = True
End If