Find Matches Query: Is there a maximum length to a search string?

  • Thread starter Thread starter Stuart E. Wugalter
  • Start date Start date
S

Stuart E. Wugalter

Hello:

I have a form with its record source being tblMasterTable contianing a MEMO
field named FASTA. I have used a double-click event to trigger a macro to
run the following query:

SELECT SNPID, OrderNum, RefNo, FASTA
FROM tblMasterTable
WHERE Forms!frmFASTA!FASTA=FASTA;

I can double-click on the FASTA field of the form and a table view will show
me that record and any other records with matching FASTA fields.

My problem is that the query will not work for FASTA fields which are larger
than 512 characters. Can anyone help me with this? TIA, Stuart

Stuart E. Wugalter
Statistician II
Building: PMB B100
Mail Code: 9011
Department: Zilkha Neurogenetic Institute
Division: Keck School of Medicine

University of Southern Californa
Telephone: (323) 442-1080
FAX: (323) 442-3054
E-Mail: (e-mail address removed)
 
Dear Stuart:

This sounds like a real "overload" of a problem.

Are your users actually expected to type text that is more than 512
characters long without misspelling a single word, leaving out or
adding any punctuation, or typing one space where two are expected, or
two spaces where one is expected? I'd like to help solve the
technical part of the problem, but are your expectations at the "user
interface" level actually reasonable?

I would also strongly caution you against naming a control on a form
with the same name as one of the data columns bound to that form.
This creates an ambiguity which can be extremely difficult to find,
with nasty consequences. I'm referring to the FASTA control on the
frmFASTA form. Many programmers use a prefix such as txt for text
box, cbo for combo box, etc. I like for my controls to alphabetize
according to the natural part of their name, so I have changed that to
a suffix. But you really should do something so the control is not
the same as the column name (field name).

Assuming you still want this, in spite of my suggested objections, you
need to be aware that memo columns are not as functional in all
respects as text values. This includes the ability to index them,
which might be an issue if you have a large number of rows of data.
If not indexed, the database must "scan" all the rows to look for your
criterion. This can be rather slow.

An alternative would be MSDE, which also comes with Access (starting
with the 2000 version) and allows text columns much longer, and then
can index them.

Enough for now. Please try to respond to each point I have made thus
far if I am to be of further help to you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top