Query selecting incorrect recordset

[GabberGod]

SELECT Rep_Project_NOItemsB.Expr1000, Rep_Project_NOItemsB.Drawings.Status, Rep_Project_NOItemsB.Project, Rep_Project_NOItemsB.Partition, Rep_Project_NOItemsB.Section, Rep_Project_NOItemsB.[Sub Section], Rep_Project_NOItemsB.[Drawing No], Rep_Project_NOItemsB.Revision, Rep_Project_NOItemsB.Type, Rep_Project_NOItemsB.Job, Rep_Project_NOItemsB.[Item No], Rep_Project_NOItemsB.[Item Name], Rep_Project_NOItemsB.[Job Items].Status, Rep_Project_NOItemsB.Quantity, Rep_Project_NOItemsB.[Due Date], Rep_Project_NOItemsB.Description, Rep_Project_NOItemsB.[Drawing Description], Rep_Project_NOItemsB.Category, Rep_Project_NOItemsB.Complete
FROM Rep_Project_NOItemsB
WHERE (((Rep_Project_NOItemsB.Category) In ([forms]![rep_project_noitems]![text7]) Or (Rep_Project_NOItemsB.Category) Is Null));

Ok so i have the above query. It should display a list of records where where the category field is not equal to the array of strings in [forms]![rep_project_noitems]![text7]. or the category field is empty.

the format of the string ([forms]![rep_project_noitems]![text7] is "'xxx', 'xxx', 'xxx'"

if i add a NOT (as below) i get every record in the table

WHERE (((Rep_Project_NOItemsB.Category) [b]NOT[/b] In ([forms]![rep_project_noitems]![text7]) Or (Rep_Project_NOItemsB.Category) Is Null));

if i remove the not I only get the empty records.

if i replace the field address with the string (as below) i get no records at all

WHERE (((Rep_Project_NOItemsB.Category) In ("'xxx', 'xxx', 'xxx'") Or (Rep_Project_NOItemsB.Category) Is Null));

please help i dont understand what is wrong

 

[dhookom]

You can't reference a value of a control on a form like that. It doesn't work if there is more than item in the text box.

There are methods of dynamically building and updating the SQL property of the query in the FAQs of either the queries or reports forum.

Is this query used as the record source of a form or report?

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]

 

[GabberGod]

the text box ony has 1 item, a programaticly generated string (format as outlined above), it is generated from a multi select listbox

yes it is the record soruce of a report

so what am i doing wrong?

 

[dhookom]

Don't apply this criteria in the report's record source. It is much easier and more flexible to build a WHERE clause to use in the DoCmd.OpenReport line.

Again, check the FAQs in the reports forum for a generic function that can be used with muli-select list boxes. FancyPrairie also has some sample code in the FAQs.

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]

 

[GabberGod]

ok so i was having a look at the faq section and i found faq703-3936 located at

http://www.tek-tips.com/faqs.cfm?fid=3936

it says

I hate writing the same code over and over like the code that loops through the items selected in a multi-select list box. This function will accept a list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "

how is that different from what i have done? i have a function that returns a string to the query, the only difference is that the string that is returns also contains part of the query

it even uses the same syntax does it not?

 

[PHV]

Yours shouldn't surround the list with double-quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Again, you can't use a function or expression in a query to return a multiple value list. You can tack this "where" clause to the DoCmd.OpenReport.

Don't waste your time attempting to solve this as you are currently trying.

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]