Combo Box Search Query

[GCLIFTON]

In the VBA i believe there is some part that isn't right. I am trying If in the Combo box i select any value to only give me those values in a report. There may be 4 values that i select by and i only want to receive the data by those 4 sorts




SELECT TBL_NSF_Data.[Building Number], TBL_NSF_Data.[Unit Number], TBL_NSF_Data.[Unit Type], TBL_NSF_Data.[Hand'], TBL_NSF_Data.[Scope'], TBL_NSF_Data.[HD Sku], TBL_NSF_Data.Description, TBL_NSF_Data.Unit, TBL_NSF_Data.Qty, TBL_NSF_Data.xmcode
FROM TBL_NSF_Data
WHERE (((TBL_NSF_Data.[Unit Number]) Like "*" & [forms]![Searchform]![UnitNumber] & "*") AND ((TBL_NSF_Data.[Unit Type]) Like "*" & [forms]![Searchform]![Unittype] & "*") AND ((TBL_NSF_Data.[Hand']) Like "*" & [forms]![Searchform]![Hand] & "*") AND ((TBL_NSF_Data.[Scope']) Like "*" & [forms]![Searchform]![scope] & "*") AND ((TBL_NSF_Data.Phase) Like "*" & [forms]![Searchform]![Phase] & "*") AND ((TBL_NSF_Data.[L or M])<>"-"));

[CJ_London]

I think what you want is instead of this

....((TBL_NSF_Data.[Unit Number]) Like "*" & [forms]![Searchform]![UnitNumber] & "*")......

have this

....((TBL_NSF_Data.[Unit Number]) Like "*" & [forms]![Searchform]![UnitNumber] & "* OR " & [forms]![Searchform]![UnitNumber] & " is null")....

Just a general comment about using like

1. if user is selecting something that it will equal, use = rather than like - for example user enters a unit number of 10 - do you also want the query to return unit numbers 100, 105, 761023?
2. using the initial * prevents access (or any db) from using the indexes and instead does a sequential search which will be significantly slower - so consider whether it is really necessary (most times users will enter the first few characters rather than ones in the middle or end)

[GCLIFTON]

I apologize for just now responding

1. No i do not use for it to return those number. But it will not because the Unit are only from 1-12
2. Ok

3. Is this correct- Because it is not working. I am getting no data. Am I doing something wrong



SELECT TBL_NSF_Data.[Building Number], TBL_NSF_Data.[Unit Number], TBL_NSF_Data.[Unit Type], TBL_NSF_Data.[Scope'], TBL_NSF_Data.[HD Sku], TBL_NSF_Data.Description, TBL_NSF_Data.Unit, TBL_NSF_Data.Qty, TBL_NSF_Data.xmcode
FROM TBL_NSF_Data
WHERE (((TBL_NSF_Data.[Unit Type]) Like "*" & [forms]![Searchform]![Unity Type] & "*OR" & [forms]![Searchform]![Unity Type] & " is Null"));

4.Just to recap. When i select from the drop box from any of the six i would like for it to return the data that is related to it.

[GCLIFTON]

I apologize for just now responding

1. No i do not use for it to return those number. But it will not because the Unit are only from 1-12
2. Ok

3. Is this correct- Because it is not working. I am getting no data. Am I doing something wrong



SELECT TBL_NSF_Data.[Building Number], TBL_NSF_Data.[Unit Number], TBL_NSF_Data.[Unit Type], TBL_NSF_Data.[Scope'], TBL_NSF_Data.[HD Sku], TBL_NSF_Data.Description, TBL_NSF_Data.Unit, TBL_NSF_Data.Qty, TBL_NSF_Data.xmcode
FROM TBL_NSF_Data
WHERE (((TBL_NSF_Data.[Unit Type]) Like "*" & [forms]![Searchform]![Unity Type] & "*OR" & [forms]![Searchform]![Unity Type] & " is Null"));

4.Just to recap. When i select from the drop box from any of the six i would like for it to return the data that is related to it.

Here is the logic behind the View Report button