Syntax for filter with text field criteria

[jketcher]

Need help with this. Getting errors every time I run it.

I am selecting a text "1" or "2" or "3" or All from a combo box. I started out coding this directly in the query but ran into problems because I have five combo boxes on this form and found it easier to do VBA (I think) for combox that need the "All" criteria.

My code looks like this and is not working:

Me.[Marketing subform].Form.Filter = "[T_YPEINSTI] = Chr$ (34) 1 Chr$ (34)"

I would expect the SQL translation of that would be Field = "1" and it would select all of the "1"'s. What am I doing wrong.

Also, while you are helping me what would the syntax be for the 'All' which is "1" Or "2" Or "3". Having trouble getting that one to work. I tried Not IsNull and it didn't like it. Tried other ideas to and did not like any of them.

Thanks for your help!

jketcher

 

[SOS]

I would change to:

If Me.YourComboBoxNameHere = "ALL" Then
   Me.[Marketing subform].Form.Filter = ""
   Me.[Marketing subform].Form.FilterOn = False
Else
   Me.[Marketing subform].Form.Filter = "[T_YPEINSTI] = Chr(34) & Me.YourComboBoxName & Chr(34)"
   Me.[Marketing subform].Form.FilterOn = True
End If

 

[jketcher]

The 'All' selection worked perfectly. But, the Else condition did not work. This is my code. When I used the form it ignored my selection and gave no results. I tried putting brackets around the combo box field Me.[Type_Inst] and that did not help. Any other ideas of what might be wrong?

Thank you!

If Me.[Type_Inst] = "All" Then
Me.[Marketing subform].Form.Filter = ""
Me.[Marketing subform].Form.FilterOn = False
Else
Me.[Marketing subform].Form.Filter = "[T_YPEINSTI] = Chr(34) & Me.Type_Inst & Chr(34)"
Me.[Marketing subform].Form.FilterOn = True
End If

 

[SOS]

You didn't do the code exactly right and that is my fault.

This:

Me.[Marketing subform].Form.Filter = "[T_YPEINSTI] = Chr(34) & Me.Type_Inst & Chr(34)"

should actually be:

Me.[Marketing subform].Form.Filter = "[T_YPEINSTI] =" & Chr(34) & Me.Type_Inst & Chr(34)

See the red part and remove the quote from the end.

 

[jketcher]

That worked great! Thank you very much

jketcher

 

[SOS]

That worked great! Thank you very much

Sure thing

 

[jketcher]

I am back! Have a new issue that I need advice on. There are multiple combo boxes on the form. The ones that I don't need to choose 'All' for have the selection criteria in the query. The others are VBA code. I noticed that when I make the selection from the last combo box it ignores the selections from the other boxes that were previously working and filtering data. What is the best approach to take. Should I create a button to filter all of the data in one object? Or would it work to requery the other boxes when the last combo filter is applied? I don't want to go to alot of extra work to set up the button (easy) and code all of the filter criteria (ugh) if there is a simple way of handling this.

Thanks again, Jketcher

 

[SOS]

If you are using several combos together, I would include them all in the code instead of as criteria in the query so you can build the appropriate where clause.

 

[jketcher]

New syntax question. How do I add two fields together to use the result in a filter? My code that is not working is below. I am totalling undergraduate and graduate enrollment. Do I need brackets around the fields? What else? Do you have good examples for syntax in filters? I am getting stuck there a lot. Thanks!

Dim Und_Grad As Integer
Und_Grad = C_P2D7A + C_P2D7B

 

[SOS]

New syntax question. How do I add two fields together to use the result in a filter? My code that is not working is below. I am totalling undergraduate and graduate enrollment. Do I need brackets around the fields? What else? Do you have good examples for syntax in filters? I am getting stuck there a lot. Thanks!

Dim Und_Grad As Integer
Und_Grad = C_P2D7A + C_P2D7B

If this is in code then to add them would be

Und_Grad = Nz(Me!C_P2D7A,0) + Nz(Me!C_P2D7B,0)

I used the NZ function so that nulls wouldn't cause an #Error or #Num to show up.

 

[jketcher]

Your code: Und_Grad = Nz(Me!C_P2D7A,0) + Nz(Me!C_P2D7B,0)

The fields are actually numeric data from the input table not combo box data. The combo boxes are check for ranges to filter selections. So would the code then be?

Und_Grad = Nz(C_P2D7A,0) + Nz(C_P2D7B,0)

 

[SOS]

Your code: Und_Grad = Nz(Me!C_P2D7A,0) + Nz(Me!C_P2D7B,0)

The fields are actually numeric data from the input table not combo box data. The combo boxes are check for ranges to filter selections. So would the code then be?

Und_Grad = Nz(C_P2D7A,0) + Nz(C_P2D7B,0)

The Me! with the bang refers to fields, by the way.

 

[jketcher]

Yes -- Me! refers to the form object (combo box in this case). But this calculation requires getting the data from the source table which has led me to adding a calculated field to the query which should solve the problem in a different way. Thanks!

 

[SOS]

Yes -- Me! refers to the form object (combo box in this case). But this calculation requires getting the data from the source table which has led me to adding a calculated field to the query which should solve the problem in a different way. Thanks!

No, Me!C_P2D7A refers to the field, Me.C_P2D7A refers to the control.

 

[jketcher]

P_2D7A has been working in this DB without the Me!. When is it necessary to use the Me! or Me. and when do you not have to use it? There is soooo much to learn.

 

[SOS]

P_2D7A has been working in this DB without the Me!. When is it necessary to use the Me! or Me. and when do you not have to use it? There is soooo much to learn.

1. I usually make sure to rename my controls so that they do not share the same name as the field (much simpler then).

2. Yes, it will work without the qualifier - most of the time, but I like to be explicit.

3. In reality the ! and the dot can refer to controls as well. But when you have the names the same as the control, I find that using the ! to refer to the field works well. There is a whole ! vs dot thread somewhere on the site here. It has some good info.

 

[jketcher]

Still stuck on something. I put VBA code under button control and it is still only filtering the enrollment control - ignoring the others in the final results. Here is my code. What am I doing wrong? I tried a couple of different scenarios for turning the filter on with the same results.

'Use filtering method to create mailing list
Private Sub btnSubmit_Click()
On Error GoTo Err_btnSubmit_Click
Me.[Marketing subform].Form.FilterOn = False

'Filter type of school
If Me.[Type_Inst] = "All" Then
Me.[Marketing subform].Form.Filter = ""
Me.[Marketing subform].Form.FilterOn = False
Else
Me.[Marketing subform].Form.Filter = "[T_YPEINSTI] =" & Chr(34) & Me.Type_Inst & Chr(34)

'Me.[Marketing subform].Form.FilterOn = True
End If

'If Type of school is Proprietary, filter on type of proprietary (art, business etc.)

If Me.[Type_Inst] = "3" Then
Me.[Marketing subform].Form.Filter = "[T_YPEPROP] =" & Chr(34) & Me.Proprietary_Code & Chr(34)
'Me.[Marketing subform].Form.FilterOn = True
End If

'Filter for length of Program
Me.[Marketing subform].Form.Filter = "[L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)


'Filter for Traditional Total Enrollment

If Me.[Total_Students] = "All" Then
Me.[Marketing subform].Form.Filter = "[Und_Grad] > 0"
Else
If Me.[Total_Students] = "1-500" Then
Me.[Marketing subform].Form.Filter = "[Und_Grad] BETWEEN 1 AND 500"
Else
If Me.[Total_Students] = "501-1000" Then
Me.[Marketing subform].Form.Filter = "[Und_Grad] BETWEEN 501 AND 1000"
Else
If Me.[Total_Students] = "1001-2000" Then
Me.[Marketing subform].Form.Filter = "[Und_Grad] BETWEEN 1001 AND 2000"
Else
If Me.[Total_Students] = "2001-5000" Then
Me.[Marketing subform].Form.Filter = "[Und_Grad] BETWEEN 2001 AND 5000"
Else
If Me.[Total_Students] = ">5000" Then
Me.[Marketing subform].Form.Filter = "[Und_Grad] > 5001"
End If
End If
End If
End If
End If
End If

Me.[Marketing subform].Form.FilterOn = True


Exit_btnSubmit_Click:
Exit Sub

 

[SOS]

So, this may take a little to digest, but are you saying that you can select multiple things to filter on? If that is the case then you don't treat these as independent things. You would need to build a where clause that takes into account all that has been selected.

 

[jketcher]

Where clause -- you mentioned it in an earlier thread and it just now sunk in. Do you have an example? I do not know the syntax for this for VBA. Thank you, jketcher

 

[SOS]

Where clause -- you mentioned it in an earlier thread and it just now sunk in. Do you have an example? I do not know the syntax for this for VBA. Thank you, jketcher

Perhaps the use of a sample here, might illustrate.