Query question in Access

Hi Experts,
I have a question about query.  I'm currently working in a query.  The query has 6 tables.  one of the table has a field name "Notes".  I want to pull all the records from this table that has Notes.  I used in the criteria as "Not Is Null and <> "", I also tried Is Not Null and <>"", they both gave me error message "ODBC-Failed".  I've tired "Is Not Null" but it gave me records that is empty.  I'm not sure what I did wrong.

Thanks,
urjudoAsked:
Who is Participating?
 
Nick67Commented:
it showed all the records not those only has notes.
Good! That was the idea, the first step.
So NotesLength has values, 0 and 1, right?
Next step

SELECT Event_Pleading.PDCASENUM, Event_Pleading.PDDOCKET, Event_Pleading.PDNOTES, Len(left(Nz([Notes],""),1)) as NotesLength
  FROM (Custodial_Parent INNER JOIN Child ON Custodial_Parent.CPCASENUM = Child.CHCASENUM) INNER JOIN Event_Pleading ON (Child.CHCASENUM = Event_Pleading.PDCASENUM) AND (Child.CHDOCKET = Event_Pleading.PDDOCKET)
  WHERE (((Event_Pleading.PDCASENUM)=[forms]![frmEventView_Pleading]![PDCASENUM] and Len(left(Nz([Notes],""),1)) = 1 )));

Does this work?
Or throw an error?
0
 
Nick67Commented:
You've still left a lot of detail out.
There are many, many things that can be "ODBC-Failed"
Trying to filter on a memo field can be one of them.

Let's go sideways
Add another column to your query
NotesLength:Len(left(Nz([Notes],""),1))
Run the query
Does it work?
It is does, try for a criteria of
=1

We want something relatively efficient
We have to handle Null, so
Nz([Notes],"") will do that
We don't want blanks, so a Len() will give us how many characters there are
On the other hand we don't really want to do the expensive thing of calculating the full [Notes] field.
So we'll just figure out the length of the first (if there is one) character.

This should run ok, and get around the quirks of a memo type field, if that's the problem
0
 
Walter RitzelSenior Software EngineerCommented:
Can you please share the table structures (script) and the query?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
urjudoAuthor Commented:
here is the query:
SELECT Event_Pleading.PDCASENUM, Event_Pleading.PDDOCKET, Event_Pleading.PDNOTES
FROM (Custodial_Parent INNER JOIN Child ON Custodial_Parent.CPCASENUM = Child.CHCASENUM) INNER JOIN Event_Pleading ON (Child.CHCASENUM = Event_Pleading.PDCASENUM) AND (Child.CHDOCKET = Event_Pleading.PDDOCKET)
WHERE (((Event_Pleading.PDCASENUM)=[forms]![frmEventView_Pleading]![PDCASENUM]) AND ((Event_Pleading.PDNOTES) Is Not Null));
0
 
urjudoAuthor Commented:
I tried both ways from Nick67, it still not working.
0
 
Nick67Commented:
So first you tried:
SELECT Event_Pleading.PDCASENUM, Event_Pleading.PDDOCKET, Event_Pleading.PDNOTES, Len(left(Nz([Notes],""),1)) as NotesLength
 FROM (Custodial_Parent INNER JOIN Child ON Custodial_Parent.CPCASENUM = Child.CHCASENUM) INNER JOIN Event_Pleading ON (Child.CHCASENUM = Event_Pleading.PDCASENUM) AND (Child.CHDOCKET = Event_Pleading.PDDOCKET)
 WHERE (((Event_Pleading.PDCASENUM)=[forms]![frmEventView_Pleading]![PDCASENUM])));

Did it throw an error?
What is the exact error?
0
 
urjudoAuthor Commented:
to Nick67, I tried it.  it showed all the records not those only has notes.
0
 
urjudoAuthor Commented:
to Nick67, ok, this time works.  but I was wondering, I used that criteria before (is not null), it worked but now all the sudden is not working, I just wondering what make s wrong like that?
0
 
aikimarkCommented:
You should be able to use this
Len("" & [Notes]) <> 0

Open in new window

or this
Len("" & Trim([Notes])) <> 0

Open in new window

0
 
Nick67Commented:
@aikimark
What if it's a memo field with 32K characters?
I'd be interested to see SQL sniffing of exactly how the competing alternatives get rendered.
Trying to do string calculations on the smallest string possible struck me as the best for efficiency

@urjudo
I used that criteria before (is not null), it worked but now all the sudden is not working, I just wondering what make s wrong like that?
Which do you mean:
a) I have used IS NOT Null and <> '' at other times and with other fields and had no problems.  Why is the Event_Pleading.PDNOTES different
or
b)I have used IS NOT Null and <> '' in other queries on Event_Pleading.PDNOTES and had no problems.  Why is it no longer working on Event_Pleading.PDNOTES?

I suspect you mean A)
Memo fields (text fields that can take MORE than 255 characters) are finicky -- and they are finicky on purpose.  String operations are very expensive for a database engine, and they do tend to get more expensive geometrically -- that is a string twice as long is four times the work.  In general, database engines are built to run fast and the designers have made a tradeoff.  You can generally--although it can be slower--get a database engine to be willing to do stuff with strings <256 characters without any messing around.  Longer than that, though, and the engines want to do something to keep from being drastically slow.  So they force the query programmer to do his/her homework.

If you tried
SELECT DISTINCT Event_Pleading.PDNOTES from Event_Pleading
you would quickly discover that all Access is going to return to you is the first 255 characters.
The memo field has been truncated.
If you tried to export Event_Pleading.PDNOTES to Excel, you'd find the same thing.

The database engine isn't going to let you create a query that will flat-line the server and tie it up all day.

Trying to set criteria on a memo field is another one of those operations.
What you wanted wasn't so extreme
'Is Not Null and <> ""
But thing what would happen if you tried
Like *gee this query is slow*
as a criteria on a memo field where each row is thousands of characters long!

So, in general, filtering and joining on memo fields is either not allowed, results in truncation, or is REALLY slow.

There are techniques to work around that.  Some guys will put the memo field in a table by itself with a foreign key back to the main table and do not permit nulls or empty strings.  Then the problem goes away.  Another technique is to add a Boolean field 'HasNotes' that the user has to make true before they can enter stuff in the memo field.  Then you can check against the Boolean field for a non-null memo field, and not the field itself.

Or stuff like I've posted or as @aikimark has posted.
One way or another memo fields present some unique challenges that you need to be aware of.

Nick67
0
 
aikimarkCommented:
Should work with either a memo or text field
0
 
urjudoAuthor Commented:
Thank you for your helped also for the very detail explanation!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.