Board index » delphi » Locate fails when searching text field with # character

Teller Coates

Delphi Developer
Sat, 27 Sep 2003 23:15:27 GMT

Locate fails when searching text field with # character

Hi,

I'm using Locate (seek is hopeless) on an Access2000 mdb to search for text
fields that match a certain string.  This works great unless the text I'm
searching for contains a pound # symbol, in which case it consistently
fails.

What does Access/ADO have against Finding text with the # character?  How
would you work around this?

Thanks,
Teller

 
accma

Delphi Developer
Sun, 28 Sep 2003 00:08:53 GMT

Re:Locate fails when searching text field with # character


#  is the date identifier in Access. ex. #01/04/1998#
Probably the SQL engine is getting confused by your select. Have you tried
using the ASCII value for "#"?

Quote
"Teller Coates" <tel...@2020insight.net> wrote in message

news:3ad323a3$1_2@dnews...
Quote
> Hi,

> I'm using Locate (seek is hopeless) on an Access2000 mdb to search for
text
> fields that match a certain string.  This works great unless the text I'm
> searching for contains a pound # symbol, in which case it consistently
> fails.

> What does Access/ADO have against Finding text with the # character?  How
> would you work around this?

> Thanks,
> Teller

Teller Coate

Delphi Developer
Sun, 28 Sep 2003 02:52:41 GMT

Re:Locate fails when searching text field with # character


Um...forgive me, but what does that look like?  Before, I had:

if MyADOTable.Locate('Caption', 'What do you believe should be this
person''s #1 priority for development?', [loCaseInsensitive])

Now I've tried:

if MyADOTable.Locate('Caption', 'What do you believe should be this
person''s chr$(35)1 priority for development?', [loCaseInsensitive])

and also variations, like just chr(35)...no luck...forgive my ignorance, but
how does one go about expressing this ascii character?

Thanks again,
Teller

Quote
"accman" <acc...@tampabay.rr.com> wrote in message

news:3ad32fb7$1_1@dnews...
Quote
> #  is the date identifier in Access. ex. #01/04/1998#
> Probably the SQL engine is getting confused by your select. Have you tried
> using the ASCII value for "#"?

> "Teller Coates" <tel...@2020insight.net> wrote in message
> news:3ad323a3$1_2@dnews...
> > Hi,

> > I'm using Locate (seek is hopeless) on an Access2000 mdb to search for
> text
> > fields that match a certain string.  This works great unless the text
I'm
> > searching for contains a pound # symbol, in which case it consistently
> > fails.

> > What does Access/ADO have against Finding text with the # character?
How
> > would you work around this?

> > Thanks,
> > Teller

Thérèse Hanque

Delphi Developer
Sun, 28 Sep 2003 05:24:56 GMT

Re:Locate fails when searching text field with # character


Hi Teller,

Quote
> if MyADOTable.Locate('Caption', 'What do you believe should be this
> person''s #1 priority for development?', [loCaseInsensitive])

The problem is effectively that ADO interprets the # sign as a delimiter
not only for dates but also for strings. I don't see how the ASCII code
can help with that, because the problem is not at a Delphi level but at an
ADO level. For a single field, Locate use the ADO Find method, which has a
strict syntax - see here for details:

http://msdn.microsoft.com/library/psdk/dasdk/mdam2aun.htm

Unfortunately this page does not suggest solutions for your problem...

Thrse

FL

Delphi Developer
Sun, 28 Sep 2003 22:43:47 GMT

Re:Locate fails when searching text field with # character


Just tested using Locate on my machine with an Access 2K database. It
runs OK. I cannot reproduce the problem you're experiencing, but I'm
using ADODataset.

Francisco

Quote
"The Hanquet" wrote:

> Hi Teller,

> > if MyADOTable.Locate('Caption', 'What do you believe should be this
> > person''s #1 priority for development?', [loCaseInsensitive])

> The problem is effectively that ADO interprets the # sign as a delimiter
> not only for dates but also for strings. I don't see how the ASCII code
> can help with that, because the problem is not at a Delphi level but at an
> ADO level. For a single field, Locate use the ADO Find method, which has a
> strict syntax - see here for details:

> http://msdn.microsoft.com/library/psdk/dasdk/mdam2aun.htm

> Unfortunately this page does not suggest solutions for your problem...

> The

Other Threads

1. Text searching in character, memo fields

2. Accented characters in search field / soundex search

3. searching text fields for partial matches anywhere in field

4. ADO Locate fails on empty DateTime fields

5. BDE - Locating a field with NIL or NULL string fails

6. Table.Locate options for multiple field searches

7. How to use Table.Locate method for more than one fields search

8. search on 2 fields using Locate?

9. Locate error when search > field length

10. Q: SQL TEXT Field > 32769 characters