Parameter query does not show all data in memo fields with vbcrlf

[GrandpaRay]

SELECT Performances.Perf_Key, Performances.Perf_Date, Performances.Perf_Dancers
FROM Performances
WHERE (((Performances.Perf_Dancers) Like [Enter Search Criteria For Dancers]))
ORDER BY Performances.Perf_Dancers;

The results from above query do not show all names in memo fields that are
separated by chr(13) chr(10) or vbCrLf. The first name in a memo field is all
that appears. I do not want to remove the separators in the .mdb. Thanks

 

[Douglas J. Steele]

You sure it's only showing the first name? How are you displaying the
results of the query? If the field is supposed to contain multiple lines of
data, have you made the control large enough to display multiple lines?

 

[John W. Vinson]

SELECT Performances.Perf_Key, Performances.Perf_Date, Performances.Perf_Dancers
FROM Performances
WHERE (((Performances.Perf_Dancers) Like [Enter Search Criteria For Dancers]))
ORDER BY Performances.Perf_Dancers;

The results from above query do not show all names in memo fields that are
separated by chr(13) chr(10) or vbCrLf. The first name in a memo field is all
that appears. I do not want to remove the separators in the .mdb. Thanks

You don't have any wildcards in the LIKE clause so this will - I expect -
return only exact matches; or is the user expected to enter

*Diane*

to find all records where any dancer named Diane is mentioned?

The reason you're having difficulty is that you're jamming a many
(performances) to many (dancers) relationship into one big blobby memo field,
rather than properly having three tables reflecting the relationship.

 

[GrandpaRay]

Doug,
Yes, it's only showing one name per memo field. I was using msaccess 2003 to
run a simple query to display all names from the table in question. This
data was created using a vb6 application where only the first name of each
person was entered in a multiline text box , hence the vbcrlf, the textbox
was then saved in a recordset field (called dancers in the table) that was
used to update the mdb file. I use the msaccess SQL capability in designing
queries to eventually port to my vb application. It's a learning tool for
me.
--
Grandpa Ray

You sure it's only showing the first name? How are you displaying the
results of the query? If the field is supposed to contain multiple lines of
data, have you made the control large enough to display multiple lines?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

SELECT Performances.Perf_Key, Performances.Perf_Date,
Performances.Perf_Dancers
FROM Performances
WHERE (((Performances.Perf_Dancers) Like [Enter Search Criteria For
Dancers]))
ORDER BY Performances.Perf_Dancers;

The results from above query do not show all names in memo fields that are
separated by chr(13) chr(10) or vbCrLf. The first name in a memo field is
all
that appears. I do not want to remove the separators in the .mdb. Thanks

 

[GrandpaRay]

John,
May I send you a word document of just one performance record showing what
it might look like in a report with explanations of how each field is related
to the mdb file?
--
Grandpa Ray

SELECT Performances.Perf_Key, Performances.Perf_Date, Performances.Perf_Dancers
FROM Performances
WHERE (((Performances.Perf_Dancers) Like [Enter Search Criteria For Dancers]))
ORDER BY Performances.Perf_Dancers;

The results from above query do not show all names in memo fields that are
separated by chr(13) chr(10) or vbCrLf. The first name in a memo field is all
that appears. I do not want to remove the separators in the .mdb. Thanks

You don't have any wildcards in the LIKE clause so this will - I expect -
return only exact matches; or is the user expected to enter

*Diane*

to find all records where any dancer named Diane is mentioned?

The reason you're having difficulty is that you're jamming a many
(performances) to many (dancers) relationship into one big blobby memo field,
rather than properly having three tables reflecting the relationship.

 

[John W. Vinson]

John,
May I send you a word document of just one performance record showing what
it might look like in a report with explanations of how each field is related
to the mdb file?

Well... I'd prefer that you post a text description here. I'm a self employed
consultant, and normally provide private email support only for paying
customers.

The appearance of a report is completely irrelevant in any case, and a field
isn't "related to a mdb file". Could you instead just post the names and
fieldnames (and datatypes) of the relevant table fields, and the SQL view of
the query?

 

[GrandpaRay]

SELECT Performances.Perf_Date, Performances.Perf_Dancers
FROM Performances
WHERE (((Performances.Perf_Dancers) Like [Enter Search Criteria For Dancers]))
ORDER BY Performances.Perf_Date, Performances.Perf_Dancers;

Parameter [N*]
Query result looks something like this:

Perf_Date Per_Dancer
06/19/1999 Naomi
07/04/1999 Nancy
07/16/1999 Nichole

Perf_Dancers is a memo field. Each performance date above has several dancer
names
Seperated by vbCrLf . If the parameter selection is “N*†then only those
records with the first dancer name starting with “N†for each date is
selected the other “Nâ€s are not shown because they were not first in the list
for that date. I want to see all dancers whose name starts with Nâ€.

 

[John W. Vinson]

SELECT Performances.Perf_Date, Performances.Perf_Dancers
FROM Performances
WHERE (((Performances.Perf_Dancers) Like [Enter Search Criteria For Dancers]))
ORDER BY Performances.Perf_Date, Performances.Perf_Dancers;

Parameter [N*]
Query result looks something like this:

Perf_Date Per_Dancer
06/19/1999 Naomi
07/04/1999 Nancy
07/16/1999 Nichole

Perf_Dancers is a memo field. Each performance date above has several dancer
names
Seperated by vbCrLf .

Why?

You're having trouble because your Perf_Dancer field is not atomic. Your table
structure is not designed to work as a relational database!

A better structure would have THREE tables:

Performances
PerformanceID Primary Key
Perf_Date
<other info about the performance itself, venue, comments, etc.>

Dancers
DancerID Primary Key
LastName
FirstName
<other bio information>

Performers
PerformanceID <link to Performances>
DancerID <link to Dancers>
<other info about this dancer in this performance, e.g. role, evaluation of
performance, comments>

If the parameter selection is “N*” then only those
records with the first dancer name starting with “N” for each date is
selected the other “N”s are not shown because they were not first in the list
for that date. I want to see all dancers whose name starts with N”.

If your Perf_Dancer field contains

Alexis
Charles
Lorene
Nancy
Susan

then the search criterion

LIKE N*

will not match. The text string does NOT begin with N; it begins with A.
Access does not consider these names as five records; they are *ONE RECORD*,
consisting of a string of letters, maybe some blanks, carriage return
characters, linefeed characters. The N in Nancy is the 26th character of the
string.

Your criterion would need to be

LIKE "*N" OR LIKE Chr(13) & Chr(10) & "N*"

to find EITHER a letter N at the start of the first name, or a letter N
immediately following a carriage return-linefeed pair.