need some help pls with using a combobox as criteria for a query (1 Viewer)

[martinr]

i've built a form with a Combo box that works well as the criteria selection tool, to run a query that returns and displays the data in a subform
and also serves as the recordSource for a report.

The query gets it's criteria from the combobox: eg Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue]

This works well but the User also wants the option to clear the Combobox and see ALL Records (ie Unfiltered data) and
view/print the corresponding report with ALL records listed.

I was able to clear the contents of the Combobox with the simple commands:
Me.ComboType.SetFocus
Me.ComboType.Text = ""

I've tried an iif statement in the query to show all records when the combobox is Empty but it returns 0 records:
the sql WHERE Clause is something like:
Where (TblTransactions.Type) =IIf([Forms]![FrmTransactions02]![ComboType].[Text]="",([TblTransactions].[Type]) Like "*",[Forms]![FrmTransactions02]![Textvalue]))

Is there a way to get this to work or Is there a better way to toggle the query from displaying selected records and
ALL records based on the form Controls???

thanks for any suggestions offered!

 

[arnelgp]

Where (TblTransactions.Type) =IIf(Len([Forms]![FrmTransactions02]![ComboType] & "")=0, [Type],[Forms]![FrmTransactions02]![ComboType])

 

[oleronesoftwares]

Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue] Or Is Not Null(TblTransactions.Type)

In the image file attached, two records exists in the table, and it returns all two records, this is a guide, you can apply same principle to yours.

 

[Pat Hartman]

Me.ComboType.SetFocus
Me.ComboType.Text = ""

The way to clear a field is:

Me.ComboType = Null

"" is a ZeroLengthString. If your combo is supposed to return a numeric value, this will be a problem.

The .text property is only used when a control has the focus. Each control has three buffers:
.OldValue = original value when the record became current. It will be the saved value from the table or it will be null if this is a new record.
.text = the holding area that collects each character as it is being typed. In most versions of Access, the .text property is only available when the control has the focus.
.value = the default property and so it is frequently omitted for convenience. Me.SomeControl = Me.SomeControl.Value. This is the value of the control at all other times.

.OldValue = .Value when a record obtains the focus. Between the form's BeforeUpdate event and the form's AfterUpdate event the .OldValue is changed to the .Value so in the AfterUpdate event, the two buffers again hold identical values.

Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue] Or Is Not Null(TblTransactions.Type)

Not quite. This should be:
Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue] Or [Forms]![FrmTransactions]![Textvalue] Is Null) if you want to select all rows when the user doesn't provide a specific value for the criteria.

 

[martinr]

The way to clear a field is:

Me.ComboType = Null

"" is a ZeroLengthString. If your combo is supposed to return a numeric value, this will be a problem.

The .text property is only used when a control has the focus. Each control has three buffers:
.OldValue = original value when the record became current. It will be the saved value from the table or it will be null if this is a new record.
.text = the holding area that collects each character as it is being typed. In most versions of Access, the .text property is only available when the control has the focus.
.value = the default property and so it is frequently omitted for convenience. Me.SomeControl = Me.SomeControl.Value. This is the value of the control at all other times.

.OldValue = .Value when a record obtains the focus. Between the form's BeforeUpdate event and the form's AfterUpdate event the .OldValue is changed to the .Value so in the AfterUpdate event, the two buffers again hold identical values.


Not quite. This should be:
Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue] Or [Forms]![FrmTransactions]![Textvalue] Is Null) if you want to select all rows when the user doesn't provide a specific value for the criteria.

Thanks Pat,

Using your code and it's working well.

btw, What is the best way to check if the ComboBox is cleared or has a
Value selected – would any of these statements work?

• If Me.ComboBox1 = Null
• If Me.ComboBox1 <> Null
• If Me.ComboBox1.value = Null
• If Me.ComboBox1 Is Null
• If Me.ComboBox1 Is Not Null

 

[The_Doc_Man]

The best/most reliable way to verify a combo box has no selection (assuming that the combo DOES NOT support "Not in list" events, i.e. no type-in selections that aren't already in the table) is to do a test of Me.ComboBox1.ListIndex = -1, because that would say that nothing has been selected. Also works for listboxes in single-select mode. Combo boxes start zero-based. That is, the first row of possible selections in a combo box is 0 and you have numbers from 0 to n-1 representing the various rows you could have selected. So the non-selected index is -1, not 0.

 

[Pat Hartman]

would any of these statements work?

None of the above
You cannot compare Null to anything. The result will always be Null which means the If will never test true.
The last two are using SQL Syntax.

Use Doc's suggestion for a listbox or combo if you like. I prefer to use the same test regardless of what type of field I'm testing. It is less brain strain.

If Me.AnyFieldType & "" <> ""

This concatenates a ZLS which will convert a null value to "". The advantage of this method is that it works regardless of whether the contents are null or a ZLS. You can get ZLS in a control if the user types something and then rather than using esc or select all and delete, uses the backspace key. The field looks "empty" but it isn't. It now contains ZLS.

If you don't have to worry about ZLS, you can use the IsNull() function

The .Value property is the default property for a control so Me.SomeName is the same thing as Me.SomeName.Value. Since .Value is the default, most people just don't type it for brevity.

If IsNull(Me.AnyFieldType)

In a query, use Is Null or Is Not Null - HOWEVER, if you don't set your text fields' AllowZeroLength property to No, you can end up with those pesky ZLS getting in the way. So my first example with the concatination also works in queries:

Where SomeField & "" <> ""

 

[martinr]

Thanks Pat & Doc_Man for your suggestions and explanations.

 

[Pat Hartman]

You're welcome. Null is a very difficult concept.