Report shows first in a list not the user entry

[cookie5]

I have a field that has a single or a list of entries. When I query the field using
Where[batchno] like '*'&[enter no]&'*'
The query shows the data for the batchno entered but if the batch no is not the first in the list when I run the report it shows the first batch no in the list, not the one the user entered. For example

record 1 batch no = d6v04211
record 2 batch no = lv604321,d6v04211,xl603211

When I run the query for batch d6v04211, it shows in the datasheet view both records.
When I run the report, it actually looks like I had queried lv604321.
What am I doing wrong?

 

[dhookom]

Are you using a list box in a report? If so, why not change it to a text box?

I could be way off since your question is difficult to understand. Fields should have a single entry, no a list of entries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cookie5]

Yes, There SHOULD be only 1 item in a field but this is an inherited db. There may be only 1 item but may also have multiple items separated by a comma or a slash or a semi-colon. What I don't understand is that when I just run the query with the above code, it shows all the records that contain what the user queried but when I execute the report that is based on the query, it shows different records.
Thanks for any help you can give.

 

[dhookom]

Unless you are running the first beta version of Access 2007, you can't/shouldn't store multiple values in a single field.

I'm not sure how you would expect a list box to display the correct value if the field contains more than one value. If you change the list box to a text box, it should display the entire contents of the field (if it is large enough).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]