Complicated SQL Build

[bitech]

I need to make a string that is based on what the user selects on a form. I have a continous form based on a table named "Ethnicity" which has a list of all of the ethnicities the user has listed. In order for them to search on the ethnicities I need to popup the continuous form and based on which ones they select...{the system will have to detect it based on whether or not the [SELECT] field has a "-1" in it or not} have it build a sql string. This is an easy task if I can only find a way to get the multiple Ethnicity choices to get in one field. Let me know if I'm not explaining this correctly.

 

[Knitter]

Need clarification. Will the user be indicating more than one ethnicity? Or, only one?

If user is allowed to select more than one, you might try the following:

SELECT * FROM tblEthnicity WHERE (((tblEthnicity.Ethnicity_1)=-1)) OR (((tblEthnicity.Ethnicity_2)=-1)) OR ((tblEthnicity.Ethnicity_3)=-1 OR .......));

 

[bitech]

They would be able to select more than one ethnicity, which I wouldn't even know what the ethnicities are because they aren't static they are put in by the user. So it needs to look for anything in the ethnicity table that has an "-1" and search for anything company in the vendor table that match the selected ethnicities.

 

[cmmrfrds]

select * from vendortable
where ethinicity IN (
select ethinicity from ethinicitytable
where yourfield = -1)

 

[MichaelRed]

With out discussing the specific technique(s) to build the SQL statemnet, I WOULD question the prudence of searching a db (via any method) on the basis of free form user input (regardless of the number of terms). If you want to permit searches based on the values in a field, I would suggest the use of a multiselect combo/list box, with the source set to the unique values in the field.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over