What Dlookup won't lookup

[smercer]

Hi all

I just had a situation where I had a dlookup could not search in a field where it contains an apostraphe {'} (the button next to the enter key).

I got around that by having the ascii code changed with the following code:

If intKey = 39 Then intKey = 96

I used Mile's example in the code section of the forum, and adjusted it to suit myself.

Anyway, I do have a question; are there any other symbols that cannot be used in the field contents so Dlookup can be used?

Thanks in advance

 

[WayneRyan]

S,

The reason that happens is that SQL requires:

Where SomeField = 'Jones'

That's OK, but if you need:

Where SomeField = 'O'Conner'

Then SQL can't parse it because it uses the single-quote as
a delimiter and you have a single-quote in the data.

If you change it to:

Where SomeField = "O'Conner"

you'll be OK, because SQL is flexible enough to use the double-quotes
as delimiters when it encounters them.

However, even that won't work if you have:

Where SomeField = "Joe Said: "Hi There""

The bottom line - Don't allow either single or double quotes in
your data. They will produce undesireable results when SQL tries
to parse your statements. Domain functions (DLookUp, DMax, DCount, etc.)
are "translated" to SQL so they're included.

Additionally, I have seen problems with data that included things like
"[A", because SQL gets them confused with its pattern-matching verbage.

Also, some things like the TAB and Return keys, which can be very difficult for
a user to enter.

I'd be interested to hear of any others.

Wayne

 

[smercer]

Wyane: I forgot to mention that that IF statement I mentioned earlier replaces the apostraphe (')with the (`) (the button above the tab key)

I thought this was a good idea because the user will not notice any difference.

 

[WayneRyan]

S,

I've also done that. I had a little While loop that did that same replacement
using the AfterUpdate event.

Wayne

 

[smercer]

S,

I've also done that. I had a little While loop that did that same replacement
using the AfterUpdate event.

Wayne

Sounds complex. I use the OnKeyPress event and I only need one line to do it in (in a called module).