How ignore blank form fields in a query that has 17 columns (PLEASE HELP)

[Shib]

I'm trying to create a database to find out what a sample is based on the composition values filled into a form.

I want to be able to input a set of values and have the closest 3 records returned.

I have tried to use the AND operator with the criteria in each of the 17 columns:
(Where txtbox is the field name)

 [forms]![input form]![txtbox]

However it's not giving me any results.

When using the OR operator for each column it returns any records that contain each of the values.

Im unsure of how to use the AND operator in each of the columns whilst ignoring any form fields that have been left blank. The form fields have been set to "".


Please help if you have any ideas of how to!

[ranman256]

Are you sure the design is correct?
you don't normally search across many columns.
you search IN a column.

[kd2017]

I don't know exactly what your doing but one approach may be to have the query return all the data to a form, then have a search form that filters the data's form appropriately.

[Shib]

Would it help to post my SQL code?

[Shib]

Are you sure the design is correct?
you don't normally search across many columns.
you search IN a column.

I'm using a select query but I think the issue im having is that I'm looking for different values in multiple table fields for one particular record and that the form that I'm using will have some blank input boxes. Do you have any ideas?

[kd2017]

Give this page a read: http://allenbrowne.com/ser-62.html

Again, w/ out more information we don't know excatly what you are doing. That said, the section heading "Using a query instead" I think describes how to do what you are trying to do. It also gives several points as to why that might not be the best approach, but rather to use the approach I suggested in post #3.

[kd2017]

Would it help to post my SQL code?

Yeppers...

[Shib]

Give this page a read: http://allenbrowne.com/ser-62.html

Again, w/ out more information we don't know excatly what you are doing. That said, the section heading "Using a query instead" I think describes how to do what you are trying to do. It also gives several points as to why that might not be the best approach, but rather to use the approach I suggested in post #3.

Thanks! I'll try both of them, what type of filters would have to be applied?

[kd2017]

Thanks! I'll try both of them, what type of filters would have to be applied?

A form has one property called Filter that works just like a where clause. The property accepts one string. The string looks exactly like an sql where clause. For example Me.Filter = "ID=2342"

You can build this string dynamically in vba, then when set a forms filter equal to this string, then turn the filter on: Me.FilterOn = True

Let's say you have a main form with user input options then you have a subform who's dataset you want to filter.
It might look something like this:

Dim MyFilter as String

MyFilter = ""

If Not IsNull(Me.opt1) then
  MyFilter = "opt1=" & Me.opt1
End If

If Not IsNull(me.cmb2) then
  If MyFilter <> "" Then MyFilter = MyFilter & " AND "
  MyFilter = MyFilter & "opt2=" & Me.cmb2
End If

If me.txt1 <> "" then
  If MyFilter <> "" Then MyFilter = MyFilter & " AND "
  MyFilter = MyFilter & "opt3=" & Me.txt1
End If

Me.Subform.Form.Filter = MyFilter
Me.Subform.Form.FilterOn = True

First it looks at a control called opt1, if the user selected a value then it adds it to the string, then it looks at cmb2 and optionally adds it to the string. so on and so forth.
Does the above make sense?

The Allen Browne link has a working database example you can download and study.

How comfortable are you with VBA and/or working directly with SQL code (not the query designer)?

[orange]

Further to the advice given so far, it would be helpful to readers if you could put the subject matter and a typical search into context.
That is,
-what is the subject of the data you are searching, and (chemicals, Poisons, Forensics....)
-what sort of terms are used in a search (Sodium, Potassium, alcohol, blood type....??)

The Allen Browne search info that kd2017 suggested is excellent, and often referenced!

[Shib]

Further to the advice given so far, it would be helpful to readers if you could put the subject matter and a typical search into context.
That is,
-what is the subject of the data you are searching, and (chemicals, Poisons, Forensics....)
-what sort of terms are used in a search (Sodium, Potassium, alcohol, blood type....??)

The Allen Browne search info that kd2017 suggested is excellent, and often referenced!

I’m searching for Metals based on the elemental composition of Fe, B, C, Co, Cr, Cu, Mn, Mo, V, W, Nb, Ni, Si, Pb, Zn, Al and Sn

[Shib]

A form has one property called Filter that works just like a where clause. The property accepts one string. The string looks exactly like an sql where clause. For example Me.Filter = "ID=2342"

You can build this string dynamically in vba, then when set a forms filter equal to this string, then turn the filter on: Me.FilterOn = True

Let's say you have a main form with user input options then you have a subform who's dataset you want to filter.
It might look something like this:

Dim MyFilter as String

MyFilter = ""

If Not IsNull(Me.opt1) then
  MyFilter = "opt1=" & Me.opt1
End If

If Not IsNull(me.cmb2) then
  If MyFilter <> "" Then MyFilter = MyFilter & " AND "
  MyFilter = MyFilter & "opt2=" & Me.cmb2
End If

If me.txt1 <> "" then
  If MyFilter <> "" Then MyFilter = MyFilter & " AND "
  MyFilter = MyFilter & "opt3=" & Me.txt1
End If

Me.Subform.Form.Filter = MyFilter
Me.Subform.Form.FilterOn = True

First it looks at a control called opt1, if the user selected a value then it adds it to the string, then it looks at cmb2 and optionally adds it to the string. so on and so forth.
Does the above make sense?

The Allen Browne link has a working database example you can download and study.

How comfortable are you with VBA and/or working directly with SQL code (not the query designer)?

I’m not familiar with the syntax at all however I can give it a go!

[orange]

Perhaps you could post a copy of your database or a part of your test database so readers can see the data and search criteria, and offer suggestions regarding user interface/forms etc.
Some times a contextual picture of what you trying to accomplish is more communicative than words.

[Shib]

My SQL is

 SELECT [Existing Metallurgy].[ID], [Existing Metallurgy].[Metal], [Existing Metallurgy].[Fe], [Existing Metallurgy].[B], [Existing Metallurgy].[C], [Existing Metallurgy].[Co], [Existing Metallurgy].[Cu], [Existing Metallurgy].[Cr], [Existing Metallurgy].[Mn], [Existing Metallurgy].[Mo], [Existing Metallurgy].[Nb], [Existing Metallurgy].[Ni], [Existing Metallurgy].[Si], [Existing Metallurgy].[V], [Existing Metallurgy].[W], [Existing Metallurgy].[Zn], [Existing Metallurgy].[Pb], [Existing Metallurgy].[Al], [Existing Metallurgy].[Sn]FROM [Existing Metallurgy]
WHERE ((([Existing Metallurgy].[Fe]) Between ([forms]![Input form]![Fe]*0.95) And ([Forms]![Input form]![Fe]*1.05))) OR ((([Existing Metallurgy].[B]) Between ([forms]![Input form]![B]*0.95) And ([Forms]![Input form]![B]*1.05))) OR ((([Existing Metallurgy].[C]) Between ([forms]![Input form]![C]*0.95) And ([Forms]![Input form]![C]*1.05))) OR ((([Existing Metallurgy].[Co]) Between ([forms]![Input form]![Co]*0.95) And ([Forms]![Input form]![Co]*1.05))) OR ((([Existing Metallurgy].[Cu]) Between ([forms]![Input form]![Cu]*0.95) And ([Forms]![Input form]![Cu]*1.05))) OR ((([Existing Metallurgy].[Cr]) Between ([forms]![Input form]![Cr]*0.95) And ([Forms]![Input form]![Cr]*1.05))) OR ((([Existing Metallurgy].[Mn]) Between ([forms]![Input form]![Mn]*0.95) And ([Forms]![Input form]![Mn]*1.05))) OR ((([Existing Metallurgy].[Mo]) Between ([forms]![Input form]![Mo]*0.95) And ([Forms]![Input form]![Mo]*1.05))) OR ((([Existing Metallurgy].[Nb]) Between ([forms]![Input form]![Nb]*0.95) And ([Forms]![Input form]![Nb]*1.05))) OR ((([Existing Metallurgy].[Ni]) Between ([forms]![Input form]![Ni]*0.95) And ([Forms]![Input form]![Ni]*1.05))) OR ((([Existing Metallurgy].[Si]) Between ([forms]![Input form]![Si]*0.95) And ([Forms]![Input form]![Si]*1.05))) OR ((([Existing Metallurgy].[V]) Between ([forms]![Input form]![V]*0.95) And ([Forms]![Input form]![V]*1.05))) OR ((([Existing Metallurgy].[W]) Between ([forms]![Input form]![W]*0.95) And ([Forms]![Input form]![W]*1.05))) OR ((([Existing Metallurgy].[Zn]) Between ([forms]![Input form]![Zn]*0.95) And ([Forms]![Input form]![Zn]*1.05))) OR ((([Existing Metallurgy].[Pb]) Between ([forms]![Input form]![Pb]*0.95) And ([Forms]![Input form]![Pb]*1.05))) OR ((([Existing Metallurgy].[Al]) Between ([forms]![Input form]![Al]*0.95) And ([Forms]![Input form]![Al]*1.05))) OR ((([Existing Metallurgy].[Sn]) Between ([forms]![Input form]![Sn]*0.95) And ([Forms]![Input form]![Sn]*1.05)));

This query works fine as a first step as it narrows down the results but I'm stuck for what to do next. I used OR because I cannot get the AND to work if the boxes are null or ""

[Shib]

Attachment 35076

I've attached a simple copy of my database to help