query selects incorrect data

[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 ((Not (Rep_Project_NOItemsB.Category)=([forms]![rep_project_noitems]![text7]) Or (Rep_Project_NOItemsB.Category) Is Null));

the above code is the last part of a three part query. Each query builds on the last. the other two parts are working without a hitch.

this query is supposed to accept a text string as a parameter query for a report. the parameter (text7) will contain the following format text:

xxxxxx or xxxxx or xxxxx.

i.e select a bunch of stuff where category = not(text7) or is null

the aim is to filter out unwanted records where of certain categories. i.e filter out all the records relating to bolts and gaskets, leaving only the records relating to flanges and widges.

basicly if there is only 1 parameter in the text box the code works fine, if there is 2 or more parameters

note [text7] is an unbound field on a form which is programaticly filled in by a vb function that compiles the text parameters from the selected items in a multi-select listbox on the same form.

 

[fredericofonseca]

as are dealing with text you need to surround the value with single quotes.

WHERE ((Not (Rep_Project_NOItemsB.Category)=('" & [forms]![rep_project_noitems]![text7] & ') Or

And what I think you really need from your explanation is

WHERE (((Rep_Project_NOItemsB.Category)= not in ('" & my_single_quote_and_comma_separated_list & ') Or

so if your [forms]![rep_project_noitems]![text7] has "xx1 or xx2 or xx3" this should convert to "'xx1', 'xx2', 'xx3'"

If you with to be clever enough and allow for OR and AND on your text box then the code will need more changes.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[GabberGod]

and how do i programaticly construct "'xx1', 'xx2', 'xx3'"

what is the code for putting a ' in a string? and a "?

unfortunately ive never done this and cant find it anywhere in the access help files, but thats not unusual.