SpaceCoastLife
asked on
Acceptable Criteria for Memo fields
Pretty sure I've dealt with this before but alas, no memory!
Using Access 2003
I have a table with 2 fields, a date field and a memo field I need to insert into a 2nd table with the same field name and type. A date exists in every record but in several of those records the memo field is blank.
I only want records with both date and memo to transfer but the memo field is particular when it comes to criteria. Conventional syntax like Not Null or <>" " doesn't work.
What's the trick?
Using Access 2003
I have a table with 2 fields, a date field and a memo field I need to insert into a 2nd table with the same field name and type. A date exists in every record but in several of those records the memo field is blank.
I only want records with both date and memo to transfer but the memo field is particular when it comes to criteria. Conventional syntax like Not Null or <>" " doesn't work.
What's the trick?
PatHartman
Are you sure that the memo field is actually null and not a Zero Length String (ZLS)?
ASKER
No but I set the table property not to accept zero length strings and it didn't help.
I don't have any problem:
Select * from yourtable
Where somefield Is Not Null;
Select * from yourtable
Where somefield Is Null:
Both work fine.
Select * from yourtable
Where somefield Is Not Null;
Select * from yourtable
Where somefield Is Null:
Both work fine.
How about using Len to judge if its empty or not
Len(YourMemo)>1
Len(YourMemo)>1
ASKER
Hartman: Ordinarily that works but not with memo fields. At least none of the memo fields in my db.
Len doesn't do it either.
Len doesn't do it either.
What do you get from Len ?
If nothing helps... there is a chance you have corruption...do a compact and repair
If nothing helps... there is a chance you have corruption...do a compact and repair
ASKER
compact and repair is the 1st thing I tried
ASKER
To clarify my len comment, the reason it doesn't work is because the value is not 0. In many cases it exceeds 500 even though it looks blank.
ASKER
Ok folks. I figured it out. The records "blank" in fact are not blank. There's a CR preceding the memo value. I'm good now.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.